Outlook Run Activity Report

Brandon · 04-13-2009, 06:35 PM

Can anyone assist me with this nested SQL scenario? I would like to check the table
Evt_AeX_Application_Start for the entries where internalname = 'Outlook' and productversion LIKE '8.%'

For each unique guid I would then like to check the first run and last run dates:

MIN([Start Date]) AS 'First Run'
,MAX([Start Date]) AS 'Last Run'

In the end I would like to get all machines that still have Outlook 98 installed and then determine the first and last time it was used.

I keep getting weird results where it is returning the first and last runs for all products associated with that guid, rather than just the outlook 8.x ones.

Thanks for any assistance.

Brandon · 04-13-2009, 07:01 PM

I came up with this, but I think the report is still off in the scenario where they have office 97 installed, then outlook alone was uprgaded to 2003.

Code:


SELECT sq.name
,'First Run'=(SELECTTOP 1 MIN(sq.[Start Date])FROM Evt_AeX_Application_Start t1 WHERE sq.[guid] = t1.[_resourceguid])
,'Last Run'=(SELECTTOP 1 MAX(sq.[Start Date])FROM Evt_AeX_Application_Start t1 WHERE sq.[guid] = t1.[_resourceguid])
FROM(
SELECT
t0.[name]
,t0.guid
,t2.[ProductName]
,t2.[ProductVersion]
,t2.[start date]
FROM Evt_AeX_Application_Start t2
JOIN vComputer t0
on t0.guid = t2.[_resourceguid]
WHERE t2.[internalname] ='Outlook'AND t2.[productversion] LIKE'8.%'
) sq
GROUPBY sq.[guid], sq.[name]

If anyone could clean this up I would be greatful.