Retrieve Tier Description within a sql query

Justin_Clarke

Hi all,

I have a requirement to be able to associate an owner to each of our support groups(Tier Queues). This will be used to create dashboards showing open IR/SR's and who the owner of the support group is. Was thinking i could use the description field to store the owner in and be able to query this via sql to create my dashboards.

Does anyone know if the support group description is synced into the servicemanagement database or how i could pull this information?

Also any other options are welcome. Other thought was a custom field but thought this might be worth a try first

Thanks in advance.

Justin

Justin_Workman

@Justin_Clarke - Yep.  I added Status description and not Support Group description.  My bad.  It's fixed up now and should be what you're after.

Geoff might be right though.  If you did it his way, you'd have an actual user object as the owner as opposed to just a string description.  

Justin_Workman

Added.

Justin_Workman

@Justin_Clarke
I've attached a query against the ServiceManager database that I think might fetch what you're after.  Should work in a dashboard too.   

Justin_Clarke

Hi @Justin_Workman ,

Thanks for the query. Looks good but the owner field seems to be showing the status and not the description of the support group. Screenshot of where i have set the description FYI and an output of the query on a dev server. 





Thanks for you help. 

Geoff_Ross

Hi Justin,

I think you would be better off creating a custom class that has a support group property, you can link to the existing Incident Tier Queue Enum, and then an owner field (you get this for free if you build a custom Config Item class). Then you can look this up when you create your work item report.

Geoff

Justin_Workman

@Justin_Clarke - Yep.  I added Status description and not Support Group description.  My bad.  It's fixed up now and should be what you're after.

Geoff might be right though.  If you did it his way, you'd have an actual user object as the owner as opposed to just a string description.  

Justin_Clarke

Thanks @Justin_Workman This query worked a treat.

I will look into the Custom Class @Geoff_Ross if i require more than just reporting data. thanks for the input. 

Justin_Clarke

Thanks again for the query, 

Just a question, is there any easy way to pull the last modified date out of the service manager DB?

Justin_Workman

Added.

Justin_Clarke

Thanks Justin for your quick response.