Kurt,

Here is a general AS audit.  It selects the newest report by MAC  
address, restricted by IP if you wish, then displays the AS product  
name, version, and def date for each report.

-- AS audit report
select a.dm_user as "User ID", a.dm_mac as "MAC Address",  
z.max_report_time as "Report Time", b.av_prod_name as "AS Product",  
b.av_prod_ver as "AS Version", b.av_def_date as "Spyware Definition  
Date"
from dm_report a, dm_report_av b,
-- select newest report by mac in subnet
(select a.dm_mac, max(a.report_time) as max_report_time
from dm_report a
-- where a.dm_ip like '192.168.59%'  --limit to an IP
-- or a.dm_ip like '192.168.60%'
group by a.dm_mac) z
where a.dm_mac = z.dm_mac
and a.report_time = z.max_report_time
and a.report_id = b.report_id
and av_type='AS' -- use AV if you want to see AntiVirus
--order by a.dm_user
order by to_date(b.av_def_date, 'MM/DD/YYYY') -- may not work on some  
strings
, a.dm_user

You could also add in a "where b.av_prod_name like '%Sweeper%' " to  
get only the SpySweeper entries.  Remember that users can have more  
than one AS or AV entry per report.


Here is another one.  It selects the newest report by MAC address,  
restricted by IP if you wish, for a given requirement.  You can find  
the requirement ids by doing 'select * from dm_soft_info'

-- Failed Requirements by User
select a.dm_user as "User ID", a.dm_mac as "MAC Address",  
z.max_report_time as "Report Time", c.soft_name as "Requirement"
from dm_report a, dm_report_soft b, dm_soft_info c,
-- select the newest report by mac in subnet
(select a.dm_mac, max(a.report_time) as max_report_time
from dm_report a
where a.dm_ip like '192.168.59%'  --limit to an IP
or a.dm_ip like '192.168.60%'
group by a.dm_mac) z
where a.dm_mac = z.dm_mac -- join by MAC address and report_time
and a.report_time = z.max_report_time
and a.report_id = b.report_id -- join with individual requirement info
and b.soft_id = c.soft_id -- get the requirement name
and b.soft_id = 29 -- show reports for a specific requirement
and b.soft_status = 0 -- only show failed requirements
order by z.max_report_time, a.dm_user, c.soft_name

Hope that helps,

Michael Grinnell
Network Security Administrator
The American University


On Apr 27, 2007, at 3:13 PM, Kurt Huenemann wrote:

> Good day all,
>
> I would like to determine how many of our NAC users have a  
> particular Anti-Spyware package installed.  We have an 'optional'  
> requirement checking if WebRoot SpySweeper is installed, so it gets  
> reported on the CAM "Reports" tab, and we offer it as a download if  
> the user does not have it.
>
> I'm ready to renew the license for SpySweeper, so I'm wondering who  
> really uses it.
>
> Something like this should work:
>
>  controlsmartdb=# select dm_user,dm_mac,dm_os,report_text
>  controlsmartdb-#   from dm_report
>  controlsmartdb-#  where report_text like '%Sweeper%';
>
> but of course report_text is a huge field that is difficult to work  
> with, and I'm not sure how to select if the test was successful or  
> not.
>
> Can anyone suggest a 'tidy' way to see what A-S package the user  
> actually has?
>
> Thanks in advance!
>
> Kurt
>
>
> -- 
> Kurt E. Huenemann '83
> Assoc. Vice President for Information Resources
> Asst. Professor of Computer Science
> Heidelberg College
> 310 East Market Street
> Tiffin, OH 44883
>
> Internet: [log in to unmask]
> Fax:      419-448-2176
> Voice:    419-448-2351
>
> Engaging Students.  Creating futures.