Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Report to show software install date.

Created: 29 Nov 2012 • Updated: 30 Nov 2012 | 9 comments
This issue has been solved. See solution.

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.

Comments 9 CommentsJump to latest comment

Andrew Bosch's picture

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...

------------------------------------
Sr. Principal SQA Engineer
Symantec

network101's picture

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

Andrew Bosch's picture

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%'

------------------------------------
Sr. Principal SQA Engineer
Symantec

network101's picture

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%'

Andrew Bosch's picture

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%'

------------------------------------
Sr. Principal SQA Engineer
Symantec

network101's picture

i really appriciate your help with this.

I just got an error when i tried this one.

  This DataSource is not in a runnable state.
Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

network101's picture

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

network101's picture

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]

SOLUTION