4 Replies Latest reply on Aug 11, 2016 9:02 AM by phoffmann

    Provisioning Report - Who, When, and What Machine(s)

    ecoidan Specialist

      LDMS 2016 SU3

       

      There is a need to produce a report from LDMS that shows who initiated the provisioning task and what machine it was done on.  There are quarterly audits and the auditors would like to see that type of report.

       

      Report Details:

      Who logged in the PXE Menu (basically whose LDMS User Account started the provisioning scheduled task)

      When was the task performed

      What machine was the task performed on.

       

      The Audit section in LDMS shows the WHO and WHEN but does not list the WHAT MACHINE this was performed on.  Some of the canned reports will show the WHEN and WHAT MACHINE but not the WHO.  

       

       

      Anyone created a report like this or have suggestion in how to do so?

        • 1. Re: Provisioning Report - Who, When, and What Machine(s)
          phoffmann SupportEmployee

          How comfortable would you be in getting your hands dirty with SQL / do you (/are you allowed to) have access to the database in the first place?

           

          Basic information around tasks (assuming you've not deleted them) including "what task - what target - who owns the task" can be found in the LD_TASK table.

           

          The Provisioning history table(s) may contain a bunch of things you're after as well.

           

          I can try to dig into this to get you most of the way fairly quickly assuming you're not afraid of SQL / have actual access to it.

           

          If you don't have access to SQL ... coerce the DBA's with wanton bribes of doughnuts or other food to be nice?

          • 2. Re: Provisioning Report - Who, When, and What Machine(s)
            jrrippel Apprentice

            We actually did this a few months ago - this will only work as long as the task is still in the database.

             

            /****** Script for SelectTopNRows command from SSMS  ******/
            SELECT        PROV_HISTORY_TASK.LD_TASK_IDN, PROV_HISTORY_TASK.DEVICE_NAME, PROV_HISTORY_TASK.DISPLAY_NAME, PROV_HISTORY_TASK.TASK_NAME, PROV_HISTORY_TASK.TASK_OWNER,
                                     PROV_HISTORY_TASK.FIRST_ACTION_QUERY_DATE, PROV_HISTORY_TASK.LAST_ACTION_STATUS_DATE,
                                     CASE TASK_STATUS WHEN 5 THEN 'Success' WHEN 7 THEN 'Failed' WHEN 12 THEN 'In Process' Else 'Other' END AS TASK_STATUS
            FROM            PROV_HISTORY_TASK INNER JOIN
                                     LD_TASK ON PROV_HISTORY_TASK.LD_TASK_IDN = LD_TASK.LD_TASK_IDN
            
            • 3. Re: Provisioning Report - Who, When, and What Machine(s)
              ecoidan Specialist

              I have full access to the SQL server and that script worked perfectly!!  Thank you so much.

              • 4. Re: Provisioning Report - Who, When, and What Machine(s)
                phoffmann SupportEmployee

                <I've made your post a little prettier by making it use the SQL syntax highlighting>

                 

                Nice script - thank you for sharing!