Sccm report for showing when software was last used

Question

I am creating a report to find computers on our network that have certain software on it.

The report was working fine until i wanted to find out when the program was last used. When i added "LastUsedTime0" the result have increased.

I am not a coder and my scripts a trial and error of many scripts but it looks like the linking of the database is not correct (from my crystal reports experience).

Here is the script

=============================

SELECT s.Name0, DisplayName0, Publisher0, s.AD_Site_Name0, TopConsoleUser0, LastUsedTime0

FROM v_Add_Remove_Programs arp,V_R_system s, 
v_FullCollectionMembership fcm, 
v_GS_CCM_RECENTLY_USED_APPS rua,
v_GS_SYSTEM_CONSOLE_USAGE scu

WHERE fcm.CollectionID = 'SMS00001' and 
arp.ResourceID=fcm.ResourceID and 
arp.ResourceID=s.ResourceID and 
arp.ResourceID=rua.ResourceID and
arp.ResourceID=scu.ResourceID and


(
DisplayName0 like 'Microsoft Office%' and
DisplayName0 not like '%Excel%' and
DisplayName0 not like '%database engine%' and
DisplayName0 not like '%slide%' and
DisplayName0 not like '%frontpage%' and
DisplayName0 not like '%SharePoint%' and
DisplayName0 not like '%Live%' and
DisplayName0 not like '%PowerPoint%' and
DisplayName0 not like '%Word%' and
DisplayName0 not like '%XML%' and
DisplayName0 not like '%Communicator%' and
DisplayName0 not like '%SR-1%' and
DisplayName0 not like '%Resource%' and
DisplayName0 not like '%Visio%' and
DisplayName0 not like '%Web%' and
DisplayName0 not like '%XP%' and
DisplayName0 not like '%Sounds%' and
DisplayName0 not like '%OneNote%' and
DisplayName0 not like '%Click%' and
DisplayName0 not like '%Primary%' and
DisplayName0 not like '%Standard%' and
DisplayName0 not like '%Connector%' and
DisplayName0 not like '%Pack%' and
DisplayName0 not like '%Add-in%' and
DisplayName0 not like '%Validation%' and
DisplayName0 not like '%Components%' and
DisplayName0 not like '%Proof%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Service%' or
DisplayName0 like 'Microsoft Project%' and
DisplayName0 not like '%Pack%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Service%' or
DisplayName0 like 'Microsoft Access%' and
DisplayName0 not like '%engine%' and
DisplayName0 not like '%Runtime%' and
DisplayName0 not like '%Standard%' and
DisplayName0 not like '%Connector%' and
DisplayName0 not like '%Pack%' and
DisplayName0 not like '%Add-in%' and
DisplayName0 not like '%Validation%' and
DisplayName0 not like '%Components%' and
DisplayName0 not like '%Proof%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Service%' or
DisplayName0 like 'Microsoft Project%' and
DisplayName0 not like '%Pack%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Trial%' and
DisplayName0 not like '%Service%' or
DisplayName0 like 'Microsoft Access%' and
DisplayName0 not like '%engine%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Service%' or
DisplayName0 like 'Microsoft Visio%' and
DisplayName0 not like '%Pack%' and
DisplayName0 not like '%MUI%' and
DisplayName0 not like '%Viewer%' and
DisplayName0 not like '%Service%' 
)

GROUP BY DisplayName0, Publisher0, s.AD_Site_Name0, s.Name0, LastUsedTime0, TopConsoleUser0

ORDER BY Publisher0

===========================

This script is probably not even the best way to do what i want to do so any suggestions would be great.

thanks heaps for your time.

Answer 1

The joins you are doing technically work, but will give you incorrect results. It is a one-to-many relationship, which is why you are seeing "duplicates."

Add/remove programs data is not related to execution data. It is related to the machine, which is, in turn, related to the execution data. But there is no direct relationship.

Add/remove programs data is data that is (usually) written to the registry in Windows when a program is installed. Execution data is collected by the software metering part of the SCCM client. It monitors the execution of .exe files. An .exe file is not necessarily a program in add/remove programs, and a program in add/remove programs may have multiple executables that pertain to it. This is why your query returns bad results.

You may be better off getting all of your data from v_GS_CCM_RECENTLY_USED_APPS, and dropping add/remove programs from your query. v_GS_CCM_RECENTLY_USED_APPS has a product name column in it, and it has execution data.