1 Reply Latest reply on Oct 5, 2015 6:38 AM by masterpetz

    OS Provisioning Query

    Alessandro_Antonini Rookie

       

      Hello

       

      I would like to enqury all pcs that have been provisioned by month

       

      I'm using this syntax : Select task_name,DISPLAY_NAME,LAST_ACTION_STATUS_DATE from PROV_HISTORY_TASK where [HISTORY_TASK_STATE] = 4

      and I've got the list pc pcs provisioned ... but

      How can I filter by Month ?

       

      which syntax I've to add ?

       

      thank in adavance

      Best Regards

        • 1. Re: OS Provisioning Query
          masterpetz ITSMMVPGroup

           

          Hi Alessandro

           

           

           

          you can use the following Syntax to split the date to day, month and year. This query would list all devices ordered by year and month descending:

           

          SELECT DISPLAY_NAME, DATEPART(DAY,LAST_ACTION_STATUS_DATE) AS DAY, DATEPART(MONTH,LAST_ACTION_STATUS_DATE) AS Month, DATEPART(YEAR,LAST_ACTION_STATUS_DATE) AS Year FROM PROV_HISTORY_TASK WHERE [HISTORY_TASK_STATE] = 4

           

          GROUP BY DATEPART(DAY,LAST_ACTION_STATUS_DATE), DATEPART(MONTH,LAST_ACTION_STATUS_DATE),DATEPART(YEAR,LAST_ACTION_STATUS_DATE), DISPLAY_NAME

           

          ORDER BY Year DESC, month DESC

           

          If you want to filter for a special month for example September, you have to modify the where part as shown here:

           

          SELECT DISPLAY_NAME, DATEPART(DAY,LAST_ACTION_STATUS_DATE) AS DAY, DATEPART(MONTH,LAST_ACTION_STATUS_DATE) AS Month, DATEPART(YEAR,LAST_ACTION_STATUS_DATE) AS Year FROM PROV_HISTORY_TASK WHERE [HISTORY_TASK_STATE] = 4 AND DATEPART(Month, LAST_ACTION_STATUS_DATE) = 9

           

          GROUP BY DATEPART(DAY,LAST_ACTION_STATUS_DATE), DATEPART(MONTH,LAST_ACTION_STATUS_DATE),DATEPART(YEAR,LAST_ACTION_STATUS_DATE), DISPLAY_NAME

           

          ORDER BY Year DESC, Month DESC

           

           

           

          Hope that helps.

           

           

           

          Kind regards

           

          Christian

           

           

           

          1 of 1 people found this helpful