Home Powershell

Member Of AD Group is Affected User SR Export

Cary_MyersCary_Myers Customer IT Monkey ✭
edited May 2022 in Powershell

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-Module ActiveDirectory
Import-Module SMLets

#Define Variables
$Path = '\PathToFile\File.xlsx'
$Recipients = @("<[email protected]>","<[email protected]>")
$Sender = '[email protected]'
$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_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

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

  • Peter_MuttenthalerPeter_Muttenthaler Partner Advanced IT Monkey ✭✭✭

    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_MyersCary_Myers Customer IT Monkey ✭

    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

Sign In or Register to comment.