title: Sccm report for showing when software was last used
body: <p>I am creating a report to find computers on our network that have certain software on it.</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>Here is the script</p>
  
  <p>=============================</p>
  
  <pre><code>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
  </code></pre>
  
  <p>===========================</p>
  
  <p>This script is probably not even the best way to do what i want to do so any suggestions would be great.</p>
  
  <p>thanks heaps for your time.</p>