Home Analyst Portal

Documentation or training on the SQL databases contents

DSVellaDSVella Member IT Monkey ✭

Good afternoon all;

I have been tasked with pulling in data from Service Manager into our companies Data Warehouse. I have had some success with the analytics database and some tables in the management database. However management have asked for some more complex metrics (for example Incidents that have changed level or number of related items to a given work item).

My goal is to create some comprehensive materialised views in our Data warehouse to serve management their reports.

I could really use some documentation or training on the SQL schema of the system so I can get more useful information out and into managements hands. I have looked around the site but I cannot find anything.

Best Answer

Answers

  • DSVellaDSVella Member IT Monkey ✭

    Adam,

    Thanks for your reply, I have been busy with the DWDataMart database as i didn't even know it existed!

    While I have been able to get 90% of my current requirements by querying the various tables and views in the DW, I'm short one last thing, the history tab. I am looking to query this to answer the business question "How many incidents changed level (positively or negatively) during its life. My research suggests that SCSM does not move the ticket's history to the DW by default.

    I will create a separate thread about capturing the history data.

  • Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
    edited February 19

    No problem. There is a lot of power to be found there especially when you add SCOM and SCCM into the mix.

    History can mean a couple things in SCSM (although I get the one you're after) -

    • The Action Log? There is actually a community MP for that. Requires a very small edit if you want it to pull Problems.
    • The actual History (Entity Change Log) table when you view the History tab on an object in SCSM? You're right, that doesn't make it to the DW. ECL only exists in the operational ServiceManager DB and is responsible for recording all of the changes made to an object in SCSM.

    But since you keep coming back to "how many times an Incident changed level" and I can only assume you mean Incident Tier Queue (commonly referred to as Support Group) the good news is that there is a post from Marcel on this very topic that I was alluding to earlier.

Sign In or Register to comment.