We need to build a report of VIP user requests.

Jason_Meyer

We need to build a report of VIP user requests. Any advice on how to do this?

We are an organization of roughly 20k 'affected users', of those 20k users we probably have about 100 VIP customers that we need to take extra care of. Today, the only way that I have to know if they are being taken care of is to manually search for each user, and check if they have any pending requests.

What I would like, is to build a VIP Report that we can generate on the fly or scheduled that would show me any requests pending for any of those 100 individuals.

Our first thought was to build a some kind of SSRS report that we could populate off of an Active Directory group, our developers have been looking at a way to do that and thus far haven't had much success. The thinking is then we could just maintain a AD security group on the back end to monitor any requests related to them.

Any advice on how we can create such a report? Or any thoughts on how to accomplish this?

Thanks!

Adam_Dzyacky

The pure AD/SQL/environment way to do this is kind of what you're suggesting -

• Write a report that says get me all the Work Items, whose Affected User has an OU or DistinguishedName property like X

The probably better, more longer term approach is -

• Create a Class Extension for the AD User class called "VIP"
• Check this value off for all of your VIP users in question
• When the MP syncs to the SCSM DW, you can simply look for all Work Items where the Affected User's VIP Property = true

The other thing the class extension does is open up a new workflow avenue. Incidents coming from VIPs could trigger additional notifications, runbooks, etc. The good news is there are are several blogs on this topic to help you extend SCSM to meet this need.

Jason_Meyer

Adam, thanks for your input. Today, there is no 'common attribute' in AD that we can write a report against. 

In your probably better approach, you are talking about extending the class (schema?) in Active Directory, correct? 

Or are you talking extending the class in SCSM?

Adam_Dzyacky

Extending the class in SCSM

@Brett_Moffett is one such author whose laid out the entire thing start to finish. https://systemcenternoise.wordpress.com/2015/02/18/scsm-vip-user-solution/

The core of the change here is really just the new bool property class extension in a custom sealed MP, but as you can see he's really gone the distance with console form edits.

Gerhard_Goossens

Here is query I created for reporting on a bool extension to the IR and SR classes from the live DB.

You can use the same concept and just add your class extensions in the WHERE clauses on line 20 and 46.

You can also add some filters to check only for Active workitems.

-- IR
SELECT
 IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID]
,IR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title]
,DSStatus.DisplayName As [Status]
,DSSG.DisplayName As [Support Group]
,DSClass.DisplayName As [Classification]
,DSSource.DisplayName As [Source]
,CONVERT(nvarchar(24),CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688,105) AS [Created Date]
FROM MT_System$WorkItem$Incident IR
INNER JOIN MT_ClassExtension_b9efcfcc_c218_4780_b35a_08db7edb9464 OPS ON IR.BaseManagedEntityId = OPS.BaseManagedEntityId
INNER JOIN EnumType ETSG ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = ETSG.EnumTypeId 
INNER JOIN DisplayStringView DSSG ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = DSSG.MPElementId
INNER JOIN EnumType ETStatus ON IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED = ETStatus.EnumTypeId   
INNER JOIN DisplayStringView DSStatus ON IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED = DSStatus.MPElementId 
INNER JOIN EnumType ETClass ON IR.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = ETClass.EnumTypeId  
INNER JOIN DisplayStringView DSClass ON IR.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = DSClass.MPElementId
INNER JOIN EnumType ETSource ON IR.Source_96FD9295_16FA_3D7A_5995_F805B7B01F21 = ETSource.EnumTypeId  
INNER JOIN DisplayStringView DSSource ON IR.Source_96FD9295_16FA_3D7A_5995_F805B7B01F21 = DSSource.MPElementId
WHERE OPS.EscalatedToOps_643BC398_B323_CF06_EA66_724BD2D68ABA = 1
AND DSSG.LanguageCode = 'ENU' 
AND DSStatus.LanguageCode = 'ENU'
AND DSClass.LanguageCode = 'ENU'
AND DSSource.LanguageCode = 'ENU'
-- UNION
UNION ALL
-- SR
SELECT
 SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID]
,SR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS [Title]
,DSStatus.DisplayName As [Status]
,DSSG.DisplayName As [Support Group]
,DSClass.DisplayName As [Classification]
,DSSource.DisplayName As [Source]
,CONVERT(nvarchar(24),SR.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688,105) AS [Created Date]
FROM MT_System$WorkItem$ServiceRequest SR
INNER JOIN MT_ClassExtension_8ec3283a_1bb0_4eb0_a8b2_1941958532cc OPS ON SR.BaseManagedEntityId = OPS.BaseManagedEntityId
INNER JOIN EnumType ETSG ON SR.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 = ETSG.EnumTypeId 
INNER JOIN DisplayStringView DSSG ON SR.SupportGroup_766D07C2_2680_CD38_4638_D3A7813E6488 = DSSG.MPElementId
INNER JOIN EnumType ETStatus ON SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = ETStatus.EnumTypeId   
INNER JOIN DisplayStringView DSStatus ON SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = DSStatus.MPElementId 
INNER JOIN EnumType ETClass ON SR.Area_0E2739F7_6889_09EF_B260_35CD954D3563 = ETClass.EnumTypeId  
INNER JOIN DisplayStringView DSClass ON SR.Area_0E2739F7_6889_09EF_B260_35CD954D3563 = DSClass.MPElementId
INNER JOIN EnumType ETSource ON SR.Source_3734511A_37D2_6E2C_3C8B_3C880AD8500D = ETSource.EnumTypeId  
INNER JOIN DisplayStringView DSSource ON SR.Source_3734511A_37D2_6E2C_3C8B_3C880AD8500D = DSSource.MPElementId
WHERE OPS.EscalatedToOps_A5C3660A_FC4C_366C_A82E_1E4D989E1068 = 1
AND DSSG.LanguageCode = 'ENU' 
AND DSStatus.LanguageCode = 'ENU'
AND DSClass.LanguageCode = 'ENU'
AND DSSource.LanguageCode = 'ENU'
ORDER BY [Created Date];

I used Entity explorer to get the values for the class extensions I created"

IR

WHERE OPS.EscalatedToOps_643BC398_B323_CF06_EA66_724BD2D68ABA = 1

SR

WHERE OPS.EscalatedToOps_A5C3660A_FC4C_366C_A82E_1E4D989E1068 = 1

Gerhard_Goossens

Please remember that any class extensions will reset the last modified time on all workitems of that class. So make sure to use this tool to avoid that situation. (Use at own risk) :-)

https://gallery.technet.microsoft.com/SCSM-Last-Modified-Date-15425cdc

Jason_Meyer

I really appreciate all of the info and knowledge share.  Thanks men.

Jason_Meyer

Finally getting back to this one.

Is it possible to write a Powershell query that would give me all pending requests that are assigned to members of an Active Directory group?

Active Directory doesn't want to identify VIPs in the directory, SCSM doesn't want to extend the schema.

What I can do is create a active directory group, populate it with the VIPs and then hopefully run a powershell command to query for any workitems pending for members of the group?

Justin_Workman

GetWisByGroup.zip

@Jason_Meyer - Powershell attached!

EDIT: Re-attached after a bit more testing

Jason_Meyer

Thanks Justin!!! All of my Powershell comes from Exchange admin days. I'll talk to our SCSM admin and see if we can make this work.

Jason_Meyer

So, was able to spend some time on this. Was able to get powershell connected to our Service Manager servers. Quickly learned that the script required the community built "SMLets" powershell commands. Did some research to get that going.

Found SMLets (1.0 I think, an old version?) and got it to install, setup the two .DLL files that it needed and was able to get Import-Module SMLets to complete. Listed out the commands and found it was missing some. Did some more research found that I may have an old version.

Question: What version of SMLets are you running and where can I find it? Dug around on GitHub and PowerShell Gallery and finding some various versions, want to make sure I'm running the same as you.

Question: I've read quite a bit about the SCSM SDK and wondering if installing that will help. Originally I got the version of SMLets that I had running but when running your script got a few errors. Should I install the SCSM SDK? and (sorry) where can I find it? Again, did some digging and not finding a good source for it.

Question: At the end of your script, line 51 is "Get-WIsByGroup -groupName scsm_analysts". Just verifying that I need to change the -groupname to the name of the Active Directory group, correct?

I appreciate the assistance Justin.

Justin_Workman

@Jason_Meyer - You should be able to run the powershell on the portal server. The SMLets that gets installed by the portal will be compatible with the script. You are correct in that the group name on line 51 needs to be changed to your group.

Jason_Meyer

Hmm, I don't have access to run this directly on the Cireson web server. Any way to run it on a workstation?

Justin_Workman

I've attached the version of SMLets that the portal ships with. It may take a bit of configuration...

https://lazywinadmin.com/2014/09/powershell-scsm-install-and-config.html

The "SDK" pieces you need are just the DLLs from your SCSM management server.

SMLets.zip

Jason_Meyer

Thanks Justin, I couldn't get it to work but our Service Manager admin did tweaking and we did get it working.

needed to add the -computername to eliminate the data access service error.

Really appreciate your help on this.

Now looking at how to pull Affected User name for the results somehow.

Jason_Meyer

So far I have not been able to get Affected User pulled into this report. And actually what we need is the e-mail address of the affected user, which I now am learning is a related object to the Affected user, which is a related object to the work item.

Can someone take a look at the attached code and see how I could get the Affected User's email address added to the output? I'm not concerned about the format, just need a way to get the addresses in bulk.

Script.txt