Edgesight SQL to Retrieve Application useage by workstation

The Microsoft office licence agreement is based on devices with the ability to run office products. This can make licencing in a large corporation quite hard work (or expensive)

A typical power user who has a Desktop PC, Laptop, and Home PC with logon to the office, would under the rules require 3 office licences.

Gains are made where 1 PC is shared by mutiple shift workers in our call centres.

To gain the figures to ensure licence compliance in our environment I used a SQL query on the edgesight server. The following command gathers the unique workstation names of devices that launched winword.exe within our citrix environment. The code can easily be changed for each of the office products. The date range can also be changed. I ran mine over a 5 week period.

SQL Command
select tab1.Filename, tab1.client_name from

( select distinct I1.Filename, s1.client_name
from dbo.vw_image_perf V1,
dbo.image I1,
dbo.ctrx_session S1
where V1.Imid = I1.Imid
and V1.Session_id = S1.Sessid
and lower(I1.Filename) like 'winword.exe%'
and convert(nvarchar(10),V1.dtperiod,120) between '2009-10-05' and '2009-11-12' ) as Tab1

-- group by tab1.Filename
order by 1,2