I'd like to create a lable that will tell me which pc's are missing critical patches. I have created a smart label for all critical patches for Windows XP, now I'd like to run that against my inventory to see what still needs critical patches. Is this possible with the K1000 appliance? Obviously I'm new to this(had the box 1 week), so please assume I know nothing about this when you reply. Thanks in advance.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Jmcelvoy,

You can add this as a new SQL report to get a list of all machines missing critical patches.
This will look great if you set your brake on column to machine.
SELECT M.NAME AS MACHINE,
VENDOR,
PP.IDENTIFIER,
PP.TITLE
FROM MACHINE M,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP
WHERE KO.KUID = M.KUID
AND KS.KUID = M.KUID
AND PMS.MACHINE_ID = M.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL'
ORDER BY NAME,
VENDOR,
IDENTIFIER,
TITLE


To turn this into a label, add a new smart label with any criteria.
After the label is created goto home -> label -> smart label -> your new label.
Replace that SQL you just created with this
SELECT *,
Unix_timestamp(NOW()) - Unix_timestamp(LAST_SYNC) AS LAST_SYNC_TIME,
Unix_timestamp(MACHINE.LAST_SYNC) AS LAST_SYNC_SECONDS
FROM MACHINE
WHERE MACHINE.ID IN (SELECT M.ID
FROM MACHINE M,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP
WHERE KO.KUID = M.KUID
AND KS.KUID = M.KUID
AND PMS.MACHINE_ID = M.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL')

Hope this helps.
Answered 11/29/2010 by: dchristian
Red Belt

Please log in to comment
0
For performance reasons avoid subqueries in cases like these. This query is equivalent for the filter part of the question


SELECT MACHINE.*,
Unix_timestamp(NOW()) - Unix_timestamp(LAST_SYNC) AS LAST_SYNC_TIME,
Unix_timestamp(MACHINE.LAST_SYNC) AS LAST_SYNC_SECONDS
FROM MACHINE ,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP
WHERE KO.KUID = MACHINE.KUID
AND KS.KUID = MACHINE.KUID
AND PMS.MACHINE_ID = MACHINE.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL'
Answered 11/30/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Gerald,

Good tip!!

Will the labels be ok as long as i return everything from machine and the last sync times?
Answered 11/30/2010 by: dchristian
Red Belt

Please log in to comment
0
yes, should be fine. Except I just realize my shorthand will cause a problem. minor edit in my post now

Another tip if you have multiple ways to write a query : http://dev.mysql.com/doc/refman/5.0/en/explain.html
Answered 11/30/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Just tried creating the SQL report and when I preview it, I get "Caught Invalid XML String" as the result. I created a new test SQL report, pasted the below into the SQL Select Statement field, set Machine as my Break on Columns, and left Auto-generate Layout checked. Am I doing something wrong?

SELECT M.NAME AS MACHINE,
VENDOR,
PP.IDENTIFIER,
PP.TITLE
FROM MACHINE M,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP
WHERE KO.KUID = M.KUID
AND KS.KUID = M.KUID
AND PMS.MACHINE_ID = M.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL'
ORDER BY NAME,
VENDOR,
IDENTIFIER,
TITLE
Answered 12/08/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
Just noticed that even when going into a working report and trying to preview it, I get that error message. So I just saved it and it's working. Now if I want to a column to see labels associated with these assets, how would I do that?
Answered 12/08/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
Try this (not tested)
SELECT M.NAME AS MACHINE,
VENDOR,
PP.IDENTIFIER,
PP.TITLE ,
GROUP_CONCAT(L.NAME ORDER BY 1 SEPARATOR ',')
FROM ( MACHINE M,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP )
JOIN
MACHINE_LABEL_JT ML ON ML.MACHINE_ID=M.ID

JOIN LABEL L ON ML.LABEL_ID=L.ID
WHERE KO.KUID = M.KUID
AND KS.KUID = M.KUID
AND PMS.MACHINE_ID = M.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL'
GROUP BY M.NAME
ORDER BY M.NAME,
VENDOR,
IDENTIFIER,
TITLE LIMIT 0


edit: fixed the syntax
Answered 12/08/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I received this error:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON ML.MACHINE_ID=M.ID,
LABEL L ON ML.LABEL_ID=L.ID
WHERE KO.KUID = M.KUID
' at line 11] in EXECUTE(
"SELECT M.NAME AS MACHINE,
VENDOR,
PP.IDENTIFIER,
PP.TITLE ,
GROUP_CONCAT(L.NAME ORDER BY 1 SEPARATOR ',')
FROM MACHINE M,
KBSYS.KUID_ORGANIZATION KO,
KBSYS.SMMP_CONNECTION KS,
PATCHLINK_MACHINE_STATUS PMS,
KBSYS.PATCHLINK_PATCH PP ,
MACHINE_LABEL_JT ML ON ML.MACHINE_ID=M.ID,
LABEL L ON ML.LABEL_ID=L.ID
WHERE KO.KUID = M.KUID
AND KS.KUID = M.KUID
AND PMS.MACHINE_ID = M.ID
AND PMS.PATCHUID = PP.UID
AND PMS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = 'CRITICAL'
GROUP BY M.NAME
ORDER BY NAME,
VENDOR,
IDENTIFIER,
TITLE LIMIT 0")
Answered 12/09/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
Thanks for the help GillySpy. You posted a reply in another of my post that got me the answer I was looking for.
Answered 12/09/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity