Querys

SCCM Query To check machine RAM Memory

********************************

select distinct 

SMS_R_System.NetbiosName, 

SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 

SMS_R_System.IPAddresses, SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DNSDomain 

from  SMS_R_System inner join SMS_G_System_PC_BIOS 

on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_X86_PC_MEMORY 

on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION 

on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId 

order by SMS_R_System.NetbiosName

*****************************************

Duplicate PC’s or GUIDS in my SCCM

Have you ran across where you keep seeing PC’s show up in your Collections with duplicate names and every time you update and refresh the collection you’ll see a different set of PC’s show duplicates.

I was seeing this happen while at a customer and it turned out they imaged a machine with ghost and then used that image over and over and over. Well, if they don’t prep that machine correctly with sysprep and the have the SCCM or SMS client already installed, this will cause a problem that you could be tracking down for days.

To resolve this issue, let’s see how many duplicates you have. Create a collection and add the following 3 queries as part of the collection. Then update and refresh your collection.

Query 1

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 ClientVersion = „2.50.4160.2000“ or Client is NULL

Query 2

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 join SMS_G_System_System on SMS_R_System.ResourceID = SMS_G_System_System.ResourceID where SMS_R_System.Name <> SMS_G_System_System.Name

Query 3

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 SMS_R_System.ResourceID in (select SMS_GH_System_System.ResourceID from SMS_G_System_System join SMS_GH_System_System on SMS_G_System_System.ResourceID = SMS_GH_System_System.ResourceID where SMS_G_System_System.Name <> SMS_GH_System_System.Name)

Now you should have a list of machines showing duplicate GUIDS in your database.

Check Tasksequence installation for a Client

# Will check the Task Sequence Deplyments on the Specified Client – Is a Status Message Query!

*****************************************************************

select SMS_StatusMessage.*, SMS_StatMsgInsStrings.*, SMS_StatMsgAttributes.*, SMS_StatusMessage.MachineName, SMS_StatMsgAttributes.AttributeTime, SMS_StatMsgAttributes.AttributeID from  SMS_StatusMessage left join SMS_StatMsgInsStrings on SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID left join SMS_StatMsgAttributes on SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID where SMS_StatusMessage.MachineName = ##PRM:SMS_StatusMessage.MachineName## and SMS_StatusMessage.SiteCode = „SITE“ and SMS_StatMsgAttributes.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## order by SMS_StatMsgAttributes.AttributeTime DESC

****************************************************************

System Reboot less that 1 Day – Collection Query!

*************************************************************

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_OPERATING_SYSTEM.Caption like „%xp%“ or SMS_G_System_OPERATING_SYSTEM.Caption like „%vista%“ or SMS_G_System_OPERATING_SYSTEM.Caption like „%windows 10%“) and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) <1)

*************************************************************

All Model IDs – Collection Query

select distinct SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId

Hardware Query for a Collection

**************************************************************

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model = „HP Z2 Tower G5 Workstation“

**************************************************************

SCCM Client install <3 Days

***************************************************************

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId     WHERE    dateDiff(dd, SMS_G_System_OPERATING_SYSTEM.InstallDate, GetDate()) < 3

******************************************************************