SQL table/query to show data for "Support Group" for Change Requests
Mina_Saidi
Customer IT Monkey ✭
Hi Team,
We are looking at creating reports to create an overview of change requests within our section.
As i had extended the class to include "Support Group" within the CR template, the support group doesnt show when our inhouse report developer tries to create queries. He cant locate the tables.
unlike IR and SR where its easy to find the "support Group" tables in the SQL database, we can not locate the tables where the data for support group is written too.
Does anyone know where the tables for Support group in CRs are written too, or any queries that we can use to show support group data for CRs?
Below is the thread which involves the discussion around extending the class. The support group works and everything is fine but we dont know which table it reads to in SQL.
https://community.cireson.com/discussion/4674/extending-a-class#latest
Any help or guidance will be much appreciated.
Kind Regards
Answers
Which Database are you looking in? ServiceManager or ServiceManagement?
We are looking at servicemanagement dbo. Should we be looking at another one? Regards
If you have a new-ish version of the Portal, you should see an additional set of tables in ServiceManagement that bring in more data around work items and also class extensions (which this one is). Do you see a table called "csnCached_MT_System_WorkItem_ChangeRequest"? If so, it should have a SupportGroup column (or whatever you named it).
Hi @steve_tuel Thank you. i asked our report developer to have a look and he responded with the below; "I had a look at the DWDataMart (the database that is used for reporting) and also the CMDWDataMart for the table csnCached_MT_System_WorkItem_ChangeRequest and couldn’t find it. The tables appear in alphabetical order and it’s not viewable by me as can be seen in the partial list shown below"I have attached the image. also we are using portal version: 8.9.5.2016
Management Pack Version: 7.7.2016.218
is there any further areas he should be looking? it just doesn't seem to appear to him. we really need to be able to generate report based on support groups for CR's but cant seem to find it anywhere in the SQL database.
@Mina_Saidi The table "csnCached_MT_System_WorkItem_ChangeRequest" Steve is referring to is located in the Cireson ServiceManagement database - not in the datawarehouse or Microsoft ServiceManager database