Once you do this please let me know how this was done- :-)
Every Customer wants this .
Saurabh, Can you build a simple query script to fetch result that includes all the status life cycle of an incident in a single row. If that can be achieved we can think of getting time spent
Has anyone solved this query? I need to do the same
1 of 1 people found this helpful
Have you tried using audit log to determine those values? Extracting time of status change and new status value is pretty simple, but calculating differences between consecutive changes and summing them per status value might be a bit more complicated.
Which field from Incident Audit log view is capturing all the statuses of one incident and of course all the timestamp of each interaction?
4 of 4 people found this helpful
Unfortunately, it is not that simple. Records in HPD:HelpDesk_AuditLogSystem get created on each successful submittal and modification of incident -- semicolon-separated list of changed fields is stored in Fields Changed, list of changed fields and their new values in Log field, what looks like Incident Number (though it is actually Entry ID) in Original Request ID, and relevant date in Audit Date.
To extract necessary information, join HPD:HelpDesk_AuditLogSystem form to HPD:Help Desk form using `HPD:Help Desk`.`Entry ID` = `HPD:HelpDesk_AuditLogSystem`.`Original Request ID`, then filter out all records where Fields Changed doesn't contain ";Status;" and you'll be left with audit records which include change of status; to separate them by status value, use calculated fields defined as something like
CASE WHEN `HPD:HelpDesk_AuditLogSystem˙.`Log` LIKE "%Status: <Status Selection Value> (<Status ID>)%" THEN 1
for each possible Status Selection Value and respective Status ID (e.g. Assigned (1)).
Remaining problems are calculating duration of each status state as difference between Audit Date of next change of status value and Audit Date of "current" change of status value, as well as summing all differences for each status value.
If there is anyone interested in an example on how to create report from initial post, have a look at Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1.