One of the “missing” features concerning reporting in SCSM is there are no reports for Service Requests. I think it was a year ago I wrote a need bundle with some reports.
Now since this came up with another customer and to be honest there are some parts (very big understatement!)missing here I decided to write the whole sjizzle shebang.
and when I mean the whole sjizzle shebang I mean the whole nine yards!
Thanks MS I can now write C#, SQL Queries, XML, Management Packs, Bundles, DW Extensions, DW views, tables, temp tables and my lord can I join tables and views endlessly!
I can honestly say Developer makes more sense these days than consultant but don’t understand me wrong I love writing code and all the rest! Whoop, whoop!
If that is the new popular term “Devops” than I have been a Devops man since MOM 2005
I won’t go into detail in how you can get everything into packs etc. but will quickly point out some nice things which might be handy and why you want these extensions in your DW
I know there are the cubes which are great for long term reporting but to be honest although they like it at first they want to be able to go into details, including dates and times something which simply isn’t in the cubes. So back to reporting since writing and extending DW I can now do in my dreams. Yeehaaa
People who know me I tend not to take the easy way but only go the whole nine yards! So prepare for a journey into how I got these reports!
Action logs – Analyst/User Comments logs in Datawarehouse
First of all there are no action logs / comment logs in the Data Warehouse…yes that makes 0,0 sense when your manager wants to know what happened and when.
To get these logs into the dw a simple extension is sufficient to re-use over and over again.
http://gallery.technet.microsoft.com/SCSM-Action-Logs-user-e2c87c1f
Once you have imported these extensions and synchronized the dw you are ready to start writing reports including comments, actionlogs!
After importing in SCSM run the MPsync job:
Right after this you could wait endlessly for the other jobs to kick in but Travis to the rescue, he wrote a script which kicks of the jobs in sequence for you with a little PowerShell magic:
http://gallery.technet.microsoft.com/PowerShell-Script-to-Run-a4a2081c
Don’t forget to remove the cube jobs since this will not improve the running of the sync as we all now.
Simply run the script after the MPSync job finishes and there you go, although now I think of it they might kick in right after MPSync job has run??? Well I want to be sure, so use the script
Woohoo Actionlogs in the DW how nice!
Now for a little back ground there are also logs for activities in these extensions! Why?
Take a look at a runbook activity and there they are and we want these as well!
Result in report:
Actual Start Date – Actual End Date
Nice there is an actual start and end date property on every activity, that’s cool I can use these!
So when a manager or customer asks you can you report on how long it took for an activity to complete you all yell “Yes off course actual start and end date!”’
Below a quick table what is actually logged in the DB:
| Activity | Actual Start Date | Actual End Date | |
| Service Request | |||
| Manual | No | No | |
| Review | No | Yes | |
| Parallel | No | No | |
| Sequential | No | No | |
| Runbook | Yes | Yes |
Yes this is it!
pretty scary there is an actual end date on review activities but never a start……
Nice another challenge, you could use ActivityStatusDurationFactvw but this is more complicated for the normal user and even worse where exactly get I get these in a view? Or use SLO on the activities?
Yes you cannot until now.
I have written a couple of workflows which actually populates the actual Start Date and Actual end Date!
http://gallery.technet.microsoft.com/SCSM-Actual-Start-and-End-2c6d40e2
Simply import the management pack and you can now use the actual start and end date in your views as well, pretty cool!
What about Lists or enumerations in reports?
If you have joined three tables you will be able to view the area of a service request by display name in your report. Nice! But what if you have multiple area’s named the same but under a different branch?
Let me explain by example:
In a report you cannot tell if it is a Linux or Windows Account since it will only show “User Account”.
A solution for this is to create a custom view in the Data Warehouse which stores the entire name:
CREATE
VIEW dbo.ServiceRequestAreaHierarchy AS WITH _Items as ( SELECT ServiceRequestAreaId Id, ParentId, d.DisplayName, Ordinal, 1 AS Level, cast(‘:’ + cast(ServiceRequestAreaId as varchar) + ‘:’ as varchar (100)) AS Struc , cast(‘\’ + cast(d.DisplayName as varchar) + ‘\‘ as varchar (100)) AS FullName FROM dbo.ServiceRequestArea AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = ‘ENU‘ AND d.ElementName = t.Id WHERE ParentId = 1 UNION ALL SELECT ServiceRequestAreaId Id, t.ParentId, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(ServiceRequestAreaId as varchar)+ ‘:‘ as varchar(100)) AS Struc , cast(i.FullName + cast(d.DisplayName as varchar) + ‘\‘ as varchar(100)) AS FullName FROM dbo.ServiceRequestArea AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = ‘ENU‘ AND d.ElementName = t.Id JOIN _Items i on i.Id = t.ParentId ) SELECT ISNULL(Parent.Id,Child.Id) ParentId, ISNULL(Parent.DisplayName,Child.DisplayName) ParentClassification, Child.ID ChildId, Child.DisplayName Classification, Child.Ordinal , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) FullName FROM _Items Child LEFT OUTER JOIN _Items AS Parent ON Parent.ID = Child.ParentId
This will create the view and now you can report on the full name which would be Linux\User Account
instead of User Account!
I cannot remember exactly where I got this solution it was somewhere on the TechNet Forums I think, if you recognize the solution please let me know and I will add your credits!
How about Runbook Activities?
By default there are no runbook activities in the Data Warehouse. When you want to report on these you can use the ActivityDimVw but this will not have the runbook specific settings like which runbook and more runbook activity specific information.
Since we are interested in these properties as well I have written an extension to report on these as well!
http://gallery.technet.microsoft.com/SCSM-Data-Warehouse-e8e9bb25
Just Import the management pack and you will extend your datawarehouse with runbook activities:
Once you start writing your queries there is one little catch in here.
if you want to join the tables you need to join on
runbookitemdimvw.id on RunbookItemAutomationActivityDimvw.Runbookid
you can try but his is not going to work because in runbookitemdimvw this property is a string and in the RunbookItemAutomationActivityDimvw this is a guid!
LEFT OUTER JOIN runbookitemdimvw rbitem ON CAST( SUBSTRING(rbitem.id, 1, 8) + ‘-‘ + SUBSTRING(rbitem.id, 9, 4) + ‘-‘ + SUBSTRING(rbitem.id, 13, 4) + ‘-‘ + SUBSTRING(rbitem.id, 17, 4) + ‘-‘ + SUBSTRING(rbitem.id, 21, 12) AS UNIQUEIDENTIFIER)
Which actually converts the string to ID, really Cool !
Wrap up
With this post and the solutions you will prepare your environment to do some really cool reporting sjizzle! I promised you the whole shebang so wait for part two which will go into the reports!
Happy Authoring,
Oskar Landman
