Hogville Blogville

If you use Lumension’s PatchLink Update Server, here’s a very handy SQL script that will output the names of your servers or workstations (devices) and count the number of patches they are behind. There isn’t a a way to do this in the app and it saves you from having to buy ERS.

USE PLUS
select b.agentname, count(c.impact) as xct, b.contactAddress, b.ContactDNS
from dbo.rpt_VulnerabilityResults a,
dbo.UP_Agents b,
dbo.rpt_Vulnerabilities c
--where a.agentid = '09B4BD74-3479-4727-AAFD-4C679B175D6C'
--and
where a.agentid = b.agentid
and a.vulnerabilityid = c.vulnerabilityid
and c.impact in ('Critical - 05', 'Critical - 01', 'Critical', 'Recommended')
and result = 'Not Patched'
group by b.ContactAddress, b.ContactDns, b.agentname
order by xct desc

Output below; It doesn’t spit out header info, I’ve added that manually below.

Device | Patches Behind | IP Address | FQDN

Now to apply some patches… 😉