3 Replies Latest reply on Aug 24, 2016 3:54 AM by Peter Weighill

    Querying the database

    Apprentice

      I am trying to create a report using a custom application and need to query the ServiceDesk Database to find the following information.

      1. Number of incidents opened in a given month

      2. Number of incidents closed in a given month

      3. Number of change tickets opened in a given month

      4. Number of change tickets closed in a given month

       

      The issue I have after looking through the database is that I cannot see what tables give the actual open date and closed date information.

       

      Can anyone help?

       

      -Ryan

        • 1. Re: Querying the database
          ITSMMVPGroup

          If you have a look through this landing page it should get you started.  Several articles talk about using crystal;l, but don't worry about that the database information is still relevant for any reporting tool.

           

          Ivanti Service Desk Reporting Frequently Asked Questions

          • 2. Re: Querying the database
            Jamie Cannon ITSMMVPGroup

            In response to "The issue I have after looking through the database is that I cannot see what tables give the actual open date and closed date information."

             

            The Open date for a ticket is going to be under pm_process.creationdate.  You will need to match some guids from the process table to the incident, change, problem, request tables to get the actual reference.

             

            The closed date is typically inside the module table.  For example:  Incident closures will be im_incident_closures or im_incident_resolutions.

            • 3. Re: Querying the database
              Peter Weighill Specialist

              Here's some queries to do what you want.  If a ticket has been closed more than once, only the last closure is counted.

               

              declare @StateDate datetime = '1 Aug 2016'

              declare @EndDate datetime = '31 Aug 2016'

               

              select count(*) as incidents_created

              from pm_process p

              inner join im_incident i on p.pm_guid=i.pm_guid

              where p.pm_creation_date>[email protected]

              and p.pm_creation_date<dateadd(d,1,@EndDate)

               

              select count(*) as incidents_closed

              from im_incident_closure c

              where c.im_id = (select max(im_id) from im_incident_closure c2 where c2.im_incident_guid=c.im_incident_guid)

              and c.im_creation_date>[email protected]

              and c.im_creation_date<dateadd(d,1,@EndDate)

               

              select count(*) as changes_created

              from pm_process p

              inner join cm_change c on p.pm_guid=c.pm_guid

              where p.pm_creation_date>[email protected]

              and p.pm_creation_date<dateadd(d,1,@EndDate)

               

              select count(*) as changes_closed

              from cm_change_closure c

              where c.cm_id = (select max(cm_id) from cm_change_closure c2 where c2.cm_change_guid=c.cm_change_guid)

              and c.cm_creation_date>[email protected]

              and c.cm_creation_date<dateadd(d,1,@EndDate)