1 of 1 people found this helpful
Essentially the data required for your Report is available in Audit Forms, but only problem with audit data is it has excess amount of unstructured information, so we need extract required information from the large piece of chunk using String extraction functions. We have added few advanced function in recent releases So in theory it should have been achievable, But i need try it in lab env.
Will try revert back on this
Which version of Smart Reporting your customer is using ?
Thank you for your response! We are using Smart Reporting 18.05.
Were you able to get this report? We have same requirement from customer.
3 of 3 people found this helpful
Sorry for the delayed response,
I tried few combinations and this can be achieved with tricky Query qualifications and Subquery, I have tested on few Records and it works as expected I have tested for Change management but similar process can be followed for any ITSM module.
Please follow below steps -
You need to add CHG:ChangeRequest_AuditLogSystem to OOTB view to get the audit data.
Edit the change management view and add Audit form and join it with Request Id from CHG:Infrastructure Change and Original Request ID from CHG:ChangeRequest_AuditLogSystem
We need four fields from the Audit form please select them and add it in respective field category-
- Audit Date
- Fields Changes
Now our view is ready with required fields. We need to create report with subquery
Create Master query as Change ID, Audit Date and Submitter and add Fields Changed and Log fields into filter section. Defining a filter values correctly is the important part of this mechanism. Define filters values for master query as -
Now the master report will pull all change requests from the system which has set Critical priority atleast once in its life cycle. as we are traversing through Audit data it will also consider historical changes, You can additionally add Status filter to pull only open Requests.
Now add an Append Subquery on same view make join type as inner join and join it with Change ID field. Also add Fields Changed and Log into Subquery Filters
In a child Report add only two fields Audit Date and Submitter, Since we have columns with similar names in both queries. change column names to something relevant. In master query change audit date as Priority Set to Critical on , in Child query change it to Priority set to high on.
Now define filter values in child report as
Fields Changed Like %Priority% AND Log Like %Priority: High%
Here is the final output of Report -
Technically report will fetch only relevant records because different qualification for both queries and inner join in Subquery, Also this report will only track Requests which has lowered from Critical to High, If you need it to be from Critical to any Priority then change subquery filter as Log Not Like '%Priority: Critical%' .
Hope this helps to achieve your requirement, Feel free to revert in case you need any further information.
Thanks, Abhijeet, for taking the time to write this up.
Is it a necessity to use Smart? If you use the Remedy Reporting Console and AR System reports you can use the status history fields to show the status changes.