We do something similar, we just exclude the ARP data. Just use the usage data and key off Product Name. They may have to make an educated guess for some software to determine what its listed as in ARP, but it is usually pretty close.
Daniel Ratliff From: [email protected] [mailto:[email protected]] On Behalf Of Beardsley, James Sent: Friday, June 16, 2017 4:29 PM To: [email protected] Subject: [mssms] RE: All Apps Installed + Last Used Date That's a good point that the usage data is based off exe. I didn't think about that. This is the closest I could get which shows me the apps on the system but because it's the CCM_RECENTLY_USED_APPS view, the ProductName is not always the same as what shows up in ARP so it would be confusing for our service desk who will ultimately be using this report. It would be nice if the results were more like what you would see in ARP. If anyone has a better solution, I'm all ears. SELECT DISTINCT v_GS_CCM_RECENTLY_USED_APPS.ProductName0 AS ProductName, v_GS_CCM_RECENTLY_USED_APPS.ProductVersion0 as Version, MAX (v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0) AS [Last Usage] FROM v_R_System INNER JOIN v_GS_CCM_RECENTLY_USED_APPS ON v_R_System.ResourceID = v_GS_CCM_RECENTLY_USED_APPS.ResourceID WHERE v_R_System.Name0 = 'computername' AND ProductName0 <> '' GROUP BY ProductName0,ProductVersion0 ORDER BY ProductName0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Friday, June 16, 2017 2:56 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: All Apps Installed + Last Used Date This will be a difficult report to pull, because the ARP data is just that, ARP data. The usage data is based off .exe name. Also, I wouldn't recommend the Software Metering views, use the default AI view, v_gs_ccm_recently_used_software. Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Beardsley, James Sent: Friday, June 16, 2017 1:51 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] All Apps Installed + Last Used Date Anyone have a good SQL query that I could turn into a report that allows a person to enter a computer name and the results would give them all applications installed on that computer plus the last used date of each app. Basically, the report would be identical to the "Software registered in Add Remove Programs on a specific computer" report but I'm thinking I would need to join with the v_MonthlyUsageSummary.LastUsage to include the last time the app was used. Unless that information would be better coming from another view/table? Thanks, James ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation. 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. ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation. 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.
