Thanks, this is what I wound up using for my report, nothing fancy but maybe
someone else will find it useful:
SELECT DISTINCT dbo.v_R_System.Name0, dbo.v_R_System.Resource_Domain_OR_Workgr0,
(SELECT MAX(LastUsedTime0) AS Expr1
FROM dbo.v_GS_CCM_RECENTLY_USED_APPS
WHERE (dbo.v_R_System.ResourceID =
ResourceID) AND (ExplorerFileName0 = 'winword.exe')) AS [Word Last Used time],
(SELECT MAX(LastUsedTime0) AS Expr1
FROM dbo.v_GS_CCM_RECENTLY_USED_APPS
AS v_GS_CCM_RECENTLY_USED_APPS_4
WHERE (dbo.v_R_System.ResourceID =
ResourceID) AND (ExplorerFileName0 = 'excel.exe')) AS [Excel Last Used time],
(SELECT MAX(LastUsedTime0) AS Expr1
FROM dbo.v_GS_CCM_RECENTLY_USED_APPS
AS v_GS_CCM_RECENTLY_USED_APPS_3
WHERE (dbo.v_R_System.ResourceID =
ResourceID) AND (ExplorerFileName0 = 'powerpoint.exe')) AS [Powerpoint Last
Used time],
(SELECT MAX(LastUsedTime0) AS Expr1
FROM dbo.v_GS_CCM_RECENTLY_USED_APPS
AS v_GS_CCM_RECENTLY_USED_APPS_2
WHERE (dbo.v_R_System.ResourceID =
ResourceID) AND (ExplorerFileName0 = 'msaccess.exe')) AS [Access Last Used time]
FROM dbo.v_GS_CCM_RECENTLY_USED_APPS AS
v_GS_CCM_RECENTLY_USED_APPS_1 LEFT OUTER JOIN
dbo.v_R_System ON
v_GS_CCM_RECENTLY_USED_APPS_1.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER
JOIN
dbo.v_FullCollectionMembership AS fcm ON
fcm.ResourceID = dbo.v_R_System.ResourceID
WHERE (fcm.CollectionID = @COLLID)
From: [email protected] [mailto:[email protected]] On
Behalf Of Daniel Ratliff
Sent: Tuesday, June 02, 2015 10:29 AM
To: [email protected]
Subject: RE: [mssms] Software metering report
You just need AI turned on. Its enabled by default.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt T
Sent: Tuesday, June 2, 2015 1:22 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software metering report
Interesting, I’ve never looked at that table. Seems like most of what I would
want from Software Metering. Do you know how it would get that data if the
metering rules are not enabled?
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Tuesday, June 02, 2015 8:45 AM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software metering report
If all you need is last used date, stick with the AI data in HINV.
V_gs_ccm_recently_used_apps I think is the SQL view. Much easier to build than
actual SWM reports.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt T
Sent: Monday, June 01, 2015 1:54 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software metering report
The existing reports don’t appear to be able to be filtered by collection. I’ve
also tried adding a collection parameter to the existing report but haven’t had
any luck. I think the formatting I’m shooting for with the custom report would
be better as well.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Juelich, Adam
Sent: Monday, June 01, 2015 10:17 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: [mssms] Software metering report
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]<mailto:[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.
________________________________
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.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
________________________________
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.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
________________________________
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.
