To Record all softwares machine wise

This query can be used to record all softwares machines against collection.

NOTE :- This is very expensive query on your database, please use carefully.

select

v_R_System.Netbios_Name0‘Computer Name’,

v_GS_OPERATING_SYSTEM.Caption0 ‘OS Details’,

v_R_System.User_Name0 ‘User name’,

v_R_System.User_Domain0‘User domain’,

v_RA_System_IPAddresses.IP_Addresses0‘IP Address’,

v_RA_System_IPSubnets.IP_Subnets0‘Subnet’,

MAC.MAC_Addresses0 ‘MAC ADDRESS’,

v_Add_Remove_Programs.DisplayName0‘Software Display Name’,

v_Add_Remove_Programs.Publisher0‘Publisher’,

v_Add_Remove_Programs.RevisionID‘Revision’,

v_Add_Remove_Programs.Version0‘Version’,

v_Add_Remove_Programs.InstallDate0‘Installed Date’,

ins.InstalledLocation00 ‘installed path’,

sta.LastHWScan ‘Last H/w Scan’

from v_Add_Remove_Programs

join CollectionMembers on CollectionMembers.MachineID = v_Add_Remove_Programs.ResourceID

join Collections on Collections.SiteID = CollectionMembers.SiteID

join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID

join v_GS_WORKSTATION_STATUS sta on sta.resourceid = v_R_System.ResourceID

join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID =v_R_System.ResourceID

join v_RA_System_IPAddresses on v_RA_System_IPAddresses.ResourceID =v_R_System.ResourceID

join INSTALLED_SOFTWARE_DATA ins on ins.MachineID = v_R_System.ResourceID

join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID =v_R_System.ResourceID

JOIN v_RA_System_MACAddresses MAC ON MAC.ResourceID = v_R_System.ResourceID

join v_GS_SOFTWARE_TAG tag on tag.ResourceID = v_R_System.ResourceID

and v_Add_Remove_Programs.DisplayName0 not like ‘Security Update%’

and v_Add_Remove_Programs.DisplayName0 not like ‘Hotfix for %’

and v_Add_Remove_Programs.DisplayName0 not like ‘Update for Windows %’

and v_Add_Remove_Programs.DisplayName0 not like ‘Configuration Manager Client’

and v_RA_System_IPAddresses.IP_Addresses0 not like ‘0.0.0.0’

and v_RA_System_IPAddresses.IP_Addresses0 not like ‘%::%’

and Collections.CollectionName in (‘All systems’)

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s