Home General Discussion

Analytics Report for On Behalf Of tickets

Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭


My manager was asking about being able to report off the On Behalf Of input from the Portal. Does anyone know if this is possible?



  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited March 2018

    What you're going to be looking for when it comes to reporting against the SCSM DW is the difference between the Created By user and the Affected User. By default, if you aren't using that field the user who submits the request would be the Affected and Created user of the Work Item.

    If however this field is being leveraged, that new Work Item will have the person who submitted the request as the Created By, but the "On Behalf Of" will be the Affected User.

    That said, you're looking to report on this difference.

  • Options
    Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    I'm kind of thinking the same thing EXCEPT it's possible for the CreatedBy and AffectedUser to be different if an analyst creates the Work Item directly without using a RO.  I did this query on the ServiceManager database that gets SRs where UserInput is not null and CreatedUser is not equal to AffectedUser.

    select sr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title'
    ,sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID'
    ,affectedUser.DisplayName AS 'AffectedUser'
    ,createdUser.DisplayName AS 'CreatedUser'

    from MT_System$WorkItem$ServiceRequest sr

    inner join Relationship affectedUserRel 
    on affectedUserRel.sourceEntityId = sr.BaseManagedEntityId
    and affectedUserRel.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
    inner join MT_System$Domain$User affectedUser
    on affectedUser.BaseManagedEntityId = affectedUserRel.targetEntityId

    inner join Relationship CreatedUserRel
    on CreatedUserRel.SourceEntityId = sr.BaseManagedEntityId
    and CreatedUserRel.RelationshipTypeId = 'DF738111-C7A2-B450-5872-C5F3B927481A'
    inner join MT_System$Domain$User createdUser
    on createdUser.BaseManagedEntityId = CreatedUserRel.TargetEntityId

    WHERE UserInput_75DF0BE8_3446_F616_6AC0_8510B74838AF IS NOT NULL
    AND createduser.BaseManagedEntityId <> affectedUser.BaseManagedEntityId

    @Karen_Bruster1 - Is something like this what you're after?
  • Options
    Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @Justin_Workman I ran your script and it gave me the Service Re created on behalf of, so it works great. I was trying to add the created date to the columns . But as you know we are having some logon issues and cannot get to the database. BTW I was able to logon and set up Outlook finally. So we should be ready for tomorrow

  • Options
    Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭
    @Justin_Workman Can this script be modified for Incidents as well?
Sign In or Register to comment.