Why don't you just using Software Metering for those apps and use the existing built-in Reports?
*-----------------------------------------------* *Adam Juelich* Pulaski Community School District <http://www.pulaskischools.org> Client Management Specialist 920-822-6075 On Mon, Jun 1, 2015 at 11:02 AM, Atkinson, Matt T < [email protected]> wrote: > I’ve been asked to build a report to show how recently a collection of > systems have used some of the apps in the Office suite. I’ve been able to > get a basic version of this written, but I’d like to have it be a little > easier to read by using some sub-queries. > > > > Here is what I have so far: > > SELECT dbo.v_R_System.Name0, MAX(dbo.v_MonthlyUsageSummary. > LastUsage) AS Expr1, dbo.v_GS_SoftwareUsageData.FileName > > FROM dbo.v_MonthlyUsageSummary INNER JOIN > > dbo.v_R_System ON dbo.v_MonthlyUsageSummary. > ResourceID = dbo.v_R_System.ResourceID INNER JOIN > > dbo.v_GS_SoftwareUsageData ON dbo. > v_MonthlyUsageSummary.FileID = dbo.v_GS_SoftwareUsageData.FileID INNER > JOIN > > dbo.v_ClientCollectionMembers ON dbo.v_R_System. > ResourceID = dbo.v_ClientCollectionMembers.ResourceID > > WHERE (dbo.v_ClientCollectionMembers.CollectionID = '@CollID') AND > ((dbo.v_GS_SoftwareUsageData.FileName = 'winword.exe') OR (dbo. > v_GS_SoftwareUsageData.FileName = 'excel.exe') OR (dbo. > v_GS_SoftwareUsageData.FileName = 'outlook.exe')) > > GROUP BY dbo.v_GS_SoftwareUsageData.FileName, dbo.v_R_System.Name0 > > > > I get output like this: > > > > Name0 Expr1 > FileName > > Comp1 2014-12-18 22:16:31.000 EXCEL.EXE > > Comp1 2015-05-26 22:14:27.000 OUTLOOK.EXE > > Comp1 2015-05-28 23:24:44.000 WINWORD.EXE > > > > What I’d like is output like this: > > Name0 EXCEL.EXE > OUTLOOK.EXE WINWORD.EXE > > Comp1 2014-12-18 22:16:31.000 2015-05-26 > 22:14:27.000 2015-05-28 23:24:44.000 > > > > The various sub-queries that I’ve tried to build haven’t returned any > results, they never stop running. > > > > > > *Matt Atkinson* > > Client Systems Engineer > > > > > > ------------------------------ > > This message is intended for the sole use of the addressee, and may > contain information that is privileged, confidential and exempt from > disclosure under applicable law. If you are not the addressee you are > hereby notified that you may not use, copy, disclose, or distribute to > anyone the message or any information contained in the message. If you have > received this message in error, please immediately advise the sender by > reply email and delete this message. > >
