Tables for SLAs

Brian_Winter

We have 2 SLAs for Priority 1 Incidents.

1) Timer on Acknowledge

2) Timer on Resolve

I want to write a report to show both of these metrics. It looks like Analytics only has one SLA. I couldn't find the two metrics in the ServiceManagement database and I know they're hidden in the ServiceManager db. Just can't find them.

How do I make this happen?

Thanks!

Shane_White

Hi @Brian_Winter

How is this query to start with? I wasn't sure what your end goal was but you can query the Service Manager Database to get all SLO information:

SELECT SLA.[DisplayName] AS [Display Name], SLA.TargetEndDate_4F17E5C2_86D5_05E8_35DE_6E012567DAB7 AS [Target End Date], IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS Id

 FROM [ServiceManager].[dbo].[MT_System$SLA$Instance$TimeInformation] SLA

 join Relationship REL

 on REL.TargetEntityId = SLA.BaseManagedEntityId

 join MTV_System$WorkItem$Incident IR

 on REL.SourceEntityId = IR.BaseManagedEntityId

Let me know if it helps.

Thanks,

Shane

Peter_Miklian

@Brian_Winter I see columns Created, FirstAssignedDate, FirstResponseDate, ResolvedDate in [ServiceManagement].[dbo].[WorkItem].

These attributes can be found in SM_WorkItem_Incident table in CiresonAnalytics database, too. Don't you want to use these instead of accessing ServiceManager/ServiceManagement databases? CiresonAnalytics is meant for reporting and you can publish Dashboards from it.

Brian_Winter

I'm actually trying to get the SLA metric (Met, Breached). Since we can "pause" the clock, so to speak, We can't just look at Created Date delta Resolved/FirstResponse/etc. For those Work Items that only have one SLA, Analytics works perfectly. It's those Work Items that have two or more SLAs that isn't fully captured in Analytics.

Shane_White

Hi @Brian_Winter

How is this query to start with? I wasn't sure what your end goal was but you can query the Service Manager Database to get all SLO information:

SELECT SLA.[DisplayName] AS [Display Name], SLA.TargetEndDate_4F17E5C2_86D5_05E8_35DE_6E012567DAB7 AS [Target End Date], IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS Id

 FROM [ServiceManager].[dbo].[MT_System$SLA$Instance$TimeInformation] SLA

 join Relationship REL

 on REL.TargetEntityId = SLA.BaseManagedEntityId

 join MTV_System$WorkItem$Incident IR

 on REL.SourceEntityId = IR.BaseManagedEntityId

Let me know if it helps.

Thanks,

Shane

Brian_Winter

That's the query I was looking for! I hate that I can't get that information from Analytics, but it is what it is.