3 Replies Latest reply on Jul 31, 2011 6:47 AM by Catalysttgj

    Report which machines were patched from a custom group

    chyryh Apprentice

      Hi all

       

      In our Patch Manager environment, patches were deployed using custom groups, i.e. security team would approve the patches and add them to the custom group, then the desktop management team would have policy tasks repairing against the custom group.

       

      How do you then determine which patches have been deployed to all your machines? And which patches (in the group) has how many outstanding machines?

       

      Does anyone have a similar set up? Any insights into the reporting of such an environment?

       

      Thanks in advance for your help.

       

      Hong

        • 1. Re: Report which machines were patched from a custom group
          Catalysttgj Expert

          It will be interesting to see what sort of suggestions that the community comes up with.

           

          We have not been very satisfied with the reporting features of LANDesk from 8.7 through 9.02. Every time we've had a need to generate a report, we have found the built-in reports to be wanting. Additionally, building custom queries to create basic lists is both shoddy, and incomplete in thought. We definitely appreciate LANDesk's effort to try and make an easier to use query system compared to raw SQL, but unfortunately, the query logic falls pretty far from the level of capabilities that one can achieve with pure SQL. I wonder if LANDesk will ever get brave enough to open up the console to let custom SQL queries in.

           

          In our environment we developed custom SQL queries and now store procedures to produce the ideal reporting to get around LANDesk's weakness in this area, however we may be a special case. Others may find that the reporting in LANDesk is adequate, but being a stickler for detail, I've been unable to come up with an "in the box" solution that meets our reporting needs.

           

          Our first stab at a customize report was simply to produce a list of every patch "that we cared about" with a set of numbers in columns including: the patch binary, as well as a "Safe", "Vulnerable", "Need Scan", "Not applicable", and then two percentage values representing the level of success of the specific patch. One percentage includes only the "safe" and "vulnerable" counts, and the other percentage includes "needs scan" as well. "not applicable" counts are not counted in the percentage calculations, and total devices is adjusted to correct for this.

          With the two percentages it would provide us with a real world condition of risks. One representing the immediate (active computers), the other representing a future potential risk for devices that are less active. We have a lot of mobiles in our environment so this was a necessary delta to be able to interpret.

           

          Here's an example output of what this report looks like: (Keep in mind its intended to be viewed in a spreadsheet format)

           

           

          Patches for:     Security-2011-06-OB
          OSBuild:2600     Total devices: (13145)
          Vul_ID     Unique File     Safe     Vulnerable     Need Scan     N/A     S+V+N (%)     S+V (%)     Deviation     Notes
          MS11-025v2_INTL     vcredist_x86_2k5sp1.2.exe     11664     288     809     384     91.40%     97.59%     (V)|(p1)
          MS11-025v2_INTL     vcredist_x86_2k8sp1.2.exe     12085     135     809     116     92.75%     98.90%     (V)|(p1)
          MS11-025v2_INTL     vs90sp1-kb2538241-x86.2.exe     13     0     809     12323     1.58%     100.00%     (p1)
          MS11-025v2_INTL     vcredist_x86_2k10.exe     4     0     809     12332     0.49%     100.00%
          MS11-025v2_INTL     vs10-kb2542054-x86.2.exe     2     0     809     12334     0.25%     100.00%
          <Averages>--------------->     4753     84     809
          Patches for:     Security-2011-07-July
          OSBuild:2600     Total devices: (13145)
          Vul_ID     Unique File     Safe     Vulnerable     Need Scan     N/A     S+V+N (%)     S+V (%)     Deviation     Notes
          890830_INTLv5.8     windows-kb890830-v3.21.exe     11828     107     1138     72     90.48%     99.10%
          MS11-054     windowsxp-kb2555917-x86-enu.exe     11845     113     1138     49     90.45%     99.06%
          MS11-055_INTL     office2003-kb2493523-fullfile-glb.cab     5441     69     1138     6497     81.84%     98.75%     (S)|(V)
          MS11-056     windowsxp-kb2507938-x86-enu.exe     11789     169     1138     49     90.02%     98.59%     (V)
          <Averages>--------------->     10225     114     1138

           

           

           

          Our second stab at a custom report is a bit more ambititous, and a lot closer to what you are describing in your question. In this report we want to generate a list of every device in the DB that indicates that it has a patch "detected". The column set will include the "type" of device (mobile or Stationary), the "model", the "login name", the "primary user", the "number of devices associated to the login name" (in case a particular user is a technician, their name will show on many devices.), as well as the "last security scan date", and a custom definition 'found' which is the "Last Autofix date" (this indicates that a successful security scan with autofix actually took place on a device and was uploaded successfully to the core. These are the standard columns, and then to add to these columns for each patch that we want to be counted in the detection there will be an additional column that is named after this patch, and will contain either a NULL, a zero, or a one value. NULL of course means that the device has not scanned for this patch at all. Zero means its "not detected", or its patched, and of course one will mean its "detected" and needs to patch. Beyond this we may add a few more standard columns, but this is where the idea is so far. This is a very complicated report, but in theory LANDesk should be able to do something like this in the box. I have yet to see/find it. There may be bits and pieces of these features included in some of the built-in reports, but nothing that is altogether, and this robust.

           

          This is an example output, with names obsured for privacy, of what is descibed above: (Again, meant to be viewed in Excel - First line is the header - a little long.)

           

          Display Name Type Model Login Name Devices per Login Name Primary User Last Security Scan Date Last Autofix Date MS11-056 MS11-025v2_INTL MS11-038 MS11-044 MS11-045_INTL

           

          XXXX107406 Mobile Latitude D600 XXXXUSER 1 CN= XXXXUSER not scanned

          XXXX210969 Mobile Latitude E6410 BRXXXXKE 1 CN= BRXXXXKE Jun 16 2011  8:22AM not scanned 1 1 1

          XXXX211193 Mobile Latitude E6410 KIXXXXAL 1 CN= KIXXXXAL Jun 21 2011 10:18AM not scanned 0 0 0

          XXXX210217 Mobile Latitude E6410 AXXXXAL1 1 CN= AXXXXAL1 Jun 28 2011  2:01PM not scanned 1 0 0 0

          XXXX210007 Mobile Latitude E6410 PRXXXXKA 1 CN= PRXXXXKA Jun 30 2011  8:24AM not scanned 0 0 0 0

          XXXX211480 Mobile Latitude E6410 MXXXXEM 1 CN= MXXXXEM Jul  5 2011 12:51AM not scanned 0 0 0 0

          XXXX210712 Mobile Latitude E6410 MXXXXCL 1 CN= MXXXXCL Jul  5 2011  8:41PM not scanned 1 1 1 1

          XXXX210609 Mobile Latitude E6410 HAXXXXM 1 CN=HAXXXXM Jul  8 2011 12:33PM not scanned 0 0 0 0

          XXXX216000 Desktop OptiPlex 380 PXXXR 1 Built-in Administrator Jun 22 2011  3:06AM not scanned 0 0 0

          XXXX215810 Desktop OptiPlex 380 BXXXXJ 1 CN= BXXXXJ Jul  5 2011  2:57AM empty 1 0 0 0

          XXXX212751 Desktop OptiPlex 380 ELXXXXPA 1 CN= ELXXXXPA Jul 14 2011 12:36AM 2011:07:14:00:37:00 1 0 0 0 0

           

          Let's see what the rest of the community has to add.

           

          Take care.

          1 of 1 people found this helpful
          • 2. Re: Report which machines were patched from a custom group
            chyryh Apprentice

            I guess the short answer is there is no solution from the standard LANDesk report engine. We'll have to start digging into the database.

             

            Thanks for the suggestions.

             

            Hong

            • 3. Re: Report which machines were patched from a custom group
              Catalysttgj Expert

              Its the weekend, so you might get more replies from the community next week.

               

              Good luck!