Computers that have 30 days or less hardware information
SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
b.LastHWScan
FROM
v_R_System a
INNER JOIN
v_GS_WORKSTATION_STATUS b
ON
a.ResourceID = b.ResourceID
WHERE
b.LastHWScan >= Dateadd(day, -30, getdate())
Collection of clients not approved
SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = ‘2’
Collection of clients that have been approved
SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = ‘1’
Collection of clients requiring a reboot
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System AS SMS_R_SYSTEM
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_SYSTEM.ResourceID
WHERE
c.LastEnforcementMessageID = 9
Collection of ConfigMgr clients waiting for another installation to finish
SELECT
SMS_R_SYSTEM.ResourceID AS[ResourceID],
SMS_R_SYSTEM.ResourceType AS[Type],
SMS_R_SYSTEM.Name0 AS[Name],
SMS_R_SYSTEM.SMS_Unique_Identifier0 AS[UniqueID],
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0 AS[Domain],
SMS_R_SYSTEM.Client0 AS[Name]
FROM
v_R_System AS SMS_R_System
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_System.ResourceID
WHERE
c.LastEnforcementMessageID = 3
Return list of computers with C: drive space great than 100 MB
SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = ‘C:’ AND ((b.Size0)> 100)
All SCCM client computers with less than 1 GB free disk space on C:
SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = ‘C:’ AND ((b.FreeSpace0)< 1000)
All SCCM client computers with less than 10 GB free disk space on C:
SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = ‘C:’ AND ((b.FreeSpace0)< 10000)
All SCCM client computers with greater than 50 GB free disk space on C:
SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = ‘C:’ AND ((b.FreeSpace0)> 50000)
All computer objects with client installed in the specific site “001”
SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
b.SMS_Installed_Sites0 AS[SiteCode]
FROM
v_R_System a
LEFT OUTER JOIN
v_RA_System_SMSInstalledSites b
ON
a.ResourceID = b.ResourceID
WHERE
b.SMS_Installed_Sites0 = ‘001’ AND a.Client0 = ‘1’
All SCCM Clients Matching Version
Build 1702
SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like ‘5.00.8498.1007’ –OR a.Client_Version0 like ‘5.00.8498.1008’
Build 1610
SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like ‘5.00.8458.1005’ –OR a.Client_Version0 like ‘5.00.8458.1005’
Build 1606
SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like ‘5.00.8412.1006’ –OR a.Client_Version0 like ‘5.00.8412.1006’
Build 1511
SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like ‘5.0.8325.1000’ –OR a.Client_Version0 like ‘5.0.8325.1000’
Collection with all computers without a ConfigMgr client
SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
V_R_System as a
WHERE
a.Client0 is null
Return machine name, serial, and AD container
SELECT
a.Name0 AS[Name],
b.SerialNumber0 AS[Serial],
c.System_Container_Name0
FROM
v_R_System a
LEFT JOIN
v_GS_PC_BIOS b on b.ResourceID = a.ResourceID
INNER JOIN
System_System_Container_Name_A c on c.ItemKey = a.ResourceID
WHERE b.SerialNumber0 IS NOT NULL
Return computers in a specific domain
SELECT
a.Name0
FROM
v_R_System a
WHERE
a.Resource_Domain_OR_Workgr0 = ‘AWESOME’
Collection for all Workstations
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%workstation%’
Collection of all Windows 10 clients
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%workstation% 10.0’
Collection of all Windows 8.1 clients
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%workstation% 6.3’
Collection of all Windows 8 clients
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%workstation% 6.2’
Collection of all Windows 7 clients
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%workstation% 6.1’
Use this query to create collections for your various computer models
SELECT DISTINCT
a.Name0,
b.Manufacturer0,
b.Model0
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like ‘%Hewlett-Packard%’ or b.Manufacturer0 like ‘%VMWare, Inc.%’
Collection of computers ending with odd numbers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like ‘%1’
or SMS_R_System.Name0 like ‘%3’
or SMS_R_System.Name0 like ‘%5’
or SMS_R_System.Name0 like ‘%7’
or SMS_R_System.Name0 like ‘%9’
Collection of computers ending with even numbers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like ‘%0’
or SMS_R_System.Name0 like ‘%2’
or SMS_R_System.Name0 like ‘%4’
or SMS_R_System.Name0 like ‘%6’
or SMS_R_System.Name0 like ‘%8’
Collection of computers ending with LAB
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like ‘%LAB’
Collection of computers beginning with LAB
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like ‘LAB%’
Collection of computers without failing hard drive
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 != ‘Pred Fail’
ORDER BY
SMS_R_System.Name0 DESC
Collection of computers with failing hard drive
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 = ‘Pred Fail’
ORDER BY
SMS_R_System.Name0 DESC
Collection for all Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server%’
Collection of all Windows 2016 Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server% 10.0’
Collection of all Windows 2012 R2 Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server% 6.3’
Collection of all Windows 2012 Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server% 6.2’
Collection of all Windows 2008 R2 Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server% 6.1’
Collection of all Windows 2008 Servers
SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like ‘%server% 6.0’
Collection of all Domain Controllers
SELECT
*
FROM
v_R_System SMS_R_System
INNER JOIN
v_GS_COMPUTER_SYSTEM
ON
v_GS_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
WHERE
v_GS_COMPUTER_SYSTEM.Roles0 like ‘%Domain_Controller%’
Query based collection for all Microsoft Exchange Servers
SELECT
*
FROM
v_R_System
INNER JOIN
v_GS_SERVICE
ON
v_GS_SERVICE.ResourceId = v_R_System.ResourceId
WHERE
v_GS_SERVICE.Name0 like ‘%Microsoft Exchange %’
SQL Server Collections
2016
SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like ‘%Microsoft SQL Server 2016%’
2014
SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like ‘%Microsoft SQL Server 2014%’
2012
SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like ‘%Microsoft SQL Server 2012%’
2008
SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like ‘%Microsoft SQL Server 2008%’
Query based collection based on IP subnet
SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Subnets0 AS[Subnet]
FROM
v_RA_System_IPSubnets a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Subnets0 LIKE ‘10.0.0.0’
Query based collection based on IP range
SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Addresses0 AS[IP]
FROM
v_RA_System_IPAddresses a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Addresses0 LIKE ‘10.1.0.1[1-9]’
All Dell Systems
SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like ‘%Dell%’
All Hewlett-Packard Systems
SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like ‘%HP%’ or b.Manufacturer0 like ‘%Hewlett-Packard%’
All Lenovo Systems
SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like ‘%Lenovo%’
All Physical Systems
SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
v_R_System a
INNER JOIN
v_GS_SYSTEM_ENCLOSURE b
ON
b.ResourceID = a.ResourceId
WHERE
b.ChassisTypes0 = ’23’ or b.ChassisTypes0 = ’17’
Pull Distribution Points with Source Distribution Points SQL Query
SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [DP for Pull],
dbo.v_DistributionPoints.IsPeerDP,
dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [DP List],
dbo.v_DistributionPoints.IsPXE,
dbo.v_DistributionPoints.Description
FROM
dbo.vPullDPFullMap
INNER JOIN
dbo.v_DistributionPoints
ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath
Report to list of all users laptops
SELECT DISTINCT
dbo.v_R_System.Name0 AS [Computer Name],
dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM
dbo.v_GS_SYSTEM_ENCLOSURE
INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM
ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘8’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘9’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’10’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’11’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’12’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’14’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’18’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’21’)
All application list
SELECT
*
FROM fn_ListLatestApplicationCIs(1033)
SELECT
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
FROM
fn_ListLatestApplicationCIs(1033)
GROUP BY
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
ORDER BY
DateCreated DESC
All OS with Versions
SELECT DISTINCT
dbo.v_R_System.Netbios_Name0 as [Machine Name],
dbo.v_R_System.User_Name0 as [User Name],
dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain],
dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name],
dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],
dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM
dbo.v_R_System
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM
ON
dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
Software Updates Installed using SCCM or Manually
SELECT sys.Name0,ui.BulletinID, ui.ArticleID,ui.Title,
CASE
WHEN (ucs.Status=2 and ui.IsDeployed=0 ) then ‘Required_General’
WHEN (ucs.Status=2 and ui.IsDeployed=1 ) then ‘Required_ITICSDeploy’
WHEN (UCS.Status=3 and ui.IsDeployed=1 ) then ‘Installed_SCCM’
WHEN (UCS.Status=3 and ui.IsDeployed=0 ) then ‘Installed_Manual’
WHEN UCS.Status=0 then ‘Unknown’ end as ‘Status’,
CASE
WHEN ui.severity=10 THEN ‘Critical’
WHEN ui.severity=8 THEN ‘Important’
WHEN ui.severity=6 THEN ‘Moderate’
WHEN ui.severity=2 THEN ‘Low’ WHEN ui.severity=0 THEN ‘AddOn’ end as ‘Severity’
FROM
v_R_System sys
INNER JOIN
v_UpdateComplianceStatus UCS ON sys.ResourceID = ucs.ResourceID
INNER JOIN
v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
WHERE
-UI.IsDeployed=1 and sys.Netbios_Name0=’computer1′
ORDER BY Status