Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Report help

Created: 14 Jan 2013 • Updated: 15 Jan 2013 | 4 comments
This issue has been solved. See solution.

Somebody helped me create a report awhile back that shows me incidents created in the off hours and closed by a certian group of individuals.  I'm being asked to add the date these incidents were created.  Can someone take a look and show me how to include this in the below query?

Thanks!

select distinct

Process.ReportLogProcessID as [Process.ReportLogProcessID],

Process.ReportProcessID as [Process.ReportProcessID],

Process.ProcessStarted as [Process.ProcessStarted],

Process.ProcessTitle as [Process.ProcessTitle],

Process.Result as [Process.Status],

DATEPART(hh, Process.ProcessStarted) as [Process.ProcessStartedHour],

DATENAME(dw, Process.ProcessStarted) as [Process.ProcessStartedDayOfWeek],

Process.SessionID as [Process.SessionID],

Process.ProcessViewerPageID as [Process.ProcessViewerPageID],

ServiceDeskIncidentManagement0.process_id as [Incident.ID],

ServiceDeskIncidentManagement0.priority as [Incident.Priority],

ServiceDeskIncidentManagement0.classification_category_01 as [Incident.Category1],

(SELECT TOP 1 [PrimaryEmail] FROM [User] WHERE ServiceDeskIncidentManagement0.resolved_by_user_id = [UserID]) as [Incident.ResolvedByUser],

User1.PrimaryEmail as [UserTable.PrimaryEmail],

User1.FirstName as [UserTable.FirstName],

User1.LastName as [UserTable.LastName],

ReportProcessReference5.[Name] as [ReportProcessReference.ReferenceName.Location]

from

ReportProcess as Process inner join

ServiceDeskIncidentManagement as ServiceDeskIncidentManagement0 with (NOLOCK) on

((ServiceDeskIncidentManagement0.process_id = Process.ReportProcessID)) inner join

ReportProcessContact as ReportProcessContact1 on

((ReportProcessContact1.SessionID = Process.SessionID and

ReportProcessContact1.IsPrimary = 'True')) left outer join

[User] as User1 on

((ReportProcessContact1.ReferenceID = User1.UserID)) left outer join

[Group] as Group1 on

((ReportProcessContact1.ReferenceID = Group1.GroupID and

ReportProcessContact1.ReferenceType = 2)) left outer join

UserAddress as UserAddress4 on

((User1.UserID = UserAddress4.UserID)) left outer join

ReportProcessReference as ReportProcessReference5 on

((ReportProcessReference5.SessionID = Process.SessionID and

(ReportProcessReference5.ReferenceType = 'Location'))) left outer join

ReportProcessHistory as ReportProcessHistory6 on

((ReportProcessHistory6.SessionID = Process.SessionID))

where

('3DCA0263-D5EF-48ae-BF96-A161989DFB8E' = '3DCA0263-D5EF-48ae-BF96-A161989DFB8E' and

Process.ProcessStarted > '1/1/2012 12:00:00 AM' and

Process.ProcessStarted <= '12/31/2012 11:59:59 PM' and

((DATENAME(dw, Process.ProcessStarted) = 'Saturday' or DATENAME(dw, Process.ProcessStarted) = 'Sunday') or

(DATENAME(dw, Process.ProcessStarted) = 'Friday' and DATENAME(hh, Process.ProcessStarted) > 15) or

(DATENAME(dw, Process.ProcessStarted) = 'Monday' and DATENAME(hh, Process.ProcessStarted) < 7)) and

(ServiceDeskIncidentManagement0.resolved_by_user_id = '6fd81737-304a-4b85-aaf8-50e3a77f5531' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '22e9d1d6-e1b7-4586-b660-01c5cc01b1eb' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '0922686f-33c2-4f8e-9991-e0bc7f0aae2e' or

ServiceDeskIncidentManagement0.resolved_by_user_id = 'fb4c6258-b485-41a9-b634-597611761c6c' or

ServiceDeskIncidentManagement0.resolved_by_user_id = 'f2861566-391d-4907-8a60-e8f164552041' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '4219fa95-2db5-4a04-ac4c-919e696ef909' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '6654e2fd-cf8f-458d-9845-df0efba41703' or

ServiceDeskIncidentManagement0.resolved_by_user_id = '09c943ff-63f1-439a-803c-c3a0521450be'))

order by

[Process.ReportProcessID] asc

Comments 4 CommentsJump to latest comment

michael.george's picture

Seems to me the start date is already in the query. Third column. "Process.ProcessStarted as [Process.ProcessStarted]". Just ran this (replacing a userid or two) against my 7.1 DB and it looked okay to me. This will need to be changed slightly if/when you go to 7.5, FYI.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

Brian W.'s picture

I'm sorry, I typed the wrong thing.  I was asked for date created and date resolved.  I was easily able to add the process started field in the report since the data is being queried.  Can you help me get date resolved?

michael.george's picture

Sure thing. Just add another line after the process started line with:

Process.ProcessEnded as [Process.ProcessEnded],

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

SOLUTION