Monday, July 31, 2017
Multiple SQL Queries and Queries Views for SCCM 2012
This summary is not available. Please
click here to view the post.
List of Packages in SCCM
List of Packages in SCCM
---------------------------------------------
SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID
---------------------------------------------
SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID
SCCM Client Status report.
SCCM Client Status report.
SELECT S.Name0 as 'Computer Name', S.User_Name0 as 'User Name',
case when S.Decommissioned0='0'
then 'Not Decommissioned' when S.Decommissioned0='1'
then 'Decommissioned' end as 'Decommissinoed Status',
S.Last_Logon_Timestamp0,
Case when CS.ClientActiveStatus='1'
then 'Active' When CS.ClientActiveStatus='0'
then 'Inactive' end as 'Client Active Status'
FROM v_R_System S inner Join v_CH_ClientSummary CS on S.ResourceId=CS.ResourceID
Detection rule for Outlook and Skype for SCCM 2012
Detection rule for Outlook and Skype SCCM 2012 :
%ProgramFiles%\Microsoft Office\Office16
lync.exe
%ProgramFiles%\Microsoft Office\Office16
OUTLOOK.EXE
WQL- List Of Machines With Workstation 10.0
WQL- List Of Machines With Workstation 10.0
select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from SMS_R_System
where OperatingSystemNameandVersion like '%Workstation 10.0%'
select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from SMS_R_System
where OperatingSystemNameandVersion like '%Workstation 10.0%'
Tuesday, July 25, 2017
Region wise available machines report
Region wise available machines report :-
select distinct
sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0,
Usr.Mail0,
sys.USer_domain0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0
where sys.resource_domain_or_workgr0 = 'AS'
select distinct
sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0,
Usr.Mail0,
sys.USer_domain0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0
where sys.resource_domain_or_workgr0 = 'AS'
Drivers (Disk) Space information for machines & Collection
Drivers (Disk ) Space information for machines :
Select v_R_System.name0,
v_R_System.Resource_Domain_OR_Workgr0 as Domain,
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.Caption0,
v_GS_LOGICAL_DISK.DriveType0,
v_R_System.Last_Logon_Timestamp0
from v_R_System
inner Join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID
where v_GS_LOGICAL_DISK.DriveType0 = '3'
order by v_R_System.name0
Select v_R_System.name0,
v_R_System.Resource_Domain_OR_Workgr0 as Domain,
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.Caption0,
v_GS_LOGICAL_DISK.DriveType0,
v_R_System.Last_Logon_Timestamp0
from v_R_System
inner Join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID
where v_GS_LOGICAL_DISK.DriveType0 = '3'
order by v_R_System.name0
For Collection :-
SELECT distinct
SYS.Name,
v_r_system.user_name0,
sys.SiteCode,
SYS.Domain,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
LDISK.Size0,
LDISK.FreeSpace0
FROM
v_FullCollectionMembership_Valid SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
join v_r_system on SYS.ResourceID= v_r_system.resourceID
Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE LDISK.DriveType0 = 3 and sys.CollectionID = 'SMS00001'
Particular ( Machine wise ) machine software installed report
Particular ( Machine wise ) machine software installed report :-
SELECT DISTINCT
v_R_System.Netbios_Name0 AS "Machine Name",
v_r_system.User_Name0,
ARP.DisplayName0 AS"Product Name",
ARP.Version0 AS"Version" ,
ARP.InstallDate0 AS"Installation Date",
ARP.ProdID0 AS"Product ID",
ARP.Publisher0 AS"Publisher",
v_r_system.Last_Logon_Timestamp0
FROM v_R_System
JOIN v_GS_ADD_REMOVE_PROGRAMS ARP on v_R_System.ResourceID = ARP.ResourceID
JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
WHERE
v_R_System.Netbios_Name0 IN ('USWSTLUSX37806')
AND ARP.DisplayName0 NOT LIKE 'Hotfix for %'
AND ARP.DisplayName0 NOT LIKE 'Security Update for %'
AND ARP.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND ARP.DisplayName0 NOT LIKE 'Update for Office %'
AND ARP.DisplayName0 NOT LIKE 'Update for Outlook %'
AND ARP.DisplayName0 NOT LIKE 'Update for Windows %'
AND ARP.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND ARP.DisplayName0 NOT LIKE 'Hotfix%'
ORDER BY v_R_System.Netbios_Name0
SELECT DISTINCT
v_R_System.Netbios_Name0 AS "Machine Name",
v_r_system.User_Name0,
ARP.DisplayName0 AS"Product Name",
ARP.Version0 AS"Version" ,
ARP.InstallDate0 AS"Installation Date",
ARP.ProdID0 AS"Product ID",
ARP.Publisher0 AS"Publisher",
v_r_system.Last_Logon_Timestamp0
FROM v_R_System
JOIN v_GS_ADD_REMOVE_PROGRAMS ARP on v_R_System.ResourceID = ARP.ResourceID
JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
WHERE
v_R_System.Netbios_Name0 IN ('USWSTLUSX37806')
AND ARP.DisplayName0 NOT LIKE 'Hotfix for %'
AND ARP.DisplayName0 NOT LIKE 'Security Update for %'
AND ARP.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND ARP.DisplayName0 NOT LIKE 'Update for Office %'
AND ARP.DisplayName0 NOT LIKE 'Update for Outlook %'
AND ARP.DisplayName0 NOT LIKE 'Update for Windows %'
AND ARP.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND ARP.DisplayName0 NOT LIKE 'Hotfix%'
ORDER BY v_R_System.Netbios_Name0
Outlook App enforcement report report
Outlook App enforcement report report :-
select distinct fn.machinename as 'ComputerName',vrs.user_name0 as 'UserID' ,fn.applicationname as 'DisplayName', fn.deploymenttypename as deploymenttypename,
errorcode,
case when fn.appstate='1000' then 'Success'
when fn.appstate='1001' then 'Already Compliant'
when fn.appstate='1002' then 'Simulate Success'
when fn.appstate='2000' then 'In progress'
when fn.appstate='2001' then 'Waiting for content'
when fn.appstate='2002' then 'Installing'
when fn.appstate='2003' then 'Restart to continue'
when fn.appstate='2004' then 'Waiting for maintenance window'
when fn.appstate='2005' then 'Waiting for schedule'
when fn.appstate='2006' then 'Downloading dependent content'
when fn.appstate='2007' then 'Installing dependent content'
when fn.appstate='2008' then 'Restart to complete'
when fn.appstate='2009' then 'Content downloaded'
when fn.appstate='2010' then 'Waiting for update'
when fn.appstate='2011' then 'Waiting for user session reconnect'
when fn.appstate='2012' then 'Waiting for user logoff'
when fn.appstate='2013' then 'Waiting for user logon'
when fn.appstate='2014' then 'Waiting To Install'
when fn.appstate='2015' then 'Waiting Retry'
when fn.appstate='2016' then 'Waiting For Presentation Mode'
when fn.appstate='2017' then 'Waiting For Orchestration'
when fn.appstate='2018' then 'Waiting For Network'
when fn.appstate='2019' then 'Pending App-V Virtual Environment Update'
when fn.appstate='2020' then 'Updating App-V Virtual Environment'
when fn.appstate='3000' then 'Requirements not met'
when fn.appstate='3001' then 'Host Platform Not Applicable'
when fn.appstate='4000' then 'Unknown'
when fn.appstate='5000' then 'Deployment failed'
when fn.appstate='5001' then 'Evaluation failed'
when fn.appstate='5002' then 'Deployment failed'
when fn.appstate='5003' then 'Failed to locate content'
when fn.appstate='5004' then 'Dependency installation failed'
when fn.appstate='5005' then 'Failed to download dependent content'
when fn.appstate='5006' then 'Conflicts with another application deployment'
when fn.appstate='5007' then 'Waiting Retry'
when fn.appstate='5008' then 'Failed to uninstall superseded deployment type'
when fn.appstate='5009' then 'Failed to download superseded deployment type'
when fn.appstate='5010' then 'Failed to updating App-V Virtual Environment'
end as 'Laststatusmessage'
from fn_AppDTClientSummarizedState('') fn join v_r_system vrs on vrs.name0=fn.machinename
where fn.applicationname like 'Microsoft Outlook 2016%' and
fn.applicationname not like 'Microsoft Outlook 2016 XXXXXXXXX'
and fn.appstate not like '3000' and fn.deploymenttypename like '%Install%'
order by fn.machinename
select distinct fn.machinename as 'ComputerName',vrs.user_name0 as 'UserID' ,fn.applicationname as 'DisplayName', fn.deploymenttypename as deploymenttypename,
errorcode,
case when fn.appstate='1000' then 'Success'
when fn.appstate='1001' then 'Already Compliant'
when fn.appstate='1002' then 'Simulate Success'
when fn.appstate='2000' then 'In progress'
when fn.appstate='2001' then 'Waiting for content'
when fn.appstate='2002' then 'Installing'
when fn.appstate='2003' then 'Restart to continue'
when fn.appstate='2004' then 'Waiting for maintenance window'
when fn.appstate='2005' then 'Waiting for schedule'
when fn.appstate='2006' then 'Downloading dependent content'
when fn.appstate='2007' then 'Installing dependent content'
when fn.appstate='2008' then 'Restart to complete'
when fn.appstate='2009' then 'Content downloaded'
when fn.appstate='2010' then 'Waiting for update'
when fn.appstate='2011' then 'Waiting for user session reconnect'
when fn.appstate='2012' then 'Waiting for user logoff'
when fn.appstate='2013' then 'Waiting for user logon'
when fn.appstate='2014' then 'Waiting To Install'
when fn.appstate='2015' then 'Waiting Retry'
when fn.appstate='2016' then 'Waiting For Presentation Mode'
when fn.appstate='2017' then 'Waiting For Orchestration'
when fn.appstate='2018' then 'Waiting For Network'
when fn.appstate='2019' then 'Pending App-V Virtual Environment Update'
when fn.appstate='2020' then 'Updating App-V Virtual Environment'
when fn.appstate='3000' then 'Requirements not met'
when fn.appstate='3001' then 'Host Platform Not Applicable'
when fn.appstate='4000' then 'Unknown'
when fn.appstate='5000' then 'Deployment failed'
when fn.appstate='5001' then 'Evaluation failed'
when fn.appstate='5002' then 'Deployment failed'
when fn.appstate='5003' then 'Failed to locate content'
when fn.appstate='5004' then 'Dependency installation failed'
when fn.appstate='5005' then 'Failed to download dependent content'
when fn.appstate='5006' then 'Conflicts with another application deployment'
when fn.appstate='5007' then 'Waiting Retry'
when fn.appstate='5008' then 'Failed to uninstall superseded deployment type'
when fn.appstate='5009' then 'Failed to download superseded deployment type'
when fn.appstate='5010' then 'Failed to updating App-V Virtual Environment'
end as 'Laststatusmessage'
from fn_AppDTClientSummarizedState('') fn join v_r_system vrs on vrs.name0=fn.machinename
where fn.applicationname like 'Microsoft Outlook 2016%' and
fn.applicationname not like 'Microsoft Outlook 2016 XXXXXXXXX'
and fn.appstate not like '3000' and fn.deploymenttypename like '%Install%'
order by fn.machinename
Query for installed machines report 'Adobe Flash Player '
Query for installed machines report 'Adobe Flash Player ' :-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where arp.DisplayName0 LIKE 'Adobe Flash Player %'
order by sys.Netbios_Name0
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where arp.DisplayName0 LIKE 'Adobe Flash Player %'
order by sys.Netbios_Name0
LinkPoint installed machines report
LinkPoint installed machines report :-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0 ,
v_R_User.Full_User_Name0, v_R_User.Mail0,arp.installdate0
FROM v_r_system sys
JOIN v_R_User on sys.User_Name0 = v_R_User.User_Name0
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 like 'LinkPoint%'
order by sys.Netbios_Name0
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0 ,
v_R_User.Full_User_Name0, v_R_User.Mail0,arp.installdate0
FROM v_r_system sys
JOIN v_R_User on sys.User_Name0 = v_R_User.User_Name0
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 like 'LinkPoint%'
order by sys.Netbios_Name0
Installed software's report for the collection ID
Installed software's report for the collection ID :-
Declare @CollID char(8)
Set @CollID = 'ING00898'
SELECT Distinct
COMP.Name0 AS 'Machine Name',
COMP.UserName0 AS 'Username',
v_R_system.User_Domain0 AS ' Domain Name',
COMP.Manufacturer0 AS 'Make',
COMP.Model0 AS 'Model',
COMP.SystemType0 AS 'Architecture',
COMP.TotalPhysicalMemory0/1024 AS 'RAM',
ARP.Publisher0 AS 'Publisher',
ARP.DisplayName0 AS 'Program',
ARP.Version0 AS 'Version',
ARP.InstallDate0 AS 'Install Date',
ARP.ProdID0 AS 'Product ID'
FROM v_Add_Remove_Programs ARP
JOIN v_FullCollectionMembership FCM
on ARP.ResourceID = FCM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM COMP
on ARP.ResourceID = COMP.ResourceID
JOIN v_R_System on v_R_System.ResourceID = ARP.ResourceID
WHERE FCM.CollectionID = @CollID
AND ARP.DisplayName0 IS NOT NULL
AND ARP.ProdId0 NOT LIKE '%(KB%)%'
AND ARP.DisplayName0 NOT LIKE '%Update%'
Declare @CollID char(8)
Set @CollID = 'ING00898'
SELECT Distinct
COMP.Name0 AS 'Machine Name',
COMP.UserName0 AS 'Username',
v_R_system.User_Domain0 AS ' Domain Name',
COMP.Manufacturer0 AS 'Make',
COMP.Model0 AS 'Model',
COMP.SystemType0 AS 'Architecture',
COMP.TotalPhysicalMemory0/1024 AS 'RAM',
ARP.Publisher0 AS 'Publisher',
ARP.DisplayName0 AS 'Program',
ARP.Version0 AS 'Version',
ARP.InstallDate0 AS 'Install Date',
ARP.ProdID0 AS 'Product ID'
FROM v_Add_Remove_Programs ARP
JOIN v_FullCollectionMembership FCM
on ARP.ResourceID = FCM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM COMP
on ARP.ResourceID = COMP.ResourceID
JOIN v_R_System on v_R_System.ResourceID = ARP.ResourceID
WHERE FCM.CollectionID = @CollID
AND ARP.DisplayName0 IS NOT NULL
AND ARP.ProdId0 NOT LIKE '%(KB%)%'
AND ARP.DisplayName0 NOT LIKE '%Update%'
IE Installed version with OS
IE Installed version with OS :-
SELECT DISTINCT
dbo.v_R_System.Netbios_Name0,
ARP.FileName,
ARP.FileVersion,
CASE
WHEN ARP.FileVersion LIKE '4.%' THEN 'IE 4'
WHEN ARP.FileVersion LIKE '5.%' THEN 'IE 5'
WHEN ARP.FileVersion LIKE '6.%' THEN 'IE 6'
WHEN ARP.FileVersion LIKE '7.%' THEN 'IE 7'
WHEN ARP.FileVersion LIKE '8.%' THEN 'IE 8'
WHEN ARP.FileVersion LIKE '9.%' THEN 'IE 9'
When ARP.FileVersion LIKE '10.%' Then 'IE 10'
When ARP.FileVersion LIKE '11.%' Then 'IE 11'
ELSE '??' END AS 'Internet Explorer Ver',
dbo.v_FullCollectionMembership.SiteCode,
CASE
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'C%' THEN 'Japan'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'D%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'E%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'F%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'G%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'H%' THEN 'Belgium'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'I%' THEN 'Germany'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'J%' THEN 'France'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'K%' THEN 'Italy'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'L%' THEN 'PORTUGAL'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'M%' THEN 'SPAIN'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'N%' THEN 'UK'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'O%' THEN 'UK'
ELSE 'Unidentified' END AS 'Country',
v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_SoftwareFile AS ARP ON dbo.v_R_System.ResourceID = ARP.ResourceID
INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID
LEFT JOIN dbo.v_GS_WORKSTATION_STATUS AS HWSCAN ON dbo.v_R_System.ResourceID = HWSCAN.ResourceID
Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE (ARP.FileName = 'iexplore.exe') AND (ARP.FilePath LIKE '%:\prog%internet%')
and (sitecode != 'NULL') and (v_GS_OPERATING_SYSTEM.Caption0 != '%server%')
GROUP BY ARP.FileName, ARP.FileVersion,
dbo.v_FullCollectionMembership.SiteCode, dbo.v_R_System.Netbios_Name0,ARP.FilePath,v_GS_OPERATING_SYSTEM.Caption0
SELECT DISTINCT
dbo.v_R_System.Netbios_Name0,
ARP.FileName,
ARP.FileVersion,
CASE
WHEN ARP.FileVersion LIKE '4.%' THEN 'IE 4'
WHEN ARP.FileVersion LIKE '5.%' THEN 'IE 5'
WHEN ARP.FileVersion LIKE '6.%' THEN 'IE 6'
WHEN ARP.FileVersion LIKE '7.%' THEN 'IE 7'
WHEN ARP.FileVersion LIKE '8.%' THEN 'IE 8'
WHEN ARP.FileVersion LIKE '9.%' THEN 'IE 9'
When ARP.FileVersion LIKE '10.%' Then 'IE 10'
When ARP.FileVersion LIKE '11.%' Then 'IE 11'
ELSE '??' END AS 'Internet Explorer Ver',
dbo.v_FullCollectionMembership.SiteCode,
CASE
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'C%' THEN 'Japan'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'D%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'E%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'F%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'G%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'H%' THEN 'Belgium'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'I%' THEN 'Germany'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'J%' THEN 'France'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'K%' THEN 'Italy'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'L%' THEN 'PORTUGAL'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'M%' THEN 'SPAIN'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'N%' THEN 'UK'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'O%' THEN 'UK'
ELSE 'Unidentified' END AS 'Country',
v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_SoftwareFile AS ARP ON dbo.v_R_System.ResourceID = ARP.ResourceID
INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID
LEFT JOIN dbo.v_GS_WORKSTATION_STATUS AS HWSCAN ON dbo.v_R_System.ResourceID = HWSCAN.ResourceID
Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE (ARP.FileName = 'iexplore.exe') AND (ARP.FilePath LIKE '%:\prog%internet%')
and (sitecode != 'NULL') and (v_GS_OPERATING_SYSTEM.Caption0 != '%server%')
GROUP BY ARP.FileName, ARP.FileVersion,
dbo.v_FullCollectionMembership.SiteCode, dbo.v_R_System.Netbios_Name0,ARP.FilePath,v_GS_OPERATING_SYSTEM.Caption0
Count of installed software's
Count of installed software's :
select
DisplayName0,
Version0,
Count (Distinct arp.ResourceID) AS 'No Of Installed'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007',
'Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007',
'Microsoft Office Project Professional 2003','Microsoft Office Project Standard 2003',
'Microsoft Office Visio Professional 2003',
'Microsoft Office Visio Professional 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Standard 2007',
'Microsoft Visual Studio 2005','Microsoft Visual Studio 2008',
'Microsoft Visual Studio 2005 Professional Edition – ENU','Microsoft Visual Studio 2005 Team Suite – ENU','Microsoft Visual Studio 2008 Professional Edition – ENU',
'Microsoft Visual Studio 6.0 Enterprise Edition','Microsoft SQL Server 2005','Microsoft SQL Server 2000',
'Microsoft Project 2000 SR-1','Microsoft Project 2000','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Exchange','Microsoft Visio 2010 Professional','Microsoft Visio 2010 Standard')
Group by
DisplayName0,
Version0
Order by
DisplayName0,
Version0
select
DisplayName0,
Version0,
Count (Distinct arp.ResourceID) AS 'No Of Installed'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007',
'Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007',
'Microsoft Office Project Professional 2003','Microsoft Office Project Standard 2003',
'Microsoft Office Visio Professional 2003',
'Microsoft Office Visio Professional 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Standard 2007',
'Microsoft Visual Studio 2005','Microsoft Visual Studio 2008',
'Microsoft Visual Studio 2005 Professional Edition – ENU','Microsoft Visual Studio 2005 Team Suite – ENU','Microsoft Visual Studio 2008 Professional Edition – ENU',
'Microsoft Visual Studio 6.0 Enterprise Edition','Microsoft SQL Server 2005','Microsoft SQL Server 2000',
'Microsoft Project 2000 SR-1','Microsoft Project 2000','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Exchange','Microsoft Visio 2010 Professional','Microsoft Visio 2010 Standard')
Group by
DisplayName0,
Version0
Order by
DisplayName0,
Version0
Office all version report
Office all version report :
SELECT Distinct
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
v_GS_OPERATING_SYSTEM.Caption0 ,
case when v_gs_processor.addresswidth0 = 64 then '64bit OS'
when v_gs_processor.addresswidth0=32 then '32bit OS'
end as [Operating System Type],
CASE
WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
WHEN a.FileVersion LIKE '15.%' THEN 'Office 2013'
WHEN a.FileVersion LIKE '16.%' THEN 'Office 2016'
ELSE 'Lower Version' END AS 'Office Version',
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0
FROM
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID=b.ResourceID
join v_gs_processor ON b.ResourceID = v_gs_processor.ResourceID
WHERE FileName = 'outlook.exe'
GROUP BY
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0,
v_gs_processor.addresswidth0
ORDER BY
b.Netbios_Name0
SELECT Distinct
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
v_GS_OPERATING_SYSTEM.Caption0 ,
case when v_gs_processor.addresswidth0 = 64 then '64bit OS'
when v_gs_processor.addresswidth0=32 then '32bit OS'
end as [Operating System Type],
CASE
WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
WHEN a.FileVersion LIKE '15.%' THEN 'Office 2013'
WHEN a.FileVersion LIKE '16.%' THEN 'Office 2016'
ELSE 'Lower Version' END AS 'Office Version',
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0
FROM
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID=b.ResourceID
join v_gs_processor ON b.ResourceID = v_gs_processor.ResourceID
WHERE FileName = 'outlook.exe'
GROUP BY
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0,
v_gs_processor.addresswidth0
ORDER BY
b.Netbios_Name0
Office installed machine report for 64 & 32 - bit machines
Office installed machine report for 64 - bit machines :
SELECT DISTINCT v_GS_SYSTEM.Name0 as 'Machine Name',v_R_System.User_Name0,
v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,v_R_System.operatingSystem0,
v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS_64 ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIKE '%Microsoft Office Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) Like 'Microsoft Office Professional%'or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIke 'Microsoft Office Enterprise %'
Office installed machine report for 32 - bit machines :
SELECT DISTINCT v_GS_SYSTEM.Name0 AS ' Machine Name', v_R_System.User_Name0,v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,
v_R_System.operatingSystem0, v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft % Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) Like 'Microsoft % Professional%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIke 'Microsoft % Enterprise %'
Order by v_GS_SYSTEM.Name0
SELECT DISTINCT v_GS_SYSTEM.Name0 as 'Machine Name',v_R_System.User_Name0,
v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,v_R_System.operatingSystem0,
v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS_64 ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIKE '%Microsoft Office Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) Like 'Microsoft Office Professional%'or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIke 'Microsoft Office Enterprise %'
Office installed machine report for 32 - bit machines :
SELECT DISTINCT v_GS_SYSTEM.Name0 AS ' Machine Name', v_R_System.User_Name0,v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,
v_R_System.operatingSystem0, v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft % Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) Like 'Microsoft % Professional%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIke 'Microsoft % Enterprise %'
Order by v_GS_SYSTEM.Name0
Query to find the machine names for the Email ID's
This summary is not available. Please
click here to view the post.
Query is to find the Machine and Email ID for Full user name
Query is to find the Machine and Email ID for Full user name :
Select distinct Netbios_name0,USer_domain0,v.User_Name0,Mail0,v_R_User.Full_User_Name0,Last_Logon_Timestamp0
from v_r_System v
inner join v_R_User on v_r_user.User_Name0 = v.User_Name0
where v_R_User.Full_User_Name0 in ('Donn xxxxxxxx','xxxxxxxxxx','xxxxxxxxx','xxxxxxxxxx',
'xxxxxxxxx Davis','Darla xxxxxxxx')
Select distinct Netbios_name0,USer_domain0,v.User_Name0,Mail0,v_R_User.Full_User_Name0,Last_Logon_Timestamp0
from v_r_System v
inner join v_R_User on v_r_user.User_Name0 = v.User_Name0
where v_R_User.Full_User_Name0 in ('Donn xxxxxxxx','xxxxxxxxxx','xxxxxxxxx','xxxxxxxxxx',
'xxxxxxxxx Davis','Darla xxxxxxxx')
Query for Global Hardware and Software Scan report :-
Query for Global Hardware and Software Scan report :-
SELECT Distinct
v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],
V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE] ,
v_R_System.Last_Logon_Timestamp0
FROM v_R_System
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
H\W and S|W scan report for the particular file :
SELECT Distinct
v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],
V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE] ,
v_GS_SoftwareFile.FileName AS [Display Name] ,
v_R_System.Last_Logon_Timestamp0
FROM v_R_System
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
Where v_GS_SoftwareFile.FileName = 'WINPROJ.EXE'
SELECT Distinct
v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],
V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE] ,
v_R_System.Last_Logon_Timestamp0
FROM v_R_System
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
H\W and S|W scan report for the particular file :
SELECT Distinct
v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],
V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE] ,
v_GS_SoftwareFile.FileName AS [Display Name] ,
v_R_System.Last_Logon_Timestamp0
FROM v_R_System
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
Where v_GS_SoftwareFile.FileName = 'WINPROJ.EXE'
SQL query for PROJECT and VISIO
PROJECT : -
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 like '%Project%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
order by sys.Netbios_Name0
'Project' with Display Name :-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0,arp.Version0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 = 'Microsoft Office Project Standard 2010'or
arp.DisplayName0 = 'Microsoft Project Professional 2010' or
arp.DisplayName0 = 'Microsoft Project Standard 2010' or
arp.DisplayName0 = 'Microsoft Office Project Standard 2010'
order by sys.Netbios_Name0
VISIO:-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 like '%Visio%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
order by sys.Netbios_Name0
VISIO With Display Names:-
SELECT DISTINCT
v_R_System.Netbios_Name0 as 'ComputerName' ,
v_R_System.User_Name0,
v_r_system.Full_Domain_Name0 as 'Domain Name'
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
,v_GS_ADD_REMOVE_PROGRAMS.Version0
,V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE]
FROM v_R_System
inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
Where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2013'
or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Office Visio 2010' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Standard 2010' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2016' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Professional 2010'
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 like '%Project%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
order by sys.Netbios_Name0
'Project' with Display Name :-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0,arp.Version0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 = 'Microsoft Office Project Standard 2010'or
arp.DisplayName0 = 'Microsoft Project Professional 2010' or
arp.DisplayName0 = 'Microsoft Project Standard 2010' or
arp.DisplayName0 = 'Microsoft Office Project Standard 2010'
order by sys.Netbios_Name0
VISIO:-
Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
where
arp.DisplayName0 like '%Visio%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
order by sys.Netbios_Name0
VISIO With Display Names:-
SELECT DISTINCT
v_R_System.Netbios_Name0 as 'ComputerName' ,
v_R_System.User_Name0,
v_r_system.Full_Domain_Name0 as 'Domain Name'
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
,v_GS_ADD_REMOVE_PROGRAMS.Version0
,V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE]
FROM v_R_System
inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
Where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2013'
or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Office Visio 2010' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Standard 2010' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2016' or
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Professional 2010'
WQL Query for VISIO and Projects installed machine report
select SMS_R_System.Name, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_G_System_INSTALLED_SOFTWARE.InstallDate, SMS_G_System_INSTALLED_SOFTWARE.TimeStamp, SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Standard%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Professional%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Premium%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Office Visio%" order by SMS_R_System.Name
---------------------------------------------------
' Projects '
------------------------------------------------------------
select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate, SMS_G_System_ADD_REMOVE_PROGRAMS.TimeStamp from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Standard%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Professional%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office Project%" order by SMS_R_System.Name
---------------------------------------------------
' Projects '
------------------------------------------------------------
select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate, SMS_G_System_ADD_REMOVE_PROGRAMS.TimeStamp from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Standard%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Professional%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office Project%" order by SMS_R_System.Name
Tuesday, July 18, 2017
SQL QUERY for Display name is for Adobe Shockwave Player
Example for Adobe Shockwave Player:
select v_R_System.ResourceID,
v_R_System.ResourceType,
v_R_System.Name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceI
d where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Adobe%Shockwave%Player%' )
select v_R_System.ResourceID,
v_R_System.ResourceType,
v_R_System.Name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceI
d where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Adobe%Shockwave%Player%' )
Qury for Hardware scan with machines information
SELECT distinct sys.Netbios_Name0 AS AssetID, sys.User_Name0 AS [User Name], usr.Full_User_Name0 AS [Full User Name], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], sys.AD_Site_Name0 AS [AD Site Location],
vWorkstationStatus.LastHardwareScan AS [Last Hardware Scan], MAX(v_AgentDiscoveries.AgentTime) AS Discovery_time
FROM v_AgentDiscoveries
INNER JOIN v_R_User AS usr ON v_AgentDiscoveries.ResourceId = usr.ResourceID
RIGHT OUTER JOIN v_GS_PC_BIOS
INNER JOIN v_R_System_Valid AS sys ON v_GS_PC_BIOS.ResourceID = sys.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM ON sys.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN vWorkstationStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = vWorkstationStatus.ResourceID ON usr.User_Name0 = sys.User_Name0
GROUP BY sys.Netbios_Name0, sys.User_Name0, sys.AD_Site_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0, vWorkstationStatus.LastHardwareScan, usr.Full_User_Name0
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], sys.AD_Site_Name0 AS [AD Site Location],
vWorkstationStatus.LastHardwareScan AS [Last Hardware Scan], MAX(v_AgentDiscoveries.AgentTime) AS Discovery_time
FROM v_AgentDiscoveries
INNER JOIN v_R_User AS usr ON v_AgentDiscoveries.ResourceId = usr.ResourceID
RIGHT OUTER JOIN v_GS_PC_BIOS
INNER JOIN v_R_System_Valid AS sys ON v_GS_PC_BIOS.ResourceID = sys.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM ON sys.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN vWorkstationStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = vWorkstationStatus.ResourceID ON usr.User_Name0 = sys.User_Name0
GROUP BY sys.Netbios_Name0, sys.User_Name0, sys.AD_Site_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0, vWorkstationStatus.LastHardwareScan, usr.Full_User_Name0
Query for Domain wise with Full usernames
Full usernames:-
---------------------------------------
select
sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0
where sys.resource_domain_or_workgr0 = 'NA'
---------------------------------------
select
sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0
where sys.resource_domain_or_workgr0 = 'NA'
Application NOT Installed machine names query
Application NOT Installed machine names
-------------------------------------------------------------------------
select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Microsoft Outlook MUI (English) 2016%' AND v_GS_ADD_REMOVE_PROGRAMS.prodID0 Like '%XXXXXXXX%' AND
V_GS_ADD_REMOVE_PROGRAMS.version0 like '%XXXXXXXXX%')
-------------------------------------------------------------------------------------------------------
select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0 as 'Region',
v_R_SYSTEM.Client0
from v_R_System
where v_R_System.Netbios_Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
FROM v_R_System
INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
inner Join v_GS_ADD_REMOVE_PROGRAMS on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
Where v_GS_SoftwareFile.FileName = 'outlook.exe'and ProdID0 = '{XXXXXXXXXX}' and Version0 = 'XXXXXXXXXXXX')
-------------------------------------------------------------------------
select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Microsoft Outlook MUI (English) 2016%' AND v_GS_ADD_REMOVE_PROGRAMS.prodID0 Like '%XXXXXXXX%' AND
V_GS_ADD_REMOVE_PROGRAMS.version0 like '%XXXXXXXXX%')
-------------------------------------------------------------------------------------------------------
select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0 as 'Region',
v_R_SYSTEM.Client0
from v_R_System
where v_R_System.Netbios_Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
FROM v_R_System
INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
inner Join v_GS_ADD_REMOVE_PROGRAMS on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
Where v_GS_SoftwareFile.FileName = 'outlook.exe'and ProdID0 = '{XXXXXXXXXX}' and Version0 = 'XXXXXXXXXXXX')
Application installation report for a OUTLOOK.EXE
SELECT DISTINCT
v_R_System.Netbios_Name0 as 'ComputerName'
,v_r_system.Full_Domain_Name0 as 'Domain Name'
, v_gs_softwareFile.FilePath
,v_gs_softwarefile.filename
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
WHERE v_GS_SoftwareFile.FileName = 'outlook.exe' and V_GS_ADD_Remove_programs.DisplayName0 like 'Microsoft Office Professional Plus 2016'
v_R_System.Netbios_Name0 as 'ComputerName'
,v_r_system.Full_Domain_Name0 as 'Domain Name'
, v_gs_softwareFile.FilePath
,v_gs_softwarefile.filename
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID)
WHERE v_GS_SoftwareFile.FileName = 'outlook.exe' and V_GS_ADD_Remove_programs.DisplayName0 like 'Microsoft Office Professional Plus 2016'
SQL Query To Return Add OR Remove Programs Installed On A Specified Machine
SQL Query To Return Add OR Remove Programs Installed On A Specified Machine
SQL Query:
Select
P.DisplayName0,
P.ProdId0,
P.Publisher0,
P.Version0
From v_Add_Remove_Programs P
Join v_R_System S on P.ResourceId = S.ResourceId
Where Name0 = ‘MachineName’
-------------------------------------------------------------------------------------------
SQL Query To Retrieve Clients Last Boot up Date
SQL Query To Retrieve Clients Last Boot up Date:
-------------------------------------------------
Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Last Logged on User Name’,
Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’
From v_R_System SD
Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID
Order By ‘Machine Name’
-------------------------------------------------
Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Last Logged on User Name’,
Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’
From v_R_System SD
Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID
Order By ‘Machine Name’
Application installation report with product code
select Name0, netbios_name0,user_name0,ProdID0 ,Displayname0, installdate0,publisher0,version0 from v_r_system
inner join v_Add_Remove_Programs
on v_Add_Remove_Programs.ResourceID=v_R_System.ResourceID
where ProdID0 = 'XXXXXXXXXXXX'
-------------------------------------------------------------------------
Select *from v_Add_Remove_Programs
where ProdID0 = 'XXXXXXXXX' and Version0 = 'XXXXXXXX'
--------------------------------------------------------------------------------
SQL Query For Windows Media Player Versions:
--------------------------------------------------
SQL Query:
Select Distinct
v_R_System.Name0,
v_GS_SoftwareFile.FileName,
v_GS_SoftwareFile.FileDescription,
v_GS_SoftwareFile.FileVersion
From v_R_System
Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
Where FileName = ‘wmplayer.exe’
inner join v_Add_Remove_Programs
on v_Add_Remove_Programs.ResourceID=v_R_System.ResourceID
where ProdID0 = 'XXXXXXXXXXXX'
-------------------------------------------------------------------------
Select *from v_Add_Remove_Programs
where ProdID0 = 'XXXXXXXXX' and Version0 = 'XXXXXXXX'
--------------------------------------------------------------------------------
SQL Query For Windows Media Player Versions:
--------------------------------------------------
SQL Query:
Select Distinct
v_R_System.Name0,
v_GS_SoftwareFile.FileName,
v_GS_SoftwareFile.FileDescription,
v_GS_SoftwareFile.FileVersion
From v_R_System
Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
Where FileName = ‘wmplayer.exe’
SQL query for Application app enforcement deployment status in SCCM 2012
Application app enforcement deployment
status with installation errors
select distinct vrs.user_name0,fn.machinename,fn.applicationname,fn.deploymenttypename,fn.Enforcementtime,errorcode,
case when fn.appstate='1000' then
'Success'
when fn.appstate='1001' then 'Already
Compliant'
when fn.appstate='1002' then
'Simulate Success'
when fn.appstate='2000' then 'In
progress'
when fn.appstate='2001' then 'Waiting
for content'
when fn.appstate='2002' then
'Installing'
when fn.appstate='2003' then 'Restart
to continue'
when fn.appstate='2004' then 'Waiting
for maintenance window'
when fn.appstate='2005' then 'Waiting
for schedule'
when fn.appstate='2006' then
'Downloading dependent content'
when fn.appstate='2007' then
'Installing dependent content'
when fn.appstate='2008' then 'Restart
to complete'
when fn.appstate='2009' then 'Content
downloaded'
when fn.appstate='2010' then 'Waiting
for update'
when fn.appstate='2011' then 'Waiting
for user session reconnect'
when fn.appstate='2012' then 'Waiting
for user logoff'
when fn.appstate='2013' then 'Waiting
for user logon'
when fn.appstate='2014' then 'Waiting
To Install'
when fn.appstate='2015' then 'Waiting
Retry'
when fn.appstate='2016' then 'Waiting
For Presentation Mode'
when fn.appstate='2017' then 'Waiting
For Orchestration'
when fn.appstate='2018' then 'Waiting
For Network'
when fn.appstate='2019' then 'Pending
App-V Virtual Environment Update'
when fn.appstate='2020' then
'Updating App-V Virtual Environment'
when fn.appstate='3000' then
'Requirements not met'
when fn.appstate='3001' then 'Host
Platform Not Applicable'
when fn.appstate='4000' then
'Unknown'
when fn.appstate='5000' then
'Deployment failed'
when fn.appstate='5001' then
'Evaluation failed'
when fn.appstate='5002' then
'Deployment failed'
when fn.appstate='5003' then 'Failed
to locate content'
when fn.appstate='5004' then
'Dependency installation failed'
when fn.appstate='5005' then 'Failed
to download dependent content'
when fn.appstate='5006' then
'Conflicts with another application deployment'
when fn.appstate='5007' then 'Waiting
Retry'
when fn.appstate='5008' then 'Failed
to uninstall superseded deployment type'
when fn.appstate='5009' then 'Failed
to download superseded deployment type'
when fn.appstate='5010' then 'Failed
to updating App-V Virtual Environment'
end as 'Laststatusmessage'
from fn_AppDTClientSummarizedState('') fn join
v_r_system vrs on vrs.name0=fn.machinename
where fn.applicationname like
'%OFFICE02013ENGC2A4%' and fn.appstate not like '3000' and
fn.deploymenttypename in ('OFFICE02013ENGC2A4-Win7','OFFICE02013ENGC2A4-Win8')
Subscribe to:
Posts (Atom)
site backup job manually
Start a scheduled SCCM 2012 site backup job manually You might want to start a scheduled SCCM 2012 site backup job manually. off co...
