SQL script to read application useage from Edgesight Database

I had a requirement to extract some data from Edgesight. The standard reports in Edgesight could not provide the data i wanted so a colleague wrote me this SQL script that extracts the info straight out the database.

My requirement was to see all launches of a particular EXE , the user and their workstation ID

eg. (winword.exe , User56, Workstation1001)


This SQL script did the trick for winword instances run in the last 2 days.

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()) < 2
and filename like '%winword%'
group by i.filename, convert(char(10),t1.dtperiod,120), c.client_name, p.account_name