Query All Incidents That A User Has Touched

Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭

Management is interested to find a list of Incidents that a specific user (analyst) has touched, even if the Incident was not assigned to them.

Is it possible to do this via a SQL script against the Data Warehouse?

"Is there any way to see what tickets were modified by a specific user within SCSM? Not just ones they created, but any they made edits to, either moved them queues, or added comments to tickets that weren't in their name? They are an analyst."


Best Answers

Answers

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    The Data Warehouse.... nope.  The history logs are not archived.
  • Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    How about the ServiceManager or ServiceManagement DBs to get something more recent?
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    Yes, using ServiceManager to query history change entity logs on a particular ticket is possible.  Same goes for getting relationship changes on a ticket.  I haven't dealt with that in a long time.... last time was years ago making a C# program.

    I mine as well post the code snippets, it's more to show how MS structured the history logs.....
    //This part takes forever because we have to query SCSM for each incident to retrieve a history log, so if we get 500 recent incidents, we have to query 500 times..... Microsoft failed big time here......
    //incidentStatusCountsData.GenerateIncidentHistoryEntries(recentIncidents, emg);
    
            public void GenerateIncidentHistoryEntries(List<EnterpriseManagementObject> incidents, EnterpriseManagementGroup emg)
            {
                //These variables are referenced by our tasks!
                this.emg = emg;
                recentIncidents = incidents;
                incidentHistoryLogs = new Dictionary<Guid, List<EnterpriseManagementObjectHistoryTransaction>>();
    
                //Create Task list
                List<Task> tasks = new List<Task>();
                foreach (EnterpriseManagementObject incident in recentIncidents)
                {
                    if (IsValidIncident(incident))
                    {
                        tasks.Add( new Task(() => GetIncidentHistoryLog(incident.Id)) );
                    }
                }
                //We start all tasks and wait for them to complete.
                Parallel.ForEach(tasks, task => task.Start());
                Task.WaitAll(tasks.ToArray());
    
                //Now that we've gotten all incident history logs, for each incident we need to go through said incident history log.
                //Because this is going to increment the same numbers multiple times, we should do this sequentially to avoid failure.
                foreach(KeyValuePair<Guid, List<EnterpriseManagementObjectHistoryTransaction>> kvp in incidentHistoryLogs)
                {
                    EnterpriseManagementObject incident = recentIncidents.FirstOrDefault(i => i.Id.Equals(kvp.Key));
                    if (incident != null)
                    {
                        AnalyzeIncidentStatusHistory(incident, kvp.Value);
                    }
                }
            }
    
            /// <summary>
            /// Called from a different thread, so we need to invoke stuff to access main thread
            /// </summary>
            private void GetIncidentHistoryLog(Guid incidentId)
            {
                List<EnterpriseManagementObjectHistoryTransaction> list = new List<EnterpriseManagementObjectHistoryTransaction>();
                foreach(EnterpriseManagementObjectHistoryTransaction h in emg.EntityObjects.GetObjectHistoryTransactions(recentIncidents.FirstOrDefault(i => i.Id.Equals(incidentId))))
                {
                    if (h != null && h.DateOccurred != null)
                    {
                        list.Add(h);
                    }
                }
                if(list.Count > 0)
                {
                    incidentHistoryLogs.Add(incidentId, list.OrderBy(h => h.DateOccurred).ToList());
                }
            }
    
            /// <summary>
            /// Takes the incident and the corresponding history log associated with it and determines if it fits within the recent incident range and the status it changed to at the end of each day if any.
            /// Logic :
            /// nCreated == Incident Created Date (Ensure this date falls within our recentDate range, otherwise we exclude it from the data, we don't add it anywhere)
            /// nOpen == Status was changed and is not Resolved or Closed at End of Day.
            /// nResolved == Status is set to Resolved and does not change at End of Day
            /// nClosed == Status is set to Closed and does not change at End of Day
            /// </summary>
            public void AnalyzeIncidentStatusHistory(EnterpriseManagementObject incident, List<EnterpriseManagementObjectHistoryTransaction> incidentHistoryEntries)
            {
                if (recentIncidentStats.ContainsKey(((DateTime)(incident[null, "CreatedDate"].Value)).ToLocalTime().Date))
                {
                    //This incident was recently created
                    recentIncidentStats[((DateTime)(incident[null, "CreatedDate"].Value)).ToLocalTime().Date].nCreated += 1;
                }
                //Now we deal with the History logs, go through all history entries and pull out all status changes with corresponding date and what the status became.
                List<IncidentStatusChange> statusChanges = new List<IncidentStatusChange>();
                //string m = incident[null,"Id"].Value.ToString() + " Status History:\n";
    
                foreach(EnterpriseManagementObjectHistoryTransaction historyEntry in incidentHistoryEntries)
                {
                    foreach(EnterpriseManagementObjectHistory oHistory in historyEntry.ObjectHistory.Values)
                    {
                        //We want to look through Class properties, not relationships.
                        foreach(EnterpriseManagementObjectClassHistory cHistory in oHistory.ClassHistory)
                        {
                            //Do we have a status change
                            if (cHistory.PropertyChanges.Keys.Any(a => a.Id.Equals(statusProperty.Id)))
                            {
                                statusChanges.Add(new IncidentStatusChange(historyEntry.DateOccurred.ToLocalTime(), TryGetEnumId(cHistory.PropertyChanges[statusProperty].First), TryGetEnumId(cHistory.PropertyChanges[statusProperty].Second)));
                            }
                        }
                    }
                }
                //Now that we got all status changes with the dates from Entire Incident History Log, find out what end of each day status (at 5 PM what was the status).  (Meaning we see the last status change on a date)
                // Only list once -- nResolved, nClosed
                // List every day -- nOpen
                TimeSpan startOfDay = new TimeSpan(7, 0, 0);
                TimeSpan endOfDay = new TimeSpan(17, 0, 0);
                Guid previousStatusId = Guid.Empty;
                //We use previous entry in endOfDateStatus to determine if we mark it as changed (if they are the same we do nothing and move onto next date
                foreach(DateTime recentDate in recentIncidentStats.Keys)
                {
                    List<IncidentStatusChange> validHourStatusChanges = statusChanges.Where(s => s.whenChanged.Date.Equals(recentDate) && TimeSpan.Compare(s.whenChanged.TimeOfDay, startOfDay) >= 0 && TimeSpan.Compare(s.whenChanged.TimeOfDay, endOfDay) <= 0).OrderBy(s => s.whenChanged).ToList();
                    if (validHourStatusChanges.Count > 0)
                    {
    
                        //We only want the last entry as that will be considered our status change (note, it has to be a valid enum id in our incidentStatusList)
                        Guid endOfDayStatusId = validHourStatusChanges.Last().ToStatusId;
                        if(incidentStatusList.Any(s => s.Id.Equals(endOfDayStatusId)))
                        {
                            //Determine which status we add to.
                            ManagementPackEnumeration endOfDayStatus = incidentStatusList.First(s => s.Id.Equals(endOfDayStatusId));
    
                            if(endOfDayStatus.Name.Equals(incidentStatusResolvedName))
                            {
                                recentIncidentStats[recentDate].nResolved += 1;
                            }
                            else if (endOfDayStatus.Name.Equals(incidentStatusClosedName))
                            {
                                recentIncidentStats[recentDate].nClosed += 1;
                            }
                            else if (!endOfDayStatus.Name.Equals(incidentStatusResolvedName) && !endOfDayStatus.Name.Equals(incidentStatusClosedName))
                            {
                                recentIncidentStats[recentDate].nOpen += 1;
                            }
                        }
                    }
                }
                //We completed each end of day status for this incident and charted it correctly
            }
    


    Jeez..... ok, enough of that, luckily there are some posts out there:
    Entity Change Log The Service Manager CMDB does a great job of tracking all of the changes made to it

    So yes, you could get the history and as long as it hasn't been groomed yet you will be able to track any changes made to a ticket!
  • Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    So I assume I would use a query that contains:

    select * from EntityTransactionLog where ????

    But I'm clueless as to how I would search that log for a particular user in AD.  Any help here?

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    I would need to research and build the SQL query as I do not have exactly what you want.

    Although I did just find an interesting bit of C# code of mine I forgot about that has some SQL.....
    The main purpose was "Get a list of certain tickets with the most recent user who modified it along with the date they modified it"
            private string GetTicketQueryType()
            {
                switch (cboOpenTicketType.SelectedIndex)
                {
                    default:
                    case 0:
                        return @"SELECT IR.[BaseManagedEntityId]
                                 FROM [dbo].[MTV_System$WorkItem$Incident] AS IR (nolock)
                                 JOIN[dbo].[EnumType] AS statusEnum (nolock)
                                 ON statusEnum.EnumTypeId = IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED
                                 AND
                                 (
                                    statusEnum.EnumTypeName != 'IncidentStatusEnum.Closed' 
                                    AND statusEnum.EnumTypeName != 'IncidentStatusEnum.Resolved'
                                 )";
                    case 1:
                        return @"SELECT SR.[BaseManagedEntityId]
                                 FROM[dbo].[MTV_System$WorkItem$ServiceRequest] AS SR (nolock)
                                 JOIN[dbo].[EnumType] AS statusEnum(nolock)
                                 ON statusEnum.EnumTypeId = SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F
                                 AND
                                 (
                                    statusEnum.EnumTypeName != 'ServiceRequestStatusEnum.Closed'
                                    AND statusEnum.EnumTypeName != 'ServiceRequestStatusEnum.Completed'
                                    AND statusEnum.EnumTypeName != 'ServiceRequestStatusEnum.Canceled'
                                 )";
                    case 2:
                        return @"SELECT CR.[BaseManagedEntityId]
                                 FROM [dbo].[MTV_System$WorkItem$ChangeRequest] AS CR (nolock)
                                 JOIN[dbo].[EnumType] AS statusEnum (nolock)
                                 ON statusEnum.EnumTypeId = CR.Status_72C1BC70_443C_C96F_A624_A94F1C857138
                                 AND
    							 (
    								statusEnum.EnumTypeName != 'ChangeStatusEnum.Closed' 
    								AND statusEnum.EnumTypeName != 'ChangeStatusEnum.Completed' 
    								AND statusEnum.EnumTypeName != 'ChangeStatusEnum.Cancelled'
    							 )";
                    case 3:
                        return @"SELECT PR.[BaseManagedEntityId]
                                 FROM [dbo].[MTV_System$WorkItem$Problem] AS PR (nolock)
                                 JOIN[dbo].[EnumType] AS statusEnum (nolock)
                                 ON statusEnum.EnumTypeId = PR.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187
                                 AND
    							 (
    								statusEnum.EnumTypeName != 'ProblemStatusEnum.Resolved'
    								AND statusEnum.EnumTypeName != 'ProblemStatusEnum.Closed'
    							 )";
                    case 4:
                        return @"SELECT RR.[BaseManagedEntityId]
                                 FROM [dbo].[MTV_System$WorkItem$ReleaseRecord] AS RR (nolock)
                                 JOIN[dbo].[EnumType] AS statusEnum (nolock)
                                 ON statusEnum.EnumTypeId = RR.Status_F7BFD782_80B2_10C2_04B3_7F4C042DB5D2
                                 AND
    							 (
    								statusEnum.EnumTypeName != 'ReleaseStatusEnum.Closed'
    								AND statusEnum.EnumTypeName != 'ReleaseStatusEnum.Completed'
    								AND statusEnum.EnumTypeName != 'ReleaseStatusEnum.Cancelled'
    							 )";
                }
            }
    
    
    
                //Get our data for Open Tickets of Ticket Type Query which includes
                //Gets a list of certain tickets with the most recent user who modified it along with the date they modified it and other 1:1 user relationships
                SqlCommand cmd = new SqlCommand(@"SELECT ECL.EntityId
    	                                                ,ECL.LastModified AS 'LastModifiedByUserDate'
    	                                                ,IIF(RecordedUser.DisplayName IS NULL, UPPER(ETL.ContextGenerated), RecordedUser.DisplayName) AS 'LastModifiedBy'
    	                                                ,IIF(AssignedTo.DisplayName IS NULL, 'N/A', AssignedTo.DisplayName) AS 'AssignedTo'
    	                                                ,IIF(CreatedByUser.DisplayName IS NULL, 'N/A', CreatedByUser.DisplayName) AS 'CreatedBy'
                                                FROM EntityChangeLog ECL (nolock)
    
                                                JOIN
                                                (
                                                    SELECT  ECL.EntityId AS 'EntityID'
                                                            ,MAX(ECL.LastModified) AS 'LastModifiedByUserDate'
                                                    FROM
                                                    (
                                                    " + GetTicketQueryType() + @"
                                                    ) AS Tickets
    	
    	                                            JOIN [dbo].[EntityChangeLog] ECL (nolock)
    	                                            ON Tickets.BaseManagedEntityId = ECL.EntityId
    
    	                                            JOIN [dbo].[EntityTransactionLog] ETL (nolock)
    	                                            ON ECL.EntityTransactionLogId = ETL.EntityTransactionLogId
    	                                            WHERE UPPER(ETL.ContextGenerated) NOT IN (SELECT UPPER(Domain + '\' + UserName) AS 'ContextGenerated' FROM [dbo].[CredentialManagerSecureStorage] (nolock))
                                                    GROUP BY ECL.EntityId
                                                ) AS MostRecentUserLog ON MostRecentUserLog.EntityID = ECL.EntityId
                                                AND  MostRecentUserLog.LastModifiedByUserDate = ECL.LastModified
    
                                                JOIN [dbo].[EntityTransactionLog] ETL (nolock)
                                                ON ECL.EntityTransactionLogId = ETL.EntityTransactionLogId
    
                                                LEFT JOIN
                                                (
    	                                            SELECT UPPER([Domain_E36D56F2_AD60_E76E_CD5D_9F7AB51AD395] + '\' + [UserName_6AF77E23_669B_123F_B392_323C17097BBD]) AS 'ContextGenerated'
    			                                            ,[DisplayName]
    	                                            FROM [dbo].[MT_System$Domain$User] (nolock)
                                                ) AS RecordedUser ON RecordedUser.ContextGenerated = UPPER(ETL.ContextGenerated)
    
                                                LEFT JOIN 
                                                (
    	                                            SELECT R.SourceEntityId AS 'BaseManagedEntityId'
    		                                                ,AssignedUser.DisplayName
    	                                            FROM [dbo].[Relationship] R (nolock)
    	                                            JOIN [dbo].[RelationshipType] rType (nolock)
    	                                            ON rType.RelationshipTypeId = R.RelationshipTypeId
    	                                            JOIN [dbo].[BaseManagedEntity] AssignedUser (nolock)
    	                                            ON AssignedUser.BaseManagedEntityId = R.TargetEntityId
    	                                            WHERE rType.RelationshipTypeName = 'System.WorkItemAssignedToUser'
                                                ) AS AssignedTo ON AssignedTo.BaseManagedEntityId = ECL.EntityId
    
                                                LEFT JOIN 
                                                (
    	                                            SELECT R.SourceEntityId AS 'BaseManagedEntityId'
    		                                                ,CreatedByUser.DisplayName
    	                                            FROM [dbo].[Relationship] R (nolock)
    	                                            JOIN [dbo].[RelationshipType] rType (nolock)
    	                                            ON rType.RelationshipTypeId = R.RelationshipTypeId
    	                                            JOIN [dbo].[BaseManagedEntity] CreatedByUser (nolock)
    	                                            ON CreatedByUser.BaseManagedEntityId = R.TargetEntityId
    	                                            WHERE rType.RelationshipTypeName = 'System.WorkItemCreatedByUser'
                                                ) AS CreatedByUser ON CreatedByUser.BaseManagedEntityId = ECL.EntityId", dbConnection);
    
                //Load our data
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
    
    

    You best start reading about The Log Tables section, it goes over what to look for and explains what certain values mean...
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    I should mention there's something awfully stinky.... if the [dbo].[EntityChangeLog] table has "RelationshipId=NULL" then EntityTypeId is a class.  But if dbo].[EntityChangeLog] table has a RelationshipId then EntityTypeId actually represents the [RelationshipTypeId].

    Good news @Chris_Keander it looks like I might be making a similar query afterall.....
  • Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    Thank you immensely for helping with this.


    I entered the domain\username at the top of the script and executed it but immediately got this error:

    Msg 195, Level 15, State 10, Line 67
    'FORMAT' is not a recognized built-in function name.

    Line 67 is:        ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 'MM/dd/yyyy h:mm:ss tt') AS 'Touched Date (Local Time)'

    Any suggestions?
  • john_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    @Chris_Keander ;
    That's strange as FORMAT was introduced in SQL Server 2012. What version of SQL Server are you using and what compatibility level is the database at (right-click on the Service Manager and choose Properties and then Options) ? If the compatibility level is 110 or higher then FORMAT should be available.
  • Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    Product Version:          10.50.6220.0
    Product Name:             SQL Server 2008 R2
    Product Level:            SP3
    Product Edition:          Standard Edition (64-bit)


  • Chris_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    That worked!
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    @Chris_Keander I made it a bit better, it would double space sometimes between date and time....

  • Alex_MarshAlex_Marsh Premier Partner Advanced IT Monkey ✭✭✭
    Just come across this after an end user mentioned it during some training I was performing last week. Is this something which could be possible to implement in a dashboard?
  • Jason_MeyerJason_Meyer Customer Adept IT Monkey ✭✭
    Way out of my element here, but couldn't Cireson implement a search function in the Action Log or the HISTORY areas of requests?  Then we could just search for the analysts name anywhere in these areas.
Sign In or Register to comment.