IT Monkey:   Click Here to Help Me Build the Agenda for Upcoming Cireson Webinars!

How do I find requests that an analyst only worked on. They didn't create the request, they didn't.

Jason_MeyerJason_Meyer Customer Adept IT Monkey ✭✭
How do I find requests that an analyst only worked on.  They didn't create the request, they didn't resolve it.  But they spent a lot of time in the middle.    I do not find any way to see/search for this?

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    They're not the Assigned User?
  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    edited April 24
    This query might help:

    
    select bme.Name AS 'WorkItemId', 
    WorkingUser.DisplayName AS 'WorkingUser',bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 AS 'TimeWorked' from MT_System$WorkItem$BillableTime btjoin relationship r
    on r.TargetEntityId = bt.BaseManagedEntityIdjoin BaseManagedEntity bme
    on bme.BaseManagedEntityId = r.SourceEntityId
    join relationship ur
    on ur.SourceEntityId = bt.BaseManagedEntityId
    join MT_System$Domain$User WorkingUser
    on WorkingUser.BaseManagedEntityId = ur.TargetEntityId
    


    You could build a SQL Query Widget with that query and should be able to click through to the work item.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    While the above works, it only works if the analysts are trained to enter billable time.
    We ended up creating our own data warehouse were we run a number of ETL jobs that pull each relationship type so we can get analyst counts. Now this will provide the count metrics it doesn't provide the content. HTH
  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭
    @Jason_Meyer

    Are these analysts updating the ticket at all or performing any actions that are recorded in the action log?

    If they are working on the ticket and not updating any information in the ticket or recording time worked you may have a hard time searching for this.

    Shane.
  • Jason_MeyerJason_Meyer Customer Adept IT Monkey ✭✭

    Appreciate all of the assistance.

    @Justin Workman - No, they are not currently the Assigned To person.  They may have been at one point in time but not currently.  Our analysts are not currently not using the "Time Worked" function for ALL requests.

    @Brian_Wiest - Thanks.   Maybe Cireson just needs to add the "Action Log" as a searchable field/area?

    @Shane_White - Yes, they are adding content to the Action Log area.   How do I search for an analysts work there?   I want to find all tickets that I've contributed work to.  Created By, I can find.  Resolved By, I can find.  Assigned To, I can find.    But, tickets that I've only done some work to by adding to Action Log.  reassigning, etc..  cannot find.


    Jason

  • Shane_WhiteShane_White Cireson Support Advanced IT Monkey ✭✭✭
    @Jason_Meyer

    As far as I am aware you can search for these things using SQL and by using PowerShell.
    The SQL Tables that contain the History of the WorkItem fall under the Service Manager Databases that start with dbo.MT_System$WorkItem and end in _Log.
    With PowerShell you can run Get-SCSMObject History.

    If you do this through SQL Although it would be a heavy query you could build a dashboard around this to show all tickets you have contributed to.

    Thanks,
    Shane
  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    This will work or at least guide you in the right direction.

    https://community.cireson.com/discussion/3420/query-all-incidents-that-a-user-has-touched
  • Chris_Chekaluk1Chris_Chekaluk1 Customer Adept IT Monkey ✭✭
    I would suggest taking a look at MTV_System$WorkItem$TroubleTicket$AnalystCommentLog view in the ServiceManager db.  This view will contain the Analyst Comments and has properties for EnteredBy, EnteredDate, and Comment.  There is a relationship type called System.WorkItemHasCommentLog (GUID = 79D27435-5917-B0A1-7911-FB2B678F32A6) that you should be able to use to join to the appropriate WorkItem (I'd suggest using the BaseManagedEntity table as you can get the WI id from the Name property).

    One thing is a bit confusing is that the AnalystCommentLog class refers to 'TroubleTicket', which implies only Incident & Problem (not SR, CR, RR, etc.). Don't worry, this view also holds SR related analyst comments as well.

    Sadly, I am not seeing a relationship type to the User CI record, but only the 'EnteredBy' property.  In some cases, I am seeing the User's DisplayName property other times the domain\userid (not sure why the difference though).
  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    @Chris_Chekaluk1 - The difference probably depends on how it was entered.  It's not a relationship, it's just a string property on that ActionLog Entry object.
  • Chris_Chekaluk1Chris_Chekaluk1 Customer Adept IT Monkey ✭✭
    @Justin_Workman - yeah... maybe difference between console/portal & Exchange connector...
  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    @Justin_Workman - yeah... maybe difference between console/portal & Exchange connector...
    Exactly! :smiley:
  • Jason_MeyerJason_Meyer Customer Adept IT Monkey ✭✭

    Hmm, not something that the average analyst is going to be able to do.  Unfortunate.


    Cireson should make this part of the existing search capabilities.  My opinion, analysts should be able to 'search' any part of a request.  Probably easier said than done, but seems like a basic requirement.

Sign In or Register to comment.