Export List of SCCM Global Conditions using SQL Query and Admin Console

Let’s try to Export List of SCCM Global Conditions using SQL Query and the Admin Console. You can export SCCM global condition details using either SQL query or Admin console. I recommend using the Admin console because that is the easiest method.

You can use SQL query and SSMS (SQL Server Management Studio) to export the list of SCCM global conditions to get more details. I don’t think using SQL query is the fully supported scenario. So this is just an option shown for educational purposes.

There are two types of Global Conditions, and those are custom and read-only. The Global Conditions are the set of rules created to check the business or technical conditions. These rules can specify how an application is provided and deployed to Windows 10 or Windows 11 devices.

Patch My PC

You can create Custom Global Rules from the SCCM admin console. We already have a step-by-step guide to explain the process of the Custom Global Rule creation process. The following sections provide two options for the Export List of SCCM Global Conditions.

SQL Query Vs Admin Console

Let’s compare and understand which is the best method for you. I have created a table to make this decision easy. I prefer the SQL method if you need to get all those details for the SCCM Global Conditions.

You can export a list of SCCM global conditions using SQL and Console. If you use the export option from the console, you won’t get the heading of each column in the Global Conditions report.

If you want to have an end-to-end report or list of all the SCCM global conditions with headings, then better to use the SQL query method explained below. Depending on your requirement, you can use the following options to Export List of SCCM Global Conditions.

SQLConsole
CI_IDName
Locale IDDevice Type
DataTypeType
Source SiteRead-Only
GlobalConditionTypeIn Use
Condition In UseCI Unique ID
IsReadOnlyDescription
DisplayName
Description
CI_ID
CI_UniqueID
PlatformType
ModelName
Export List of SCCM Global Conditions using SQL Query and Admin Console

Export SCCM Global Conditions Details using Admin Console

Let’s check the option to export the list of SCCM Global Conditions using the admin console. You can skip over to the SQL query section if you want more details and the list of Global Conditions.

First, you need to select all the columns from the console Global Conditions node to get the full details during the export process. Follow the steps to select all the columns available. All these steps help you to Export List of SCCM Global Conditions.

  • Navigate to \Software Library\Overview\Application Management\Global Conditions.
  • Right-Click on any one of the column tab and select all the available columns as shown in the screenshot below.
Export List of SCCM Global Conditions using SQL Query and Admin Console
Export List of SCCM Global Conditions using SQL Query and Admin Console

You need to remember that CTRL + A to select all the Global Conditions from the list works fine in the ConfigMgr Admin console. You need to select the right pane in the console before pressing CRTL + A. You need to choose one SUG and use the up or down arrow to expand the selection of SUGs. Here is your opportunity to Export List of SCCM Global Conditions using the admin console.

  • You need to select the right pane in the console before pressing CRTL + A.
  • Select all objects by using CTRL + A button.
  • Select All the conditions that you want to export.
  • Press CTRL + C buttons on the keyboard to copy all the SCCM Global Conditions from the admin console to the clipboard.

NOTE! CTRL + C won’t copy the column headings from the Global Conditions node to the clipboard. So you will need to type the headings manually, or you can use SQL query to get the heading as explained in the next section of the post.

Export List of SCCM Global Conditions using SQL Query and Admin Console
Export List of SCCM Global Conditions using SQL Query and Admin Console

The following table gives you the list of all the read-only SCCM Global Conditions with the description of each rule. There are 23 global conditions available as read-only. Let’s check Export List of SCCM Global Conditions.

NameDevice TypeTypeData TypeRead OnlyIn UseCI UniqueDescription
Active Directory siteWindowsDeviceStringYesNoGLOBAL/ADSITEDetermines the Active Directory site of a Windows device.
Screen resolutionMobileDeviceIntegerYesNoGLOBAL/Device_ScreenResolutionDetermines the minimum screen resolution of a mobile device
Primary deviceWindowsUserBooleanYesNoGLOBAL/PrimaryDeviceDetermines if the Windows device is a primary device of the logged-on user
OwnershipWindowsDeviceStringYesNoGLOBAL/Device_OwnershipDesktopDetermines the ownership of a device
OwnershipMobileDeviceStringYesNoGLOBAL/Device_OwnershipMobileDetermines the ownership of a device
Organizational unit (OU)WindowsDeviceStringYesNoGLOBAL/MACHINEOUDetermines the Active Directory organizational unit (OU) of a Windows device
Operating system languageWindowsDeviceIntegerYesNoGLOBAL/OSLanguageDetermines the operating system language used by a Windows device
Operating system languageMobileDeviceStringYesNoGLOBAL/Device_OSLanguageDetermines the operating system language used by a mobile device.
Operating systemWindowsDeviceOperating SystemYesYesGLOBAL/OperatingSystemDetermines the operating system used by a Windows device
Operating systemMobileDeviceOperating SystemYesNoGLOBAL/Device_OperatingSystemDetermines the operating system used by a mobile device
Number of processorsWindowsDeviceIntegerYesNoGLOBAL/NumberOfProcessorsDetermines the number of physical processors occupying a socket on a Windows device
Minimum operating system versionMobileDeviceVersionYesNoGLOBAL/Device_MinOSVersionDetermines the minimum operating system version of a mobile device
Intune O365 ProPlus managementWindowsDeviceBooleanYesYesGLOBAL/Applicability_Settings_Device_IsO365IntuneManagedDetermines if Office 365 is managed by Intune on the device
Input typeMobileDeviceBooleanYesNoGLOBAL/Device_TouchTypeDetermines whether the mobile device uses a touch screen for input
Disk spaceWindowsDeviceIntegerYesNoGLOBAL/FreeDiskSpaceDetermines the free disk space on a Windows device
Device typeWindows MobileDeviceStringYesNoGLOBAL/WM_PlatformDetermines the mobile device family type
CPU speedWindowsDeviceIntegerYesNoGLOBAL/CPUSpeedDetermines the processor speed of a Windows device.
Configuration Manager siteWindowsDeviceStringYesNoGLOBAL/SCCMSITEDetermines the assigned Configuration Manager site of a Windows device
Co-managed deviceWindowsDeviceBooleanYesNoGLOBAL/Applicability_Settings_Device_IsComanagedDetermines if the device is Co-managed with Intune and SCCM
Available internal storageMobileDeviceFloating pointYesNoGLOBAL/Device_FreePhoneMemoryDetermines the free storage of a mobile device.
Total physical memoryWindowsDeviceIntegerYesNoGLOBAL/TotalPhysicalMemoryDetermines the total physical memory of a Windows device
Windows Store Global ConditionWindowsDeviceIntegerYesNoGLOBAL/WindowsStoreEnabledWindows Store Global Condition
Export List of SCCM Global Conditions using SQL Query and Admin Console

SQL Query to Export the List of SCCM Global Conditions

You can use the following SQL query from SSMS to export the list of SCCM Global Conditions. The SQL query gives you extra details, as explained in the above section of this post. Let’s check how to Export List of SCCM Global Conditions using SQL query.

NOTE! – You have to test the following query in a LAB or STAGING environment before trying it out in a production environment. The purpose of this query is only for the educational scenarios.

I don’t think Microsoft support using SQL functions in the custom SQL query of Custom SCCM reports. So this post is just for educational purposes. This is the way to Export List of SCCM Global Conditions.

  • Open the SQL Server Management Studio (aka SSMS).
  • Connect your Database Engine.
  • Right Click on your database CM_XXX and click on ‘New Query’
  • Copy the following SQL query to find the report All Global Conditions.
  • Click on the Execute button.
select  all SMS_GlobalCondition.CI_ID,SMS_GlobalCondition.LocaleID,SMS_GlobalCondition.DataType,SMS_GlobalCondition.SourceSite,SMS_GlobalCondition.GlobalConditionType,SMS_GlobalCondition.ConditionInUse,SMS_GlobalCondition.IsReadOnly,SMS_GlobalCondition.DisplayName,SMS_GlobalCondition.Description,SMS_GlobalCondition.CI_ID,SMS_GlobalCondition.CI_UniqueID,SMS_GlobalCondition.PlatformType,SMS_GlobalCondition.ModelName 
  from fn_ListGlobalConditions_List(1033) AS SMS_GlobalCondition

Depending on the DB size and the performance, the SQL query might take time to display results. In my lab environment, it took 00:00:00 time (I don’t know what does that even mean).

Export List of SCCM Global Conditions using SQL Query and Admin Console 1
Export List of SCCM Global Conditions using SQL Query and Admin Console

The results are displayed in the format below. These are the global condition rules available in the SCCM environment (including both custom and read-only conditions).

SQL Query to Export the List of SCCM Global Conditions
SQL Query to Export the List of SCCM Global Conditions – Export List of SCCM Global Conditions

This is the way to Export List of SCCM Global Conditions from SQL Server Management Studio.

  • Right click on the any one of the Columns from the Result pane on SSMS.
  • Click on Select All option to select all the columns and rows.
  • Click on Copy with Headers option to copy all the columns and rows with heading to clipboard.
  • Or The BEST option – You can use Save Results As option to save the result as CSV file.
SQL Query to Export the List of SCCM Global Conditions
SQL Query to Export the List of SCCM Global Conditions – Export List of SCCM Global Conditions

The following is the trimmed version of the SQL report. I have deleted some of the rows and columns from the below table so that they can fit on this page. The following tables give you Export List of SCCM Global Conditions.

Data TypeDisplay NameDescriptionCI_UniqueIDModel Name
OperatingSystemOperating systemDetermines the operating system used by a Windows deviceGLOBAL/OperatingSystemGLOBAL/OperatingSystem
Int64Total physical memoryDetermines the total physical memory of a Windows deviceGLOBAL/TotalPhysicalMemoryGLOBAL/TotalPhysicalMemory
StringOwnershipDetermines the ownership of a deviceGLOBAL/Device_OwnershipDesktopGLOBAL/Device_OwnershipDesktop
Int64Number of processorsDetermines the number of physical processors occupying a socket on a Windows deviceGLOBAL/NumberOfProcessorsGLOBAL/NumberOfProcessors
Int64Operating system languageDetermines the operating system language used by a Windows deviceGLOBAL/OSLanguageGLOBAL/OSLanguage
Int64CPU speedDetermines the processor speed of a Windows device.GLOBAL/CPUSpeedGLOBAL/CPUSpeed
StringOrganizational unit (OU)Determines the Active Directory organizational unit (OU) of a Windows deviceGLOBAL/MACHINEOUGLOBAL/MachineOU
StringActive Directory siteDetermines the Active Directory site of a Windows device.GLOBAL/ADSITEGLOBAL/ADSite
StringConfiguration Manager siteDetermines the assigned Configuration Manager site of a Windows deviceGLOBAL/SCCMSITEGLOBAL/SCCMSite
BooleanPrimary deviceDetermines if the Windows device is a primary device of the logged-on user. Export List of SCCM Global Conditions.GLOBAL/PrimaryDeviceGLOBAL/PrimaryDevice
Int64Disk spaceDetermines the free disk space on a Windows deviceGLOBAL/FreeDiskSpaceGLOBAL/FreeDiskSpace
StringOS Build NumberOS Build NumberGLOBAL/Platform_SettingsGLOBAL/Platform_Settings
Int64Windows Store Global ConditionWindows Store Global ConditionGLOBAL/WindowsStoreEnabledGLOBAL/WindowsStoreEnabled
BooleanCo-managed deviceDetermines if the device is Co-managed with Intune and SCCMGLOBAL/Applicability_Settings_Device_IsComanagedGLOBAL/Applicability_Settings_Device_IsComanaged
BooleanIntune O365 ProPlus managementDetermines if Office 365 is managed by Intune on the deviceGLOBAL/Applicability_Settings_Device_IsO365IntuneManagedGLOBAL/Applicability_Settings_Device_IsO365IntuneManaged
StringOS VersionOS VersionGLOBAL/WM_OSVersionGLOBAL/WM_OSVersion
StringOS VersionOS VersionGLOBAL/Symbian_OSVersionGLOBAL/Symbian_OSVersion
StringOS VersionOS VersionGLOBAL/DevicePlatformSettingsGLOBAL/DevicePlatformSettings
DoubleAvailable internal storageDetermines the free storage of a mobile device.GLOBAL/Device_FreePhoneMemoryGLOBAL/Device_FreePhoneMemory
Int64Minimum API LevelDetermines the minimum API Level of a mobile deviceGLOBAL/Device_MinAPILevelGLOBAL/Device_MinAPILevel
VersionMinimum operating system versionDetermines the minimum operating system version of a mobile deviceGLOBAL/Device_MinOSVersionGLOBAL/Device_MinOSVersion
StringOwnershipDetermines the ownership of a deviceGLOBAL/Device_OwnershipMobileGLOBAL/Device_OwnershipMobile
StringOperating system languageDetermines the operating system language used by a mobile device.GLOBAL/Device_OSLanguageGLOBAL/Device_OSLanguage
StringDevice typeDetermines the mobile device family typeGLOBAL/WM_PlatformGLOBAL/WM_Platform
OperatingSystemOperating systemDetermines the operating system used by a mobile deviceGLOBAL/Device_OperatingSystemGLOBAL/Device_OperatingSystem
BooleanInput typeDetermines whether the mobile device uses a touch screen for inputGLOBAL/Device_TouchTypeGLOBAL/Device_TouchType
Int64Screen resolutionDetermines the minimum screen resolution of a mobile deviceGLOBAL/Device_ScreenResolutionGLOBAL/Device_ScreenResolution
Export List of SCCM Global Conditions using SQL Query and Admin Console

Author

Anoop is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.