4 Replies Latest reply on Jan 20, 2017 2:43 PM by Eric.Cuthill

    Shavlik Custom SQL query

    kaanfu Rookie

      We are trying to create "Machine View" type of report by using following SQL query:

      --------------

      SELECT

          [DnsName]

      ,[LastPatchAssessedOn]

      ,[LastPatchMachineGroupName]

         ,a.AssignedPolicyName

         ,p.PatchFoundOrInstalled

         ,p.PatchMissing

         ,p.ServicePackMissing

      FROM [Protect].[Reporting].[Machine] AS m

      JOIN [Protect].[Reporting].[PatchCountsByScanMachine] AS p ON p.MachineId = m.LastAssessedMachineStateId

      JOIN [Protect].[Reporting].[Agent] AS a on m.Id = a.MachineId

      ORDER BY DnsName

      ------------------

       

      The output is almost just like "Machine View", but in text view, which is OK.

       

      But for some reason the SQL query is not listing Agentless machines. Does anyone know why?

        • 1. Re: Shavlik Custom SQL query
          kaanfu Rookie

          Basically we need "machine view" type of report every week. Any suggestions?

          • 2. Re: Shavlik Custom SQL query
            Eric.Cuthill SupportEmployee

            One or more of the lines you are joining is causing the results to truncate to only the items that have an agent assigned. If you change the Join Command to "Left Join" for the line below your results will now include Agent less scan results for machines without an assigned policy.

             

             

            Change from this:

            JOIN [Protect].[Reporting].[Agent] AS a on m.Id = a.MachineId

             

            To This:

            Left Join [Protect].[Reporting].[Agent] AS a on m.Id = a.MachineId

            • 3. Re: Shavlik Custom SQL query
              kaanfu Rookie

              This seemed to help. I also need a column that incidates when was the last time patches were installed. Where can i fnd this?

              • 4. Re: Shavlik Custom SQL query
                Eric.Cuthill SupportEmployee

                Adding the last deployment date to the query was a bit of a task but I think the query below should be what you are wanting. I have replaced the DNS name with just the machine name and added the last reported IP address.

                 

                SELECT DISTINCT

                                         Reporting.Machine.Name, Reporting.Machine.LastKnownIP, MAX(Reporting.Machine.LastPatchAssessedOn) AS ScanDate, Reporting.Machine.LastPatchMachineGroupName,

                                         Reporting.Agent.AssignedPolicyName, Reporting.PatchCountsByScanMachine.PatchFoundOrInstalled, Reporting.PatchCountsByScanMachine.PatchMissing,

                                         Reporting.PatchCountsByScanMachine.ServicePackMissing, MAX(Reporting.PatchDeployment.DeployStartedOn) AS [Last Deployment Date]

                FROM            Reporting.Agent RIGHT OUTER JOIN

                                         Reporting.AssessedMachineState INNER JOIN

                                         Reporting.PatchDeployment INNER JOIN

                                         Reporting.DetectedPatchState ON Reporting.PatchDeployment.DetectedPatchStateId = Reporting.DetectedPatchState.Id ON

                                         Reporting.AssessedMachineState.Id = Reporting.DetectedPatchState.AssessedMachineStateId RIGHT OUTER JOIN

                                         Reporting.Machine INNER JOIN

                                         Reporting.PatchCountsByScanMachine ON Reporting.PatchCountsByScanMachine.MachineId = Reporting.Machine.LastAssessedMachineStateId ON

                                         Reporting.AssessedMachineState.MachineId = Reporting.Machine.Id ON Reporting.Agent.MachineId = Reporting.Machine.Id

                GROUP BY Reporting.Machine.DnsName, Reporting.Machine.LastKnownIP, Reporting.Machine.LastPatchMachineGroupName, Reporting.Agent.AssignedPolicyName,

                                         Reporting.PatchCountsByScanMachine.PatchFoundOrInstalled, Reporting.PatchCountsByScanMachine.PatchMissing, Reporting.PatchCountsByScanMachine.ServicePackMissing,

                                         Reporting.Machine.Name

                ORDER BY ScanDate, [Last Deployment Date]