How can I remove duplicates from the Incident_SLA table?

[Tracy Davis]

I am trying to create a report of incident counts from the Incident_SLA table. There are duplicate rows for incidents (I'm assuming when the SLA is paused and restarted? which still makes no sense to me).

Does anyone know how to get a clean count of incidents from this table with unique Incident numbers?

I need the count to match a report using the Incident table - but I need to use the SLA table in order to measure if SLA was met.

[Adam Stout]

Starting and restarting shouldn't add more rows but canceling and starting a new one does (i.e., changing priority).  Additional SLAs (i.e., one for response and one for resolution) will cause multiple as well, that is very much on purpose.

Logically, the counts typically do not match, why do you need them to?  If an incident met a response SLA but not a resolution, do you want to count that as met, not net, or one of each?  Typically, we'll use separate indicators per SLA type but you still may have multiple if one SLA is canceled and another fired (changing priorities).  If you filtered out canceled and you separate by SLA definition, you may only have per incident (but that may depend on how you defined your SLA).

In any case, can your report just count distinct incident number?

[Tracy Davis]

I think the duplicates are from changes in priority, although honestly it's hard to tell, the records look almost identical.

I'm not seeing an SLA type or definition field? I do see the stage field is canceled on some. I'll do some more analysis and see if filtering these out works.

I have a dashboard with multiple reports and PA widgets on it. Some use the incident table, and a few the incident_sla table. I need the counts to match. The reports were defined by the business so for the reports using the incident_sla table, I need just one record count for each incident.

It's the resolution SLA they're interested in, although I don't see a way to differentiate the SLA type.

[Adam Stout]

On the incident_sla view, there should be a field called "SLA Definition" this is a reference to the SLA that is being tracked.  It isn't pretty, but normally you can put a condition on SLA definition CONTAINS resolution to only get the SLAs you are interested in.

In most (but not all) SLA metrics, we normally remove canceled, so I would filter those out as well.

[Tracy Davis]

Nope, no field called SLA Definition unfortunately. We're on Jakarta. Maybe something changed? Although that would be odd.

There's only one state field, I've already filtered out canceled so assume this is removing cancelled incidents.