Home Analyst Portal

We need to build a report of VIP user requests.

Jason_MeyerJason_Meyer Customer Adept IT Monkey ✭✭

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!

Answers

  • Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
    edited February 21

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

    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_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
    edited February 26

    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_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited February 27

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

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

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

Sign In or Register to comment.