Client Management Suite

Hello.

Is it possible by SQL to show when software was installed? For instance if I wanted to see when ms visio was installed which would be the best way?

Thanks.

Here you go:

SELECT sci.Name, inst.InstallDate
FROM vRM_Software_Component_Item sci
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sci.Guid
WHERE LOWER(sci.Name) LIKE '%visio%'

Just replace "visio" with any software name to find out when it was installed...

Thanks that is great, please can you add computer name and username.

SELECT vc.Name, vc.[User], sci.Name, inst.InstallDate
FROM vRM_Software_Component_Item sci
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sci.Guid
JOIN vComputer vc
   ON vc.Guid = inst._ResourceGuid
WHERE LOWER(sci.Name) LIKE '%visio%'

This does work but brings back alot of information.

I have some code that I use to show which systems visio is installed on.

This brings back a short list which is great, could you add install date to it?

Select
  ID.Name as 'Computer Name',
  ID.[User],
  ARP.DisplayName as 'Application Name'
From
  Inv_AddRemoveProgram ARP Join
  vComputer ID On ARP._ResourceGuid = ID.Guid
Where
  (ARP.DisplayName) Like 'Microsoft Office%' And
  (ARP.Publisher) Like 'Microsoft%' And
  (ARP.DisplayName) Not Like '%Service%' And
  (ARP.DisplayName) Not Like '%Security%' And
  (ARP.DisplayName) Not Like '%Validation%' And
  (ARP.DisplayName) Not Like '%Component%' And
  (ARP.DisplayName) Not Like '%Lab%' And
  (ARP.DisplayName) Not Like '%Sound%' And
  (ARP.DisplayName) Not Like '%Add-in%' And
  (ARP.DisplayName) Not Like '%MUI%' And
  (ARP.DisplayName) Not Like '%Project%' And
  (ARP.DisplayName) Like '%Visio%' And
  (ARP.DisplayName) Not Like '%Disc 2%' And
  (ARP.DisplayName) Not Like '%Communicator%' And
  (ARP.DisplayName) Not Like '%Viewer%' And
  (ARP.DisplayName) Not Like '%Visual%' And
  (ARP.DisplayName) Not Like '%Resource%' And
  (ARP.DisplayName) Not Like '%Share%' And
  (ARP.DisplayName) Not Like '%Publisher%' And
  (ARP.DisplayName) Not Like '%Producer%' And
  (ARP.DisplayName) Not Like '%proof%' And
  (ARP.DisplayName) Not Like '%Powerpoint%' And
  (ARP.DisplayName) Not Like '%outlook%' And
  (ARP.DisplayName) Not Like '%onenote%' And
  (ARP.DisplayName) Not Like '%live%' And
  (ARP.DisplayName) Not Like '%infopath%' And
  (ARP.DisplayName) Not Like '%frontpage%' And
  (ARP.DisplayName) Not Like '%converter%' And
  (ARP.DisplayName) Not Like '%access%' And
  (ARP.DisplayName) Not Like '%web%' And
  (ARP.DisplayName) Not Like '%excel%' And
  (ARP.DisplayName) Not Like '%groove%' And
  (ARP.DisplayName) Not Like '%word%' And
  (ARP.DisplayName) Not Like '%interop%'

i really appriciate your help with this.

I just got an error when i tried this one.

Select
ID.Name as 'Computer Name',
ID.[User],
ARP.DisplayName as 'Application Name',
inst.InstallDate

From
Inv_AddRemoveProgram ARP Join
vComputer ID On ARP._ResourceGuid = ID.Guid
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = ARP._SoftwareComponentGuid

Where
(ARP.DisplayName) Like 'Microsoft Office%' And
(ARP.Publisher) Like 'Microsoft%' And
(ARP.DisplayName) Not Like '%Service%' And
(ARP.DisplayName) Not Like '%Security%' And
(ARP.DisplayName) Not Like '%Validation%' And
(ARP.DisplayName) Not Like '%Component%' And
(ARP.DisplayName) Not Like '%Lab%' And
(ARP.DisplayName) Not Like '%Sound%' And
(ARP.DisplayName) Not Like '%Add-in%' And
(ARP.DisplayName) Not Like '%MUI%' And
(ARP.DisplayName) Not Like '%Project%' And
(ARP.DisplayName) Like '%Visio%' And
(ARP.DisplayName) Not Like '%Disc 2%' And
(ARP.DisplayName) Not Like '%Communicator%' And
(ARP.DisplayName) Not Like '%Viewer%' And
(ARP.DisplayName) Not Like '%Visual%' And
(ARP.DisplayName) Not Like '%Resource%' And
(ARP.DisplayName) Not Like '%Share%' And
(ARP.DisplayName) Not Like '%Publisher%' And
(ARP.DisplayName) Not Like '%Producer%' And
(ARP.DisplayName) Not Like '%proof%' And
(ARP.DisplayName) Not Like '%Powerpoint%' And
(ARP.DisplayName) Not Like '%outlook%' And
(ARP.DisplayName) Not Like '%onenote%' And
(ARP.DisplayName) Not Like '%live%' And
(ARP.DisplayName) Not Like '%infopath%' And
(ARP.DisplayName) Not Like '%frontpage%' And
(ARP.DisplayName) Not Like '%converter%' And
(ARP.DisplayName) Not Like '%access%' And
(ARP.DisplayName) Not Like '%web%' And
(ARP.DisplayName) Not Like '%excel%' And
(ARP.DisplayName) Not Like '%groove%' And
(ARP.DisplayName) Not Like '%word%' And
(ARP.DisplayName) Not Like '%interop%'

Oops, my bad.  I've fixed the query above.  It was missing an 'ON' in after the JOIN Inv_InstalledSoftware line...

Thanks, this does work but brings back multiple install date times for the same person. I believe we are getting closer tho :)

SELECT name as 'Computer Name',

[user],

DisplayName as 'Application Name',

InstallDate

FROM vComputer ID

INNER JOIN Inv_AddRemoveProgram ARP

On ARP._ResourceGuid = ID.Guid

Where (ARP.DisplayName) Like 'Microsoft Office%' And

(ARP.Publisher) Like 'Microsoft%' And

(ARP.DisplayName) Not Like '%Service%' And

(ARP.DisplayName) Not Like '%Security%' And

(ARP.DisplayName) Not Like '%Validation%' And

(ARP.DisplayName) Not Like '%Component%' And

(ARP.DisplayName) Not Like '%Lab%' And

(ARP.DisplayName) Not Like '%Sound%' And

(ARP.DisplayName) Not Like '%Add-in%' And

(ARP.DisplayName) Not Like '%MUI%' And

(ARP.DisplayName) Not Like '%Project%' And

(ARP.DisplayName) Like '%Visio%' And

(ARP.DisplayName) Not Like '%Disc 2%' And

(ARP.DisplayName) Not Like '%Communicator%' And

(ARP.DisplayName) Not Like '%Viewer%' And

(ARP.DisplayName) Not Like '%Visual%' And

(ARP.DisplayName) Not Like '%Resource%' And

(ARP.DisplayName) Not Like '%Share%' And

(ARP.DisplayName) Not Like '%Publisher%' And

(ARP.DisplayName) Not Like '%Producer%' And

(ARP.DisplayName) Not Like '%proof%' And

(ARP.DisplayName) Not Like '%Powerpoint%' And

(ARP.DisplayName) Not Like '%outlook%' And

(ARP.DisplayName) Not Like '%onenote%' And

(ARP.DisplayName) Not Like '%live%' And

(ARP.DisplayName) Not Like '%infopath%' And

(ARP.DisplayName) Not Like '%frontpage%' And

(ARP.DisplayName) Not Like '%converter%' And

(ARP.DisplayName) Not Like '%access%' And

(ARP.DisplayName) Not Like '%web%' And

(ARP.DisplayName) Not Like '%excel%' And

(ARP.DisplayName) Not Like '%groove%' And

(ARP.DisplayName) Not Like '%word%' And

(ARP.DisplayName) Not Like '%interop%'

order by [user]