ServiceDesk

 View Only
  • 1.  Report help

    Posted Jan 14, 2013 05:20 PM

    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



  • 2.  RE: Report help

    Posted Jan 15, 2013 09:43 AM

    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.



  • 3.  RE: Report help

    Posted Jan 15, 2013 11:27 AM

    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?



  • 4.  RE: Report help
    Best Answer

    Posted Jan 15, 2013 11:37 AM

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

    Process.ProcessEnded as [Process.ProcessEnded],

     



  • 5.  RE: Report help

    Posted Jan 15, 2013 11:46 AM

    quick and easy!  Thanks!