I've created a smart label with only my servers listed. I now need to run a report off that smart label of which servers are missing which critical patches. I've created a query that runs against my entire inventory, but I need to break this down to just display the missing patches for my servers now. 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,

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...
Answered 12/07/2010 by: chrisgrim
Senior Purple Belt

Please log in to comment
0
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.
Answered 12/08/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
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
Answered 12/08/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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?
Answered 12/08/2010 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
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?
Answered 12/08/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Got this all working now. Thanks.
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