Looking to Query ALL OPEN SR (service request) before they are copied to the Data Warehouse

Thomas_Stickel

As a Senior Developer in Fullstack web dev,  I'm being tasked with understanding SCSM and all its glory.   We pay for Cireson -  however my question is really around how to find queries, how to create custom queries that are going to give me ALL open Service Request tickets - most likely before they are even hitting the data warehouse.   I suppose the data warehouse is fine too as i am understanding it is updated in 20 minutes to 2 hours.   Looking to really grasp how to make sense of the databases to write effective queries.  

Joe_Burrows

Hi Thomas

If you are using the Cireson Portal, id recommend having a look at the workitem table in the servicemanagement database. The cireson cachebuilder services copies workitem data here from the servicemanager database every 1 minute and makes querying much simpler then if you were to query the servicemanager or servicemanagerDW directly.

Regards
Joe

Joe_Burrows

Hi Thomas

Drop down lists are referred to as enumerations, so what you will want to do is JOIN the enumerationID with the displaystring of the desired enumeration to show the localization value.

Depending on the reporting requirements, its definitely easier to use the ServiceManagement database with most data in here. Id also recommend using portal dashboards on custom pages with the SQL or OData widgets rather the SSRS reports. See here for more information on these: 

Vimeo tutorial:
https://vimeo.com/272447387

OData:
https://support.cireson.com/KnowledgeBase/View/1401#/
https://support.cireson.com/KnowledgeBase/View/1400#/

SQL:
https://support.cireson.com/KnowledgeBase/View/1291#/
https://support.cireson.com/KnowledgeBase/View/1292#/

Hope that helps
Regards
Joe

Gerhard_Goossens

This should get you started with the ServiceManagement DB

select wi.WorkItemId,       

       wi.Title,

   wi.Description,

   FORMAT(wi.Created, 'dd-MM-yyy hh:mm') as Created,

   dsstatus.DisplayString AS Status,

   dssupport.DisplayString AS SupportGroup,

   wi.AssignedUser, 

   wi.AffectedUser, 

   dsclassification.DisplayString AS Classification

from WorkItem wi

LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 

LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID

LEFT JOIN [Enumeration] enumclass ON enumclass.EnumerationID = wi.CategoryId

LEFT JOIN [DisplayString] dsclassification ON dsclassification.ElementID=enumclass.EnumerationID

LEFT JOIN [Enumeration] enumstatus ON enumstatus.EnumerationID = wi.StatusId

LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=enumstatus.EnumerationID

where  wi.StatusId IN('A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '72B55E17-1C7D-B34C-53AE-F61F8732E425', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') 

AND dssupport.DisplayString in ('Facilities')

The last line filters for the specific Support Group, you can comment that to show all active SR's

Brian_Wiest

To me it really depends on the use case of business need, and they drives what database to target for a report.

1. ServiceManager -

      Pros Basically the master database. Will always provide the most real time results.

      Cons All workflows run against this database, adding load for reporting/dashboards can affect the performance of workflows.

2. ServiceManagement

      Pros Queries can be simpler as the Cache builder is providing the ETL so it removes of the join requirements

      Cons The one minute delay if your use case cannot allow that time frame variance

3. DWDataMart

     Pros removes all queries processing requirements off your production systems.

     Cons It is based on the ETL jobs from ServiceManager so this can be 1 hour off

In the end we push all real time dashboards to ServiceManagement since if the cache builder is not working your analysts cannot work either. And everything in SCSM runs on 1 minute time frames. Any scheduled report for point in time metrics/management summary reports are off the datamart, as basically the moment it is run and produced to the POC it is out of date.

HTH

Joe_Burrows

Hi Thomas

If you are using the Cireson Portal, id recommend having a look at the workitem table in the servicemanagement database. The cireson cachebuilder services copies workitem data here from the servicemanager database every 1 minute and makes querying much simpler then if you were to query the servicemanager or servicemanagerDW directly.

Regards
Joe

Thomas_Stickel

How about if i wanted to see what "group" is assigned to it?    For example,  we have a group called Facilities.
So while it seems that most people use the data warehouse for generating SSRS reports -  It seems that a lot of what is needed is probably in the Cireson ServiceManagement tables, right?    I'm hoping that is the case as the ServiceManager and warehouse tables and views are not so pleasant to use.   Is there a schema or some queries the community tends to use (with ServiceManagement database) for custom queries and SSRS reports ?   Thanks in advance.   Tom

Thomas_Stickel

I can see that when I am filling out a new Service Request, there are a few labels with Facilities ,    1. Classification (drop down list )   and the other is   Support Group (drop down list )    -   this is in reference to my previous question in which I said "group"    -  so I just wanted to help clarify . thx

Joe_Burrows

Hi Thomas

Drop down lists are referred to as enumerations, so what you will want to do is JOIN the enumerationID with the displaystring of the desired enumeration to show the localization value.

Depending on the reporting requirements, its definitely easier to use the ServiceManagement database with most data in here. Id also recommend using portal dashboards on custom pages with the SQL or OData widgets rather the SSRS reports. See here for more information on these: 

Vimeo tutorial:
https://vimeo.com/272447387

OData:
https://support.cireson.com/KnowledgeBase/View/1401#/
https://support.cireson.com/KnowledgeBase/View/1400#/

SQL:
https://support.cireson.com/KnowledgeBase/View/1291#/
https://support.cireson.com/KnowledgeBase/View/1292#/

Hope that helps
Regards
Joe

Gerhard_Goossens

This should get you started with the ServiceManagement DB

select wi.WorkItemId,       

       wi.Title,

   wi.Description,

   FORMAT(wi.Created, 'dd-MM-yyy hh:mm') as Created,

   dsstatus.DisplayString AS Status,

   dssupport.DisplayString AS SupportGroup,

   wi.AssignedUser, 

   wi.AffectedUser, 

   dsclassification.DisplayString AS Classification

from WorkItem wi

LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 

LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID

LEFT JOIN [Enumeration] enumclass ON enumclass.EnumerationID = wi.CategoryId

LEFT JOIN [DisplayString] dsclassification ON dsclassification.ElementID=enumclass.EnumerationID

LEFT JOIN [Enumeration] enumstatus ON enumstatus.EnumerationID = wi.StatusId

LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=enumstatus.EnumerationID

where  wi.StatusId IN('A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '72B55E17-1C7D-B34C-53AE-F61F8732E425', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') 

AND dssupport.DisplayString in ('Facilities')

The last line filters for the specific Support Group, you can comment that to show all active SR's

Thomas_Stickel

Wow, that is a lot of joins, but it seems to work.   1. I'm shocked it takes that many joins ( I am not saying it doesn't need it )   2.  Those StatusId in the WorkItem table ,... all those specific 4 guids -  how in the world do you know it is those, and how would I actually have been able to even figure that out?        Feels like i'm severely lacking an understanding of this schema.   Thanks again   

Thomas_Stickel

There is a guy at work who has been "handling" service manager for 2 - 3 years and Cireson for the past year.  I was trying to write queries to pull data from the data warehouse yesterday before my posting as the data was literally a week old.  Come to find out, an update /patch a week ago killed the processes for archiving and so he is trying to fix it. 

Meanwhile I mentioned to him that I was told that the cache builder I'm told by Cireson Devops that it updates workitem every minute.    -  Since he already built a lot of SSRS reports with the DW, he is resisting any change and says "The cachebuilder you cannot rely on, its dynamic"  -  thus he wants me to NOT use ServiceManagement for SSRS report as he doesn't trust that the CacheBuilder to update regularly as Joe has said.     He is not a real developer, and essentially our operations manager is a developer as am I... so since the guy is constantly messing things up - we take what he says a bit with a grain of salt.    So besides the office politics -  I just want to make sure that I can truly rely on ServiceManagement database tables to get updated in a timely fashion since that is ideal in my opinion.    Please share your thoughts on doing SSRS reporting  ( lightweight - nothing that hardly anyone is going to even pull up )   against Cireson ServiceManagement Database as opposed to digging into those DW Views ...
thanks in advance
Tom

Joe_Burrows

Thomas_Stickel said:

There is a guy at work who has been "handling" service manager for 2 - 3 years and Cireson for the past year.  I was trying to write queries to pull data from the data warehouse yesterday before my posting as the data was literally a week old.  Come to find out, an update /patch a week ago killed the processes for archiving and so he is trying to fix it. 

Meanwhile I mentioned to him that I was told that the cache builder I'm told by Cireson Devops that it updates workitem every minute.    -  Since he already built a lot of SSRS reports with the DW, he is resisting any change and says "The cachebuilder you cannot rely on, its dynamic"  -  thus he wants me to NOT use ServiceManagement for SSRS report as he doesn't trust that the CacheBuilder to update regularly as Joe has said.     He is not a real developer, and essentially our operations manager is a developer as am I... so since the guy is constantly messing things up - we take what he says a bit with a grain of salt.    So besides the office politics -  I just want to make sure that I can truly rely on ServiceManagement database tables to get updated in a timely fashion since that is ideal in my opinion.    Please share your thoughts on doing SSRS reporting  ( lightweight - nothing that hardly anyone is going to even pull up )   against Cireson ServiceManagement Database as opposed to digging into those DW Views ...
thanks in advance
Tom

Hi Tom

I would agree and keep SSRS reports to the datawarehouse DB - most SCSM admins have many DW scars and avoid it like the plague where possible though - as with all its moving parts \ sync processes it easily breaks.

My response/answer was more if you were to avoid the clunky datawarehouse and SSRS reports all together and use portal dashboards\analytics, or if you were simply looking to query results then using servicemanagement is much simpler. 

Good luck on your SCSM journey, plenty of community members here to help you along the way

Regards
Joe

Brian_Wiest

To me it really depends on the use case of business need, and they drives what database to target for a report.

1. ServiceManager -

      Pros Basically the master database. Will always provide the most real time results.

      Cons All workflows run against this database, adding load for reporting/dashboards can affect the performance of workflows.

2. ServiceManagement

      Pros Queries can be simpler as the Cache builder is providing the ETL so it removes of the join requirements

      Cons The one minute delay if your use case cannot allow that time frame variance

3. DWDataMart

     Pros removes all queries processing requirements off your production systems.

     Cons It is based on the ETL jobs from ServiceManager so this can be 1 hour off

In the end we push all real time dashboards to ServiceManagement since if the cache builder is not working your analysts cannot work either. And everything in SCSM runs on 1 minute time frames. Any scheduled report for point in time metrics/management summary reports are off the datamart, as basically the moment it is run and produced to the POC it is out of date.

HTH