ITNinja

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.

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'

Gerald,

Good tip!!

Will the labels be ok as long as i return everything from machine and the last sync times?

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

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

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?

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

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")

Thanks for the help GillySpy. You posted a reply in another of my post that got me the answer I was looking for.