ITNinja

jmcelvoy,

If you have a label created, you can use the reporting wizard to create the join you need by your label name. Create a new report, choose a computer object on the first screen, some computer columns on the second, and then create a rule that is Label Names contains your_server_label. Save the report and then go back into it and click the edit SQL button and you'll see the join you need to find only computers in your label.

Chris...

Ok, I created a report with Computer as the first option, then chose IP Address, Last Reboot, and OS Name for my options. Then I added a rule for OS Name contains Server. This found all my servers. I now need to get this report to display which critical patches the servers are missing.

chrisgrim, Thanks for getting back to me on this. One thing I'm not seeing is when I added the rule, I didn't see an option for Label Names. Did I need to click on something else? Also, I went into Edit SQL and I'm not sure what you mean by "you'll see the join you need to find only computers in your label." By adding the rule OS Name contains Server, I was able to only get my servers. Now I need to add a rule or entry(not sure which) that will display the missing patches for the servers in this report.

Continue with above if you want a wizard-editable report.

Otherwise, it seems you want a hybrid of http://itninja.com/question/should-these-system-dlls-be-installed-by-an-app.?28 and http://www.appdeploy.com/messageboards/tm.asp?m=67986

I didn't try it yet but mashing the two together I get:

SELECT MACHINE.NAME AS COMPUTER_NAME,
KBSYS.PATCHLINK_PATCH.TITLE AS PATCH_TITLE,
PATCHLINK_MACHINE_STATUS.STATUS AS PATCH_STATUS,
LABEL.NAME AS LABEL_NAME
FROM PATCHLINK_MACHINE_STATUS,
LABEL,
KBSYS.PATCHLINK_PATCH,
MACHINE,
MACHINE_LABEL_JT,
PATCHLINK_PATCH_STATUS
WHERE PATCHLINK_MACHINE_STATUS.PATCHUID=KBSYS.PATCHLINK_PATCH.UID
and MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
and LABEL.ID=MACHINE_LABEL_JT.LABEL_ID
and MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID
and PATCHLINK_PATCH_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
and KBSYS.PATCHLINK_PATCH.IS_APP =1 /* 0 for OS 1 for App */
and PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
AND KBSYS.PATCHLINK_PATCH.IMPACTID = 'CRITICAL'
and PATCHLINK_PATCH_STATUS.STATUS = 0
and LABEL.NAME='Servers' /*Label to look for*/
ORDER by KBSYS.PATCHLINK_PATCH.TITLE, MACHINE.NAME


I tried creating a new SQL Report using this text and received an XML error message when trying to preview. There is a report called Machines not compliant by patch...is there a way to modify that report and put an "OS Name Contains=Server" in it?

Make sure you check the autogenerate layout box. You can modify the SQL. Are you trying to display an extra column or only include servers?

Got this all working now. Thanks.