Last Hardware inventory date for the systems that are assigned to a Site code

set @SMSSiteCode = (case when IsNULL(@SMSSiteCode,'')='' then '%' else @SMSSiteCode end)

SELECT DISTINCT SYS.Netbios_Name0, SYS.Operating_System_Name_and0,

  HWSCAN.LastHWScan, SWSCAN.LastScanDate, SWSCAN.LastCollectedFileScanDate, v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0

FROM v_R_System SYS

LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID left join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = SYS.ResourceID where v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 like @SMSSiteCode

Create a prompt with a variable name - SMSsitecode

begin
if (@__filterwildcard = '')
  select distinct SMS_Assigned_Sites0 as SMSSiteCode From v_RA_System_SMSAssignedSites
else
  select distinct SMS_Assigned_Sites0 as SMSSiteCode  from v_RA_System_SMSAssignedSites
    where ((SMS_Assigned_Sites0 like @__filterwildcard))
    order by SMS_Assigned_Sites0
end

ConfigMgr Report to show available Hard Drive space on machines with its client version

select

v_R_System.Name0, client_version0,v_GS_LOGICAL_DISK.Name0 as Drive,

v_GS_LOGICAL_DISK.Size0 as Size,

v_GS_LOGICAL_DISk.FreeSpace0 as FreeSpace, size0 as Used

from

v_R_System inner join v_GS_LOGICAL_DISK on v_R_System

.ResourceID = v_GS_LOGICAL_DISK.ResourceID inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID=v_R_System

.ResourceID

where

v_GS_LOGICAL_DISK.DriveType0 = 3 and v_FullCollectionMembership.CollectionID = @CollectionID

order

by FreeSpace

Create a prompt Query with a variable “CollectionID”

select CollectionID,Name from v_Collection

ConfigMgr SQL query to list hardware and add/remove programs inventory for a client

It lists NetBIOS Name, Install Site Code, processor Name, Processor Manufacturer, Model, Total Physical Memory, and Serial Number for a selected client.

It also lists the add remove programs inventory for a client.

SQL Query:

SELECT V_R_SYSTEM.Name0,v_RA_System_SMSInstalledSites.SMS_Installed_Sites0,v_GS_PROCESSOR.Name0,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_COMPUTER_SYSTEM.Name0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_PC_BIOS.SerialNumber0 from v_r_system inner join v_GS_PROCESSOR on v_R_system.ResourceID=v_GS_PROCESSOR.ResourceID inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_r_system.ResourceID inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID=v_r_system.ResourceID inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID=v_r_system.ResourceID inner join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID=V_r_system.ResourceID where v_r_system.Name0 like @computername

Select v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_Add_Remove_Programs.Version0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID

WHERE v_R_System.Netbios_Name0 = @computername

Prompt Query:

Create a prompt with a variable “computername” using the following query.

begin

 if (@__filterwildcard = '')

  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0

 else

  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1

  and SYS.Netbios_Name0 like @__filterwildcard

  ORDER By SYS.Netbios_Name0

end

WQL query list all the systems that does not contain a particular software

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_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.ResourceID not in (select SMS_R_System.Resourceid  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 = "Symantec Endpoint Protection")

Query to list both X64 and X86 system with a program installled.

The following query returns all the x64 and x86 systems that are installed with VMWare tools.  You can replace “VMware Tools” with your program Add Remove Program’s to query for other programs.

select SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.ResourceId in (select SMS_R_System.ResourceID  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 = "VMware Tools") or SMS_R_System.ResourceId in (select SMS_R_System.ResourceID from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where  SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "VMware Tools") order by SMS_R_System.NetbiosName

SMS/ConfigMgr SQL query to find the list of systems in a specific IP subnet

SELECT SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0, AIP.IP_Subnets0, SYS.Client_Version0

FROM v_R_System  as SYS

JOIN v_RA_System_IPSubnets as AIP on SYS.ResourceID=AIP.ResourceID

WHERE AIP.IP_Subnets0 = '192.168.0.0'

Order by SYS.Netbios_Name0

Replace 192.168.0.1 with your IP subnet.

SMS/ConfigMgr SQL query to list the systems in specific IP subnets

SELECT SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0, AIP.IP_Subnets0, SYS.Client_Version0
FROM v_R_System  as SYS
JOIN v_RA_System_IPSubnets as AIP on SYS.ResourceID=AIP.ResourceID
WHERE AIP.IP_Subnets0 in ('192.168.0.0','192.168.1.0')
Order by SYS.Netbios_Name0

Replace '192.168.0.0','192.168.1.0'... with your list of IP Subnets.