SQL Reporting - Affected User Report

I'm just going to go ahead and say it - the reports that come out of box with Service Manager does almost nothing in the way of providing value on Day 1.

But then again, neither does complaining about it. So it wouldn't hurt to start sharing some SQL queries to get you going ASAP on your SCSM data warehouse. While their are a host of things you could report on I want to start with a few that you typically would have to do a double take on and say aloud "Wait, that isn't an out of box report?" This is probably going to be the first in a series of said reports so I'll start with something I recently answered on TechNet...

How Do I Get an Affected User Report?

There are a few ways to answer that question, so I'm going to go with the one that gets you a whole lot of data and then leave it to you to remove what you don't want via picking and choosing what's in the SELECT or WHERE statements. Specifically this query will get you the following...

• Get me a list of all Incidents (IR34985, IR34986, etc.)
• For each incident show me the
• Status
• Title
• Resolution Description (will be null if not resolved)
• Incident classification
• Employee (Affected User)
• Employee's Department
• Hours and Minutes to resolve

I like this query because I think it's an absolutely great starting block for building other similar reports such as...

• Show me all Incidents broken up by Department (AD User's Department attribute)
• Show me Average Time to Resolve per Affected User
• Show me Average Time to Resolve per Department
• Show me Average Time to Resolve per Classification
• etc

With that said, this is best viewed as a starting point. It will run on its own as is, but the power is going to come from your selective modifications of it. Additionally, consider this query a quick way to solve the problem. SQL joins aren't done as pleasantly as they could be (i.e. I was lazy and didn't join the Status table and instead just used substrings)

Get to downloading here on TechNet and if you'd like to see the original thread (which is almost verbatim what I typed) you can check that out here.

Update 6.29:
This report was updated on TechNet and I wrote a follow up blogpost as to the "why". You can read it here.