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