SQL-WQL

 

Collections of systems that needs reboot after the patch:

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
inner join SMS_G_System_PatchStatusEx on SMS_G_System_PatchStatusEx.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PatchStatusEx.LastStateName = "reboot pending"

 

To add computer in collection from AD group:

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 SystemGroupName like "%AD Group NAME"

Get all virtual systems query

 
select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Manufacturer in ("VMware, Inc.","Microsoft Corporation") 
 

 Sql script to grant access to the specific SCCM DB table

 
--This script adds permissions to allow the custom App
--to dynamically determine the DP for a bare metal machine, depending on
--where a machine is located.  This group's members are support staff that will
--be executing the Custom App.
use SMS_XNC
GO
sp_grantlogin @loginame = 'ND\XME-WksInstallers-GBL.GG'
GRANT CONNECT TO "ND\XME-WksInstallers-GBL.GG"
GRANT SELECT ON DPInfo TO "ND\XME-WksInstallers-GBL.GG"
 
 

Systems that are discovered in AD in last 45 days w/o client

 

select distinct
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 
(SMS_R_System.Client = 0 or SMS_R_System.Client is null )
 and
DATEDIFF(Day,  SMS_R_System.whenChanged, GetDate()) < 45 

 
 
 
Comments