Hello,

 

I am a Junior Help Desk Admin, tasked with creating reports for a weekly update for my director. I will admit that I know almost nothing of SQL, so I have been trying to create a report through the wizard that can pull down extensive, in-depth parsed information. I know it is possible, but I would like to ask for some guidance from someone a little more experienced with KACE. I have looked at the KBOXBYKACE and other videos on the support site for using the reporting feature, however they are only looking at the something a little more basic than I want. I have also tried using duplicates of the embedded "tutorial" scripts with tweaks, and it either prints a report that is blank, or a report that contains 0's in the patch name or some other undesirable effect. I will include a list of what I'm trying to do with the database below, but the gist is, for a group of machines defined by a label, I want a report of which machines are not compliant with all active patches with type Security, Impact Critical, and a detect status of NOTPATCHED. If you have a library or other resource that can satisfy my needs and you could point me in the right direction, I would greatly appreciate it. I am also open to WEBEX sessions, etc.

REPORT- Sorted by Device Name, filtered by Label (plan on running seperate reports for each group, all sorted by label)

Detect Status- NOTPATCHED

Patch

Type=Security

Impact=Critical

Status=Active

 

 

P.S.

I have also tried sorting through the patch catalog with a Custom View delineating the filters above. I was very successful in narrowing down the list, however when I went to Create Report with the information gathered it would print everything. My initial guess is that I didn't define the filters in the report this way, but I am not sure.

Now I would like to offer a few different trailheads to start from. First, is MySQL database powerful enough to do what I'm asking it to? (through the wizard, I know that commandline would be much more powerful but I am working with what I have here, that includes a very limited KB of MySQL) Would it be easier (although more hands on involved) to print multiple reports and filter them into Excel or another program instead of one heavily modified/filtered report? Also are there any super handy URL's out there for MySQL scripting, assisting with scripting or containing basic template scripts I can modify for KACE, etc?

And as always, thank you in advance for any advice and comments. :)


V/r,


Jeff B.

1 Comment   [ + ] Show Comment

Comments

  • I'm not exactly sure what you want the report to contain. Do you want a list of the patches that haven't been installed for each computer? For instance:
    Machine1 Patch1, Patch2, Patch3
    Machine2 Patch2, Patch3, Patch4
    Machine3 Patch1, Patch5
    etc?
    • That is correct chucksteel. I am mainly interested in pulling a report of "For each computer, what patch is not installed."
      I then want to filter by Label (for location), Type of patch (Security), Impact (Critical), and Status=NOTPATCHED. However, when I try and filter it down to that, I either get a blank report or Zeroes in one of the fields with the others being blank. If I choose less filters, I usually end up with about 800-1000 entries of data.

      The goal is to reduce that data to a manageable size so that I can address manually if needed.
Please log in to comment

Answers

0
Here's a SQL query that should generate what you're looking to report:

SELECT M.NAME, COUNT(P.ID), GROUP_CONCAT(P.TITLE) as "Not Patched"
FROM ORG1.PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = PMS.PATCHUID 
JOIN MACHINE M on M.ID = PMS.MACHINE_ID
JOIN ORG1.PATCHLINK_PATCH_STATUS PPS on PPS.PATCHUID = PMS.PATCHUID
WHERE PMS.STATUS = "NOTPATCHED"
and P.IS_SECURITY = 1
and P.IMPACTID = "Critical"
and PPS.STATUS = 0
GROUP BY M.NAME

If you are using multiple organizations then you'll need to change the ORG1 tables to match your organization ID. The patch status can vary by organization so that is crucial.
Answered 09/26/2014 by: chucksteel
Red Belt

  • Thank you chucksteel. I will let you know if this works!
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share