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

Thomas_StickelThomas_Stickel Customer IT Monkey ✭
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.  

Best Answers

  • Joe_BurrowsJoe_Burrows Cireson Devops Super IT Monkey ✭✭✭✭✭
    Accepted Answer
    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_BurrowsJoe_Burrows Cireson Devops Super IT Monkey ✭✭✭✭✭
    Accepted Answer
    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_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    Accepted Answer
    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

Answers

  • Joe_BurrowsJoe_Burrows Cireson Devops Super IT Monkey ✭✭✭✭✭
    Accepted Answer
    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_StickelThomas_Stickel Customer IT Monkey ✭
    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_StickelThomas_Stickel Customer IT Monkey ✭
    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_BurrowsJoe_Burrows Cireson Devops Super IT Monkey ✭✭✭✭✭
    Accepted Answer
    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_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    Accepted Answer
    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_StickelThomas_Stickel Customer IT Monkey ✭
    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_StickelThomas_Stickel Customer IT Monkey ✭
    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_BurrowsJoe_Burrows Cireson Devops Super IT Monkey ✭✭✭✭✭
    edited March 28
    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
Sign In or Register to comment.