Member Of AD Group is Affected User SR Export

Cary_Myers

We keep track of our VIP customers in an AD group. Request came up to report on open Service Requests for VIP users. Details of Service Requests are added to a custom ps object and exported using the excel ps module here https://www.powershellgallery.com/packages/ImportExcel/7.4.1

#Import-Modules
Import-Module ActiveDirectory
Import-Module SMLets

#Define Variables
$Path = '\PathToFile\File.xlsx'
$Recipients = @("<Email@Address.Com>","<Email@Address.Com>")
$Sender = 'Email@Address.Com'
$Subject = 'Email Subject'
$SMTPServer = 'smtp.domain.com'
$Body = "Body of email."

##Create Empty Arrays
$groupMembers = @()
$SCSMVIP = @()
$VIPS = @()
$SRID = @()
$SRFilterer = @()
$SRInfo = @()

$SRClass = Get-SCSMClass -Name System.Workitem.ServiceRequest$
$cType = 'Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectCriteria'

#Retrive Enumerations
$SRInInProgressStatus = (Get-SCSMEnumeration -Name ServiceRequestStatusEnum.InProgress).id.ToString()
$SRSubmittedStatus = (Get-SCSMEnumeration -Name ServiceRequestStatusEnum.Submitted).id.ToString()
$SRNewStatus = (Get-SCSMEnumeration -Name ServiceRequestStatusEnum.New).id.ToString() 

#Get Members AD Group
$groupName = 'ADGroupName'
$groupMembers = Get-ADGroupMember $groupName | Select-Object -Property SamAccountName
$SCSMVIP = foreach ($member in $groupMembers) {
    Get-SCSMObject -Class $userClass -Filter "username -eq $($member.samaccountname)"}
$VIPS = $SCSMVIP.Displayname

#Create Search Criteria
$crtiteria = "Status = '$SRInInProgressStatus' or Status = '$SRSubmittedStatus'or Status = '$SRNewStatus'"
$crit = new-object $cType $crtiteria, $SRClass

#Get SR with criteria
$SRID = Get-SCSMObject -Criteria $crit

$ASClass = Get-SCSMRelationshipClass -Name System.WorkitemAssignedToUser
$AFCClass = Get-SCSMRelationshipClass -Name System.WorkitemAffectedUser
 
$SRFilter = foreach ($S in $SRID){
Get-SCSMRelationshipObject -BySource $S | Where-Object {($_.relationshipid -eq $AFCClass.id.Guid) -and ($_.sourceobject.classname -eq $SRClass.Name) -and ($_.TargetObject -in $VIPS)} 
}
foreach ($S in $SRFilter){
$S = Get-SCSMObject -Id $S.sourceobject.Id


$SRInfo += New-Object -Type PsObject -Prop @{
  'Ticket ID' = $S.Id
  'Title' = $S.Title
  'Status' = $S.Status.DisplayName
  'Created Date' = $S.CreatedDate
  'Last Modified' = $S.LastModified
  'Affected User' = (Get-SCSMRelatedObject -SMObject $S -Relationship $AFCClass).DisplayName
  'Assigned To' = (Get-SCSMRelatedObject -SMObject $S -Relationship $ASClass).DisplayName
  'Area' = $S.Area.DisplayName
  'Tier' = $S.SupportGroup.DisplayName
}
}

$SRInfo | Select 'Ticket ID','Title','Status','Created Date','Last Modified','Affected User','Assigned To','Area','Tier' | Export-Excel -BoldTopRow -Path $Path -AutoSize

Send-MailMessage -To $Recipients -From $Sender -Subject $Subject -Body $Body -Attachments $Path -SmtpServer $SMTPServer -credential $cred 

Tracked down a few of the affected user's who did not belong to the vip ad group. It seemed the relationship class used picked up the original submitter who was a VIP, the incident was later transferred to a non vip. Appreciate any feedback!

Justin_Workman

@Cary_Myers - Looks good! I definitely like the idea of exporting directly to Excel vs just a simple CSV!

Peter_Muttenthaler

Hi @Cary_Myers:

relationships does have the "IsDeleted" Flag in the DB, I think thats exactly your issue right here.

try to extend your get-scsmrelationshipobject where-object with "-and ($_.IsDeleted -eq $false)"

I think that should solve your issue

Cary_Myers

Thanks for the feedback Peter! Below is what ended up what was used for the affected user.

(Get-SCSMRelationshipObject -BySource $SR -Filter 
"RelationshipID -eq 'dff9be66-38b0-b6d6-6144-a412a3ebd4ce'").TargetObject.DisplayName

in place of

  'Affected User' = (Get-SCSMRelatedObject -SMObject $S -Relationship $AFCClass).DisplayName