[go: up one dir, main page]
Scribd
Upload
44 views3 pages

Query Software Versions Ashish

The document contains several SQL queries to retrieve information from database tables. The queries find MS Office products, non-Microsoft products, processor count, processor history, specific software versions, software installed on specific systems, standard software on all systems, and standard software on systems matching a name pattern.

Uploaded by

Pravin Tandel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
44 views3 pages

Query Software Versions Ashish

The document contains several SQL queries to retrieve information from database tables. The queries find MS Office products, non-Microsoft products, processor count, processor history, specific software versions, software installed on specific systems, standard software on all systems, and standard software on systems matching a name pattern.

Uploaded by

Pravin Tandel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

To Find MS Office Products

SELECT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,


v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Version0 ,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Security%'AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Update%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Update%'
AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Hotfix%'

To Fine Non Microsoft Products

SELECT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,


v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Version0 ,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) Not LIKE '%Microsoft%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Security%'AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Update%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Hotfix%'

To find Processor Count

SELECT DISTINCT SYS.Netbios_Name0, Processor.Name0,


Processor.MaxClockSpeed0,Comp.Model0,
count(*) as "Number of Processors"
FROM v_R_System SYS
JOIN v_GS_PROCESSOR Processor on SYS.ResourceID = Processor.ResourceID
JOIN v_GS_COMPUTER_SYSTEM Comp on Comp.ResourceID = SYS.ResourceID
GROUP By SYS.Netbios_Name0 , Processor.Name0, Processor.MaxClockSpeed0, Comp.Model0

ORDER BY SYS.Netbios_Name0

To Find Processor History

SELECT DISTINCT cs.name0 'Computer Name', cs.username0 'User Name', cs.model0


'Model', pr.name0 'Description', pr.maxclockspeed0 'Processor',
x.TotalPageFileSpace0 'Page File', x.TotalPhysicalMemory0 'Physical
Memory',x.TotalVirtualMemory0 'Virtual Memory'
FROM v_gs_computer_system cs
left outer join v_GS_PROCESSOR as pr on cs.ResourceID = pr.ResourceID
left outer join v_GS_X86_PC_MEMORY as x on x.ResourceID = pr.ResourceID
left outer join v_FullCollectionMembership as fc on pr.ResourceID = fc.ResourceID
where fc.collectionID = 'YBL00067'
order by cs.name0
To Find specific Software all Versions

Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName,


SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SP.ProductName like '%Google Chrome%'
Order by SP.ProductName, SP.ProductVersion

To Find Specific Software installed in Systems

SELECT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,


v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Version0 ,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_SYSTEM.Name0) LIKE 'YBL%' AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0)
LIKE '%adobe%'

All Standard Software Query

SELECT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,


v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Version0 ,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_SYSTEM.Name0) NOT LIKE '%YBL%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0)

LIKE '%adobe%' OR (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) NOT LIKE '%Microsoft%' OR


(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%XP Service Pack 3%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%SOAP%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MySupport%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%JInitiator%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%bxAutoZip%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Symantec Endpoint Protection%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%7-Zip%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Adobe Reader%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Windows Search%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Jabber%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%ScanGear%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft Office Standard 2007%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Adobe Flash Player%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%OpenOffice%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MySupport%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Xobni%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Chrome%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Cashin convertor%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MSSoap%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Ehelpline%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Suvidhaa%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%PDF%Converter%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Logi%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Spark%'
All Standard Software Query 2

SELECT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,


v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Version0 ,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_SYSTEM.Name0) LIKE '%YBL%' AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0)

LIKE '%adobe%' AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft%' AND


(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%XP Service Pack 3%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%SOAP%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MySupport%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%JInitiator%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%bxAutoZip%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Symantec Endpoint Protection%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%7-Zip%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Adobe Reader%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Windows Search%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Jabber%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%ScanGear%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft Office Standard 2007%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Adobe Flash Player%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%OpenOffice%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MySupport%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Xobni%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Chrome%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Cashin convertor%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%MSSoap%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Ehelpline%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Suvidhaa%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%PDF%Converter%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Logi%' AND
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Spark%
'

You might also like