Get Last Modified date for PowerBI report from SQL
Chris_Sprague
Customer IT Monkey ✭
I am trying to get the last modified date from the database for reports in PowerBI to be used for request management as well as management level reports. I attempted to use the last modified date from here: http://www.techguy.at/system-center-service-managersql-query-to-get-a-full-list-of-your-incidents/, however, it was not giving me the latest time a comment was added to the request. I think searched through the relationships in the ServiceManager Database and come up with the SQL below. While it gives me the correct date in most cases, there still seems to be some NULL entries. Can someone point out what I am missing?
If needed, I can upload again after I have added comments to the SQL.
Thanks,
Chris Sprague
SCCM/SCSM administrator
City of Richmond
Answers
Hi Chris,
I think you're very close here, and just a couple tables need adjustment. Currently, the table "MT_System$WorkItem$TroubleTicket$ActionLog_Log" is being used, but this table contains quite a bit more than user comments. For example, system messages about review activity voting are stored in this table (and shown in the action log).
Instead of the above table, take a look at the "MT_System$WorkItem$TroubleTicket$UserCommentLog" and "MT_System$WorkItem$TroubleTicket$AnalystCommentLog" tables. These tables store User Comments and Analyst Comments respectively (these are different classes behind-the-scenes!).
For example:
Here we have an Incident with a comment of each type:
Here is how the analyst comment appears SQL-side:
Here is how the user comment appears SQL-side:
Also, if some values are coming over as NULL, make sure you have a case statement to handle work items that do not yet have a user comment.
Hope this helps!
Thanks,
Nick
I had tried using the user and analyst comment log, but I could not get that to have the correct date, no matter what I attempted. I was using the relationship id below.
System.WorkItem.TroubleTicketHasAnalystComment 835a64cd-7d41-10eb-e5e4-365ea2efc2ea AnalystComment AppliesToTroubleTicket
For the Case statement, I was using the MAX function, which I think should return the latest date. I really do not know SQL and was following the example, looking at other relationships and attempting to follow the code as a template. Is the relationship above the right one? I was using the table MT_System$WorkItem$TroubleTicket$AnalystCommentLog_Log and that relationship in place of the action log, dbo.MT_System$WorkItem$TroubleTicket$ActionLog_Log :
SELECT
--Look at all, return the latest
MAX(LastModified) AS Expr1
FROM
(
--check SR change log
SELECT
MT_System$WorkItem$ServiceRequest_Log.EntityChangeLogId
, inc.BaseManagedEntityId
, ecl.LastModified
FROM
dbo.MT_System$WorkItem$ServiceRequest_Log
INNER JOIN
MT_System$WorkItem$ServiceRequest AS INC
ON
INC.BaseManagedEntityId = dbo.MT_System$WorkItem$ServiceRequest_Log.BaseManagedEntityId
LEFT OUTER JOIN
dbo.EntityChangeLog AS ecl
ON
ecl.EntityChangeLogId = MT_System$WorkItem$ServiceRequest_Log.EntityChangeLogId
UNION
--Check SLA instance (in the original, not sure if needed)
SELECT
SLALog.EntityChangeLogId
, inc.BaseManagedEntityId
, EntityChangeLog.LastModified
FROM
dbo.MTV_System$WorkItem$ServiceRequest AS inc
LEFT OUTER JOIN
dbo.Relationship AS rel
ON
rel.SourceEntityId = inc.BaseManagedEntityId
LEFT OUTER JOIN
dbo.MT_System$SLA$Instance$TimeInformation_Log AS SLALog
ON
SLALog.BaseManagedEntityId = rel.TargetEntityId
LEFT OUTER JOIN
EntityChangeLog
ON
SLALog.EntityChangeLogId = EntityChangeLog.EntityChangeLogId
WHERE
(
rel.RelationshipTypeId = 'BA08F9BB-85C7-20D8-05E3-0035938CDDD5'
)
UNION
--Check Action log
SELECT
TroubleTicketLALog.EntityChangeLogId
, inc.BaseManagedEntityId
, EntityChangeLog.LastModified
FROM
dbo.MTV_System$WorkItem$ServiceRequest AS inc
LEFT OUTER JOIN
dbo.Relationship AS rel
ON
rel.SourceEntityId = inc.BaseManagedEntityId
LEFT OUTER JOIN
dbo.MT_System$WorkItem$TroubleTicket$ActionLog_Log AS TroubleTicketLALog
ON
TroubleTicketLALog.BaseManagedEntityId = rel.TargetEntityId
LEFT OUTER JOIN
EntityChangeLog
ON
TroubleTicketLALog.EntityChangeLogId = EntityChangeLog.EntityChangeLogId
WHERE
(
rel.RelationshipTypeId = 'a860c62e-e675-b121-f614-e52fcbd9ef2c'
)
)
I figured it out: relationship must be rel.RelationshipTypeId = '79d27435-5917-b0a1-7911-fb2b678f32a6'
System.WorkItemHasCommentLog
Final Scripts, if anyone else is doing PowerBI custom reports. This is for live, SQL, not DW.
These are made to be joined together and give you combined IR/SR/MA reports.
Combined as table WorkItems
Calculated Column for last modified
#if there is no lastmodified, then set to end date, if no end date, set to opendate.
Last Modified = if(WorkItems[LastModified],WorkItems[LastModified],if(WorkItems[End Date],WorkItems[End Date], WorkItems[Open Date]))