3 Replies Latest reply on Nov 2, 2016 9:48 AM by rmoorenv

    Table Report Displaying The Number Of Patches From a specfic Group of Devices

    Rookie

      HI Guys, I was wondering if anybody could assist me in generating a report with a table view of the following security and patch information:


      • Device NameScan and Repair Settings NameLogin NameLast Check-in/Login DateDetected Number of Critical  Patches MissingDetected Number of Important/High Patches MissingDetected Number of Moderate Patches MissingTotal  Number Of Detected Critical/Important/High/Moderate Missing

       

       

      Ideally I'm only looking for the table to be populated with information for devices with a particular Scan and Repair Settings Name 'Offshore Patching Agent Scan and Repair'

       

       

      As for populating information on detected patches, all ideally tallied for each device.

       

       

      Any help would be much appreciated.

       

      Kind Regards,

       

      Salam

        • 1. Re: Table Report Displaying The Number Of Patches From a specfic Group of Devices
          rmoorenv Rookie

          There maybe an easier way for create this query. You should get Microsoft SQL server Manangement Studio, so you can test your queries.

           

          To filter for the specific Distribution Setting name edit "WHERE (AgentBehavior LIKE 'Work%')" (Line 6)

          You can use AgentBehavior = 'EXACT NAME'.

           

          As for the reports, there are some good guide on this community about creation and using SQL queries in your reports.

           

          Select Computer.DeviceName AS 'Device Name', BehaviorInfo.AgentBehavior AS 'Scan Setting Name', Computer.PrimaryOwner AS 'Primary Owner', Computer.SWLastScanDate AS 'Last Software Scan', COUNT(VulSeverity.DisplayName) AS 'Detected Total', COUNT(case when VulSeverity.DisplayName = 'Critical' then 1 end) AS 'Critical', COUNT(case when VulSeverity.DisplayName = 'High' then 1 end) AS 'High', COUNT(case when VulSeverity.DisplayName = 'Medium' then 1 end) AS 'Moderate' FROM Computer INNER JOIN
            BehaviorInfo ON BehaviorInfo.Computer_Idn = Computer.Computer_Idn LEFT JOIN
            CVDetected ON Computer.Computer_Idn = CVDetected.Computer_Idn LEFT JOIN
              Vulnerability ON CVDetected.Vulnerability_Idn = Vulnerability.Vulnerability_Idn LEFT JOIN
            VulSeverity ON VulSeverity.Severity_ID = Vulnerability.Severity
          WHERE (AgentBehavior LIKE 'Work%')
          Group by Computer.DeviceName, BehaviorInfo.AgentBehavior, Computer.PrimaryOwner, Computer.SWLastScanDate
          Order by Computer.DeviceName DESC;
          
          • 2. Re: Table Report Displaying The Number Of Patches From a specfic Group of Devices
            phoffmann SupportEmployee

            Edited the above post purely to format in SQL syntax highlighting - makes it easier to read.

             

            Awesome stuff! .

            • 3. Re: Table Report Displaying The Number Of Patches From a specfic Group of Devices
              rmoorenv Rookie

              Also, Mac agent information is stored in a different table from windows agent information.

              You may need to create two different reports if you are in a mix environment.