none
Affected User Report RRS feed

  • Question

  • I'm trying to get a report that will pull all the tickets for an affected user. I do not see this in the console. Is there not such a report?
    Monday, April 20, 2015 2:18 PM

Answers

  • I'm a firm believer in over delivering. That said, the following will get the average time to resolution per Incident. However before it's possibly seen as "That is not even close to what I was asking Adam!" I want to draw your attention to the kind of results this is going to pull first.

    • 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), the Incident classification, the Employee, the Employee's Department, the Hours and Minutes to resolve

    Based on the above, in the Where clause you'll be able to decide what you want/don't want in your result set. The SQL query will make more sense contextually (I hope?) but suffice it to say you could do a Where clause of only showing a specific Affected User, or a specific Department, or a specific Source, etc. To be honest, the genesis of the SQL query was I wanted something to show me all Incidents broken up by Active Directory Department. Obviously this means you have to have the AD User's Department attribute defined and thus in turn would be synced to the SCSM DB, and thus DW.

    Additionally I cheated in a few areas and didn't do all of the SQL joins that I probably should have, but it still gets the job done. It goes without saying for onlookers of this thread - help make this better! Never the less, I hope you find this useful.

    DECLARE @StartDate datetime, @EndDate datetime
    SET @StartDate = '03-01-2015'; 
    SET @EndDate = '03-31-2015'
    
    SELECT	IncidentDimvw.Id as [Incident Number], au.Department as [Dept], REPLACE(IncidentDimvw.Status, 'IncidentStatusEnum.', '') as 'Status', IncidentDimvw.Title, 
    			incidentdimvw.ResolutionDescription,
    			IncidentClassificationvw.IncidentClassificationValue as 'Category', au.displayname as 'Employee', 
    			datediff(minute, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) / 60 as [Hours to Resolve],
    			datediff(second, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) % 60 as [Minutes to Resolve]
    FROM	UserDimvw as au 
    		INNER JOIN WorkItemAffectedUserFactvw 
    			ON au.UserDimKey = WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey 
    		INNER JOIN WorkItemDimvw 
    			ON WorkItemAffectedUserFactvw.WorkItemDimKey = workitemdimvw.WorkItemDimKey
    		INNER JOIN EntityDimvw as c
    			ON c.EntityDimKey = WorkItemDimvw.EntityDimKey
    		INNER JOIN IncidentDimvw
    			ON c.EntityDimKey = IncidentDimvw.EntityDimKey
    		INNER JOIN IncidentClassificationvw 
    			ON IncidentClassificationId = IncidentDimvw.Classification_IncidentClassificationId 
    
    WHERE	(IncidentDimvw.id is not null) 
    		and (au.Department not like 'Information Systems')
    		and (IncidentDimvw.Source not like 'IncidentSourceEnum.SCOM')
    		and (au.DisplayName = 'Adam Dzak')
    		and ((incidentdimvw.CreatedDate >= @StartDate) and (incidentdimvw.CreatedDate < @EndDate + 1))
    GROUP BY Incidentdimvw.Id, IncidentDimvw.Status, IncidentDimvw.Title,incidentdimvw.ResolutionDescription, au.DisplayName, au.Department, IncidentClassificationvw.IncidentClassificationValue, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate
    ORDER BY Department


    Adam Dzak


    • Edited by Adam_TechNet Monday, April 20, 2015 10:20 PM
    • Marked as answer by Pa Chou Tuesday, April 21, 2015 7:39 PM
    Monday, April 20, 2015 10:19 PM
  • Such a report does not exist out of box - which...believe me I already share your frustration reading that. Regardless, I have such a SQL query for the DW if you're interested?

    Adam Dzak


    • Edited by Adam_TechNet Monday, April 20, 2015 6:25 PM typo
    • Marked as answer by Pa Chou Tuesday, April 21, 2015 7:39 PM
    Monday, April 20, 2015 6:24 PM

All replies

  • Such a report does not exist out of box - which...believe me I already share your frustration reading that. Regardless, I have such a SQL query for the DW if you're interested?

    Adam Dzak


    • Edited by Adam_TechNet Monday, April 20, 2015 6:25 PM typo
    • Marked as answer by Pa Chou Tuesday, April 21, 2015 7:39 PM
    Monday, April 20, 2015 6:24 PM
  • Yes very frustrated. If you don't mind Adam I would like to try your query. Something is better than nothing. Thanks for your help.
    Monday, April 20, 2015 9:04 PM
  • I'm a firm believer in over delivering. That said, the following will get the average time to resolution per Incident. However before it's possibly seen as "That is not even close to what I was asking Adam!" I want to draw your attention to the kind of results this is going to pull first.

    • 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), the Incident classification, the Employee, the Employee's Department, the Hours and Minutes to resolve

    Based on the above, in the Where clause you'll be able to decide what you want/don't want in your result set. The SQL query will make more sense contextually (I hope?) but suffice it to say you could do a Where clause of only showing a specific Affected User, or a specific Department, or a specific Source, etc. To be honest, the genesis of the SQL query was I wanted something to show me all Incidents broken up by Active Directory Department. Obviously this means you have to have the AD User's Department attribute defined and thus in turn would be synced to the SCSM DB, and thus DW.

    Additionally I cheated in a few areas and didn't do all of the SQL joins that I probably should have, but it still gets the job done. It goes without saying for onlookers of this thread - help make this better! Never the less, I hope you find this useful.

    DECLARE @StartDate datetime, @EndDate datetime
    SET @StartDate = '03-01-2015'; 
    SET @EndDate = '03-31-2015'
    
    SELECT	IncidentDimvw.Id as [Incident Number], au.Department as [Dept], REPLACE(IncidentDimvw.Status, 'IncidentStatusEnum.', '') as 'Status', IncidentDimvw.Title, 
    			incidentdimvw.ResolutionDescription,
    			IncidentClassificationvw.IncidentClassificationValue as 'Category', au.displayname as 'Employee', 
    			datediff(minute, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) / 60 as [Hours to Resolve],
    			datediff(second, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate) % 60 as [Minutes to Resolve]
    FROM	UserDimvw as au 
    		INNER JOIN WorkItemAffectedUserFactvw 
    			ON au.UserDimKey = WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey 
    		INNER JOIN WorkItemDimvw 
    			ON WorkItemAffectedUserFactvw.WorkItemDimKey = workitemdimvw.WorkItemDimKey
    		INNER JOIN EntityDimvw as c
    			ON c.EntityDimKey = WorkItemDimvw.EntityDimKey
    		INNER JOIN IncidentDimvw
    			ON c.EntityDimKey = IncidentDimvw.EntityDimKey
    		INNER JOIN IncidentClassificationvw 
    			ON IncidentClassificationId = IncidentDimvw.Classification_IncidentClassificationId 
    
    WHERE	(IncidentDimvw.id is not null) 
    		and (au.Department not like 'Information Systems')
    		and (IncidentDimvw.Source not like 'IncidentSourceEnum.SCOM')
    		and (au.DisplayName = 'Adam Dzak')
    		and ((incidentdimvw.CreatedDate >= @StartDate) and (incidentdimvw.CreatedDate < @EndDate + 1))
    GROUP BY Incidentdimvw.Id, IncidentDimvw.Status, IncidentDimvw.Title,incidentdimvw.ResolutionDescription, au.DisplayName, au.Department, IncidentClassificationvw.IncidentClassificationValue, incidentdimvw.CreatedDate, IncidentDimvw.ResolvedDate
    ORDER BY Department


    Adam Dzak


    • Edited by Adam_TechNet Monday, April 20, 2015 10:20 PM
    • Marked as answer by Pa Chou Tuesday, April 21, 2015 7:39 PM
    Monday, April 20, 2015 10:19 PM
  • This will be very useful for me when my boss request for something like this. Like I said something is better than nothing. Thanks for your help Adam!!
    Tuesday, April 21, 2015 7:39 PM
  • It incentivized me to upload to my TechNet gallery as a "primer" query along with a related blogpost. Glad to help! http://scsmmercenary.blogspot.com/2015/04/sql-reporting-affected-user-report.html

    Adam Dzak

    Wednesday, April 22, 2015 12:16 AM