Audit End Point Device names SQL Script

This is a SQL script that i use to query the Edgesight database for client devices names that have been running particular EXE's over a certain time period.

Eg. The example below shows the name of all endpoints that ran Project and Powerpoint in the last 30 days.

This is useful for controlling Device Based Licenced applications where the End point needs to be known to be licenced.

SQL Script
select i.filename, convert(char(10),t1.dtperiod,120), c.client_name, p.account_name, count(*)
from usage t1
join ctrx_session c on (c.sessid = t1.sessid)
join image i on (i.imid = t1.imid)
join principal p on (p.prid = t1.prid)
where DATEDIFF(day, t1.dtperiod, GETDATE()) < 30
and (filename like '%powerpnt%' or filename like '%winproj%')
group by i.filename, convert(char(10),t1.dtperiod,120), c.client_name, p.account_name;