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

  • Question

  • 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.  
    Wednesday, March 27, 2019 10:50 PM

All replies

  • You poor bastich.  Well, let's get started!

    The Live SCSM Database, "ServiceManager" stores data until it's purged via history retention.  If history retention is shorter than ticket lifespan, your tickets will never get purged.

    The Archive, "DWDataMart", syncs from the Live Database... the archived data remains until it too is purged, oh yes, I bet you weren't expecting that .... SCSM Data Warehouse: Fact Groom Retention - Prevent Archived Relationships Getting Deleted

    I recommend you use the SCSM Entity Explorer tool  to help you.

    Also the Cireson SQL forums can help you in the right direction which appears you've already found, the knowledge there is still valid, despite being old.

    If you want an example of SR queries, download the SCSM Reviewer Portal and view my source code.  You will find SCSM lacks even the most basic of features and you'll be creating custom things to meet business requirements.  I recommend you learn some C# and PowerShell while you attempting to master the SQL.

    Thursday, March 28, 2019 3:04 PM
  • Oh, thank you.   I know C# very well -  good with sql as well.   Issue is just understanding these schemas etc..  Since some other devs already created dozens of SSRS reports, I'm sure that is what client users will want to still use.   Perhaps I can see what other web development I can also do with this data.   The bottom line is really just for me to understand the schemas.   

    I will download and take a look at the things you provided.  Thanks !  

    Thursday, March 28, 2019 5:44 PM
  • You could also look in to the ServiceManagement database (The database that the Cireson portal caches everything to)

    This is a much easier layout and easier to understand how it all hangs together.

    You can also take a look at all of the "Out of the Box" queries that Cireson provides as part of the Analytics component of their portal.

    Hope this helps.

    Brett Moffett

    Brett Moffett

    Monday, April 15, 2019 3:14 PM