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.

SMS/SCCM SQL Query Netbios Name, Model , CPU name, Memory (Ram) and Sitecode


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 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

WQL Query for show the list of systems with two applications installed.

Query to list all the systems with Program A and Program B, use the following query.
Replace Program A and Program B with your application add remove programs display name.
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_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 like '%Program A%') and 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 like '%Program B%')


SCCM - SQL Report to show Maintance Windows for a Collection of system


Select fcm.Name,fcm.CollectionID as [Member of Collection], sw.Name as 'CEPSWName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsGMT as 'StartTimeIsGMT', sw.IsEnabled as 'CEPSWEnabled',case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
from v_ServiceWindow sw
join v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
where fcm.name in (select  v_FullCollectionMembership.name from  v_FullCollectionMembership where  v_FullCollectionMembership.collectionid=@CollID) and isnull(fcm.IsObsolete, 0)= 0

Create a prompt with variable name COllID by using the following query.

select CollectionID,Name from v_Collection

SCCM SQL Query to list IP Subnets of all the system that are in a collection


select distinct v_r_system.Name0, v_RA_System_IPSubnets.IP_Subnets0 from v_R_System inner join v_FullCollectionMembership on v_FullCollectionMembership.resourceid= v_r_system.resourceID inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID=V_R_System.ResourceID where v_FullCollectionMembership.CollectionID=@CollID
Create a Prompt with the name COLLID using the following query.


select CollectionID,Name from v_Collection
If you want to only see the unique IP subnets, then use the following query.

select distinct v_RA_System_IPSubnets.IP_Subnets0 from v_R_System inner join v_FullCollectionMembership on v_FullCollectionMembership.resourceid= v_r_system.resourceID inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID=V_R_System.ResourceID where v_FullCollectionMembership.CollectionID=@CollID

SCCM Report for 2 softwares registered in Add/Remove Programs

This report creates the output in the following format.



Computer 1Software 1Software 2
Computer 2Software 1Software 2
Computer 3Software 1Software 2
Computer 4Software 1
Computer 5Software 1Software 2
Computer 6Software 1Software 2
Computer 7Software 2
Computer 8Software 1Software 2


Query:

select sys.name0,(select distinct 'Software ARP1' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP1') ),(select distinct 'Software ARP2' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP2') )  from v_r_system sys inner join  v_FullCollectionMembership on sys.ResourceID=v_FullCollectionMembership.ResourceID where  v_FullCollectionMembership.CollectionID=@CollID

Prompt Query: Create a prompt with the variable CollID using the following query.

select CollectionID,Name from v_Collection

Here, replace Software ARP1 and Software ARP2 with your application add/remove programs name.

How to create an ConfigMgr/SCCM reports with prompts?

This is something simple to do and adds a lot more functionality SCCM report. I will walk you through the basics of creating a prompt for a SCCM report.

1) Go to Reporting hive and select Reports
2) Right Click and Reports and select New -> Report



3) Give your report a Name, Category and then click EDIT SQL Statement
4) Paste in your code for your SQL Statement.
 
5) Click on Prompts… button

A new window will appear. Clicking the Add button will bring up the Prompt Properties page. Complete the Name (used as @Name in your report SQL code), Prompt Text (the hint the user will see for the prompt), Default Value and if needs be click the Provide SQL Statement checkbox and provide the appropriate query. If a SQL statement is provided here, then when executing this report the user can click on "Values..." (see the last screenshot) and select a row from the output of this query. SCCM select the value of the first column of the row selcted by user and assignes that value to the variable defined in the Name field.   Your window should look something like this.

Click OK to close the window and repeat step 5 as many times as you need.
You have now successfully added prompts (variable(s)) to your SCCM report.
The following SQL query should list all the systems that are in a selected collection with IE7 installed. While this query is being executed the @Prompt variable will be replaced by the value that the end user provides or select by clicking "Values.." button.

select V_R_SYSTEM.Name0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.InstallDate0 from v_R_System left join v_Add_Remove_Programs on v_Add_Remove_Programs.ResourceID=V_R_SYSTEM.ResourceID left join v_ClientCollectionMembers on v_ClientCollectionMembers.ResourceID=V_R_SYSTEM.ResourceID where v_ClientCollectionMembers.CollectionID= @Prompt and v_Add_Remove_Programs.DisplayName0='Windows Internet Explorer 7'



SCCM SQL Query to list all the systems with their BIOS details.

SELECT sys.Name0, BIOS.SMBIOSBIOSVersion0,BIOS.ReleaseDate0, BIOS.Manufacturer0
FROM v_r_system sys, v_GS_PC_BIOS BIOS
WHERE sys.ResourceID = BIOS.ResourceID

How to create a collection to list all the systems with a particular KB

User the following queries to build the collection to list all the systems with a particular KB.

For finding x86 systems:

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB123456%"
For finding x64 systems:

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_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 like "%KB123456%" 
Replace KB123456 with the KB number of require security update.

How to create a package in ConfigMgr/SCCM?

Even though this article discusses about ConfigMgr/SCCM 2007, but most of the things also apply for SMS 2003.

To create a package, select Software Distribution -> Packages -> New -> Package.

New Package Wizard

Name: Type the name of the package. The length of the package name can be up to 50 characters. This field is a required fiend and can’t be left blank.
Version: This is the version number of the package. Its length can be up to 32 characters.
Manufacturer: The software manufacturer of the package and it length can be up to 32 characters.
Language: The language version of the software and its length can be up to 32 characters
Comment: Optional text about the package and its length can be up to 127 characters.  
This package contains source files” indicates whether the package contains source files. This check box is cleared by default. Check this if the package contains source files. When this check box is selected distribution points must be specified. In some cases the package may not contain any source files and we just want to execute a command line - am example would be running notepad.exe. Notepad.exe would be there on all the windows based system in %windir% folder.  In that case, de-select this option.




Use a compressed copy of the source directory: A compressed version will be decompressed and copied to the distribution points instead of being copied directly from the original source folder when the distribution points are refreshed.
If you specify Update All Distribution Points a new version of compressed copy is created from the specified source folder.


Tip: Use this option if the source files might be removed from the specified path (for example, if the source files are on a CD).
Note: A compressed version of the source folder is always created and used when the package is sent to distribution points in child sites.

Always obtain files from the source directory:

Each time a distribution point is updated, ConfigMgr will obtain the package source files from the path specified in the Source directory box.

Tip: Use this option if the source files are likely to remain on a specified path such as share on a server.

 Persist content in the client cache: Specifies whether package content should be retained in cache on the client computer indefinitely even if it has already been run. This option can be useful with some recurring packages such as virus software and this will reduce the available cache space.
Enable Binary differential replication:
Specifies whether binary delta compression should be used on changed package source files. This behavior can result in large bandwidth saving in transferring the changes for large files, compared with the traditional method where the entire file is transferred.

Note: Binary differential replication may not work properly, if you are using CIFS caching devices.




Access the distribution folder through common ConfigMgr package share:
If this option is selected, the package will be stored in common package share (SMSPKGx$, where x is the drive letter) on the distribution point.
Share the distribution folder:
If this option is selected package will be created on distribution points with an administrator specified share and path name.
Share Name:  Specify the name of the Share. This share name must be unique across all packages. All files and subdirectories within that share will be deleted and re-created each time the package is updated or refreshed. The share name\path must be unique across all packages and only the final directory in the path will be deleted and re-created each time the package is updated or refreshed.
Disconnect users from distribution points:
This setting only applies only to ‘standard distribution points’.
Number of retries before disconnecting users:
·         Number of times that ConfigMgr tires to update the package source files before disconnecting users disconnected to distribution point.
·         The default setting is 2, but we can specify 0 to 99, inclusive.
Interval between user notification and disconnection (Minutes):
·         Number of minutes ConfigMgr will wait after notifying users before disconnecting them from distribution points. The default setting is 5 minutes, but we can specify 0 to 59 minutes, inclusive.

Sending priority:
Specify the priority of this package. This priority setting will be used, when transferring this package to other child sites. The package with high sending priority will be processed first. The packages with identical priorities are sent in the order of their creation.
The three priorities available are:
  • High
  • Medium
  • Low
Medium is the default setting.

Preferred sender:
Select the preferred sender. <No Preference> is the default.
Branch distribution point content settings:

If Automatically download content when package are assigned to branch distribution points  option is selected, then package will be automatically downloaded to all branch distribution points when packages are assigned to branch DP’s.
Make this package available on protected distribution points when requested by client inside the protected boundaries:
If this option is selected, a client request will cause the local BDP to download the package. After content is successfully provisioned on the BDP, a subsequent client request will be able to download and run this content.
Download content on demand is available only for protected BDP’s. If a BDP is configured but not protected the MP will not rigger the package download.
All BDP’s in a give location will retrieve the package when on demand request is made to any of the BDP’s in a give location.
If “administrator must manually copy this package to the branch distribution points” then the package will not downloaded automatically and administrator must manually copy it to the BDP.

If “Use these fields for status MIF matching” is selected, then only the fields specified in this page will be used.

If “Use package properties for status MIF matching” is selected, then only fields on “General” page will be used.
The Security tab defines which users will have access, and what type of access they will have, to the package.
Configure the security permissions for this package and click Next.




Review the package settings and click on Finish -> Close.

All Virtual Computers 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")