1 of 1 people found this helpful
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-OBOSBuild:2600 Total devices: (13145)Vul_ID Unique File Safe Vulnerable Need Scan N/A S+V+N (%) S+V (%) Deviation NotesMS11-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 809Patches for: Security-2011-07-JulyOSBuild:2600 Total devices: (13145)Vul_ID Unique File Safe Vulnerable Need Scan N/A S+V+N (%) S+V (%) Deviation Notes890830_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.
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.
Its the weekend, so you might get more replies from the community next week.