Query All Incidents That A User Has Touched

Chris_Keander · December 2017

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."

Conner_Wood · December 2017

Wubba Lubba Dub Dub!

DECLARE @UserToAudit nvarchar(255) = 'DOMAIN\SUPERCOOLUSER';

WITH USER_TRANSACTIONS AS
(
	SELECT [EntityTransactionLogId]
		  ,[ContextGenerated]
          ,[TimeAdded]
  FROM [ServiceManager].[dbo].[EntityTransactionLog] (nolock)

  WHERE ContextGenerated LIKE @UserToAudit
)
,HistoryToAudit AS
(
  --Incident Class Properties.  You may want to add your own extended incident classes as well
  SELECT [EntityChangeLogId]
      ,[ChangeType]
      ,[EntityTransactionLogId]
	  ,[EntityId]
  FROM [ServiceManager].[dbo].[EntityChangeLog] ECL (nolock)

  JOIN [ServiceManager].[dbo].[ManagedType] MT (nolock)
  ON ECL.[EntityTypeId] = MT.[ManagedTypeId]
  AND MT.[TypeName] IN ('System.WorkItem.Incident')
  AND ECL.RelationshipId IS NULL

  UNION ALL
  --WorkItem/Incident Relationships, the ones you'd want to know on an incident if touched (Affected User, Assigned To, Primary Owner, etc.)
  SELECT [EntityChangeLogId]
      ,[ChangeType]
      ,[EntityTransactionLogId]
	  ,[EntityId]
  FROM [ServiceManager].[dbo].[EntityChangeLog] ECL (nolock)

  JOIN [ServiceManager].[dbo].[BaseManagedEntity] BME (nolock)
  ON BME.[BaseManagedEntityId] = ECL.[EntityId]

  JOIN [ServiceManager].[dbo].[ManagedType] MT (nolock)
  ON BME.[BaseManagedTypeId] = MT.[ManagedTypeId]
  AND MT.[TypeName] IN ('System.WorkItem.Incident')

  JOIN [ServiceManager].[dbo].[RelationshipType] RT (nolock)
  ON ECL.[RelationshipId] IS NOT NULL
  AND RT.[RelationshipTypeId] = ECL.[EntityTypeId]
  AND RT.[RelationshipTypeName] IN ('System.WorkItemHasCommentLog'
  ,'System.WorkItem.TroubleTicketHasAnalystComment'
  ,'System.WorkItem.TroubleTicketHasUserComment'
  ,'System.WorkItemHasActionLog'
  ,'System.WorkItem.TroubleTicketHasActionLog'
  ,'System.WorkItemAffectedUser'
  ,'System.WorkItemAssignedToUser'  
  ,'System.WorkItemCreatedByUser'
  ,'System.WorkItemHasParentWorkItem'
  ,'System.WorkItemClosedByUser'
  ,'System.WorkItemAboutConfigItem' -- This is used by both Affected Services and Affected Items
  ,'System.WorkItemRelatesToConfigItem'
  ,'System.WorkItemRelatesToWorkItem'
  ,'System.WorkItemContainsActivity'
  ,'System.WorkItemHasFileAttachment'
  ,'System.WorkItem.TroubleTicketResolvedByUser'
  ,'System.WorkItem.TroubleTicketClosedByUser'
  ,'System.WorkItem.IncidentPrimaryOwner')
)
SELECT DISTINCT HTA.[ChangeType]
      ,IR.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] AS 'ID'
	  ,IR.[DisplayName] AS 'Incident Display Name'
      ,UT.[ContextGenerated] AS 'Audited User'
	  ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 'MM/dd/yyyy h:mm:ss tt') AS 'Touched Date (Local Time)'
FROM HistoryToAudit HTA

JOIN USER_TRANSACTIONS UT
ON UT.[EntityTransactionLogId] = HTA.[EntityTransactionLogId]

JOIN [ServiceManager].[dbo].[MTV_System$WorkItem$Incident] IR (nolock)
ON IR.[BaseManagedEntityId] = HTA.[EntityId]

ORDER BY [Touched Date (Local Time)] DESC

It's messy but it gets the general idea across.

Conner_Wood · December 2017

Well, I admit I was using SQL 2012, so didn't think about you not having that functionality..... in that case you might need to find another way to get UTC Date Time to Local Time in a preferable format.

Maybe try using CONVERT on DATETIME.

See if you can replace that line with the following instead.

	  , REPLACE(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 101) 
	  + SUBSTRING(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 22), 9 , 20)
	    , '  ',' ') AS 'Touched Date (Local Time)'

Glad I helped, Good Luck.

Conner_Wood · December 2017

The Data Warehouse.... nope. The history logs are not archived.

Chris_Keander · December 2017

How about the ServiceManager or ServiceManagement DBs to get something more recent?

Conner_Wood · December 2017

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_Keander · December 2017

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_Wood · December 2017

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_Wood · December 2017

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.....

Conner_Wood · December 2017

Wubba Lubba Dub Dub!

DECLARE @UserToAudit nvarchar(255) = 'DOMAIN\SUPERCOOLUSER';

WITH USER_TRANSACTIONS AS
(
	SELECT [EntityTransactionLogId]
		  ,[ContextGenerated]
          ,[TimeAdded]
  FROM [ServiceManager].[dbo].[EntityTransactionLog] (nolock)

  WHERE ContextGenerated LIKE @UserToAudit
)
,HistoryToAudit AS
(
  --Incident Class Properties.  You may want to add your own extended incident classes as well
  SELECT [EntityChangeLogId]
      ,[ChangeType]
      ,[EntityTransactionLogId]
	  ,[EntityId]
  FROM [ServiceManager].[dbo].[EntityChangeLog] ECL (nolock)

  JOIN [ServiceManager].[dbo].[ManagedType] MT (nolock)
  ON ECL.[EntityTypeId] = MT.[ManagedTypeId]
  AND MT.[TypeName] IN ('System.WorkItem.Incident')
  AND ECL.RelationshipId IS NULL

  UNION ALL
  --WorkItem/Incident Relationships, the ones you'd want to know on an incident if touched (Affected User, Assigned To, Primary Owner, etc.)
  SELECT [EntityChangeLogId]
      ,[ChangeType]
      ,[EntityTransactionLogId]
	  ,[EntityId]
  FROM [ServiceManager].[dbo].[EntityChangeLog] ECL (nolock)

  JOIN [ServiceManager].[dbo].[BaseManagedEntity] BME (nolock)
  ON BME.[BaseManagedEntityId] = ECL.[EntityId]

  JOIN [ServiceManager].[dbo].[ManagedType] MT (nolock)
  ON BME.[BaseManagedTypeId] = MT.[ManagedTypeId]
  AND MT.[TypeName] IN ('System.WorkItem.Incident')

  JOIN [ServiceManager].[dbo].[RelationshipType] RT (nolock)
  ON ECL.[RelationshipId] IS NOT NULL
  AND RT.[RelationshipTypeId] = ECL.[EntityTypeId]
  AND RT.[RelationshipTypeName] IN ('System.WorkItemHasCommentLog'
  ,'System.WorkItem.TroubleTicketHasAnalystComment'
  ,'System.WorkItem.TroubleTicketHasUserComment'
  ,'System.WorkItemHasActionLog'
  ,'System.WorkItem.TroubleTicketHasActionLog'
  ,'System.WorkItemAffectedUser'
  ,'System.WorkItemAssignedToUser'  
  ,'System.WorkItemCreatedByUser'
  ,'System.WorkItemHasParentWorkItem'
  ,'System.WorkItemClosedByUser'
  ,'System.WorkItemAboutConfigItem' -- This is used by both Affected Services and Affected Items
  ,'System.WorkItemRelatesToConfigItem'
  ,'System.WorkItemRelatesToWorkItem'
  ,'System.WorkItemContainsActivity'
  ,'System.WorkItemHasFileAttachment'
  ,'System.WorkItem.TroubleTicketResolvedByUser'
  ,'System.WorkItem.TroubleTicketClosedByUser'
  ,'System.WorkItem.IncidentPrimaryOwner')
)
SELECT DISTINCT HTA.[ChangeType]
      ,IR.[Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] AS 'ID'
	  ,IR.[DisplayName] AS 'Incident Display Name'
      ,UT.[ContextGenerated] AS 'Audited User'
	  ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 'MM/dd/yyyy h:mm:ss tt') AS 'Touched Date (Local Time)'
FROM HistoryToAudit HTA

JOIN USER_TRANSACTIONS UT
ON UT.[EntityTransactionLogId] = HTA.[EntityTransactionLogId]

JOIN [ServiceManager].[dbo].[MTV_System$WorkItem$Incident] IR (nolock)
ON IR.[BaseManagedEntityId] = HTA.[EntityId]

ORDER BY [Touched Date (Local Time)] DESC

It's messy but it gets the general idea across.

Chris_Keander · December 2017

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_doyle · December 2017

@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_Keander · December 2017

Product Version:          10.50.6220.0
Product Name:             SQL Server 2008 R2
Product Level:            SP3
Product Edition:          Standard Edition (64-bit)

Conner_Wood · December 2017

Well, I admit I was using SQL 2012, so didn't think about you not having that functionality..... in that case you might need to find another way to get UTC Date Time to Local Time in a preferable format.

Maybe try using CONVERT on DATETIME.

See if you can replace that line with the following instead.

	  , REPLACE(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 101) 
	  + SUBSTRING(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), UT.[TimeAdded]), 22), 9 , 20)
	    , '  ',' ') AS 'Touched Date (Local Time)'

Glad I helped, Good Luck.

Chris_Keander · December 2017

That worked!

Conner_Wood · December 2017

@Chris_Keander I made it a bit better, it would double space sometimes between date and time....

Alex_Marsh · January 2019

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_Meyer · May 2019

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.

Query All Incidents That A User Has Touched

Best Answers

Answers

Query All Incidents That A User Has Touched

Best Answers

Answers

CIRESON COMMUNITY WEB SITE