Used SQL statements
Here you can find the SQL-Statements that are used to fetch the data from you SCCM-Database:
- Resource Type
- User
- User → Computer
- User Groups
- System
- System → SystemRoles
- System → TopConsoleUser
- System → PrimaryUser
- System → Asset Tag & Chassis Type
- System → Site
- Unknown System
- Network Interface
- Operating System
- CPU
- File System
- Application
- Application Service
- File
- Site
- Site → Site Role
- Site Role
- Boundary Group
- Boundary Group → Boundaries
- Boundary
- Distribution Point
- Distribution Point → Distribution Point Group
- Distribution Point Group
- Mobile Device
- Application Package
- Boot Image Package
- Device Setting Package
- Driver Package
- Driver
- Image Package
- Legacy Image Package
- OS Install Package
- Software Updates Package
- VHD Package
Resource Type
SELECT [ResourceType], [DisplayName] FROM [v_ResourceMap]
User
SELECT [SID0], [ResourceID] ,[ResourceType], [Creation_Date0], [User_Name0], [Full_User_Name0], [User_Principal_Name0], [Unique_User_Name0], [AD_Object_Creation_Time0] FROM [v_R_User]
User → Computer
SELECT DISTINCT [v_R_System].[SMS_Unique_Identifier0], [v_GS_USER_PROFILE].[SID0] FROM [v_R_System] INNER JOIN [v_GS_USER_PROFILE] ON [v_R_System].ResourceID = [v_GS_USER_PROFILE].ResourceID
User Groups
SELECT [SID0] ,[ResourceID], [ResourceType], [Creation_Date0], [Usergroup_Name0], [AD_Domain_Name0] FROM [v_R_UserGroup]
System
SELECT [RS].[SMS_Unique_Identifier0], [RS].[Object_GUID0], [RS].[SID0], [RS].[ResourceID], [RS].[ResourceType], [RS].[Creation_Date0], [RS].[Name0], [RS].[Netbios_Name0], [RS].[Resource_Domain_OR_Workgr0], [RS].[Active0], [RS].[Client0], [RS].[Client_Version0], [RS].[Decommissioned0], [RS].[User_Name0], [CS].[Domain0], [CS].[DomainRole0], [CS].[InstallDate0], [CS].[Manufacturer0], [CS].[Model0], [CS].[Roles0], [CS].[Status0], [PCB].[SerialNumber0], [OS].[LastBootUpTime0], [RAM] = (SELECT SUM([Capacity0]) FROM [v_GS_PHYSICAL_MEMORY] WHERE [ResourceID] = [RS].ResourceID) FROM [v_R_System] as [RS] LEFT OUTER JOIN [v_GS_COMPUTER_SYSTEM] as [CS] ON [RS].[ResourceID] = [CS].[ResourceID] LEFT OUTER JOIN [v_GS_PC_BIOS] as [PCB] ON [RS].[ResourceID] = [PCB].[ResourceID] LEFT OUTER JOIN [v_GS_OPERATING_SYSTEM] as [OS] ON [RS].[ResourceID] = [OS].[ResourceID]
System → SystemRoles
SELECT DISTINCT [ResourceID], [System_Roles0] FROM [v_RA_System_SystemRoles]
System → TopConsoleUser
SELECT [CU].[ResourceID] as [SystemResourceID], [USR].[ResourceID] as [UserResourceID] FROM [v_GS_SYSTEM_CONSOLE_USAGE] as [CU] INNER JOIN [v_R_User] as [USR] ON [CU].[TopConsoleUser0] = [USR].[Unique_User_Name0]
System → PrimaryUser
SELECT [USRPM].[MachineID], [USRPM].[UserResourceID] FROM [v_UsersPrimaryMachines] as [USRPM] LEFT JOIN [v_R_User] as [USR] ON [USRPM].[UserResourceID] = [USR].[ResourceID] WHERE [USR].[Name0] IS NOT NULL
System → Asset Tag & Chassis Type
SELECT [ResourceId], [SMBIOSAssetTag0], [ChassisTypes0] from [v_GS_SYSTEM_ENCLOSURE]
System → Site
SELECT [ItemKey], [SMS_Assigned_Sites0] FROM [vSystem_SMS_Assign_ARR]
Unknown System
SELECT [SMS_Unique_Identifier0], [ResourceID], [ResourceType], [Creation_Date0], [Name0], [CPUType0], [SiteCode0], [Decommissioned0] FROM [v_R_UnknownSystem]
Network Interface
SELECT [NIC].[ResourceID], [NIC].[TimeStamp], [NIC].[Name0], [NIC].[DeviceID0], [NIC].[Description0], [NIC].[Manufacturer0], [NIC].[AdapterType0], [NIC].[MACAddress0], [CONF].[DefaultIPGateway0], [CONF].[DHCPEnabled0], [CONF].[DHCPLeaseExpires0], [CONF].[DHCPLeaseObtained0], [CONF].[DHCPServer0], [CONF].[IPEnabled0], [CONF].[IPAddress0], [CONF].[IPFilterSecurityEnabled0], [CONF].[IPPortSecurityEnabled0], [CONF].[IPSubnet0] FROM [v_GS_NETWORK_ADAPTER] as [NIC] LEFT OUTER JOIN [v_GS_NETWORK_ADAPTER_CONFIGURATION] as [CONF] ON [NIC].[ResourceID] = [CONF].[ResourceID] AND [NIC].[DeviceID0] = [CONF].[Index0]
Operating System
SELECT [ResourceID], [TimeStamp], [Caption0], [Manufacturer0], [OSArchitecture0], [Version0], [BuildNumber0], [ServicePackMajorVersion0], [ServicePackMinorVersion0], [SerialNumber0], [SystemDirectory0], [WindowsDirectory0], [LastBootupTime0] FROM [v_GS_OPERATING_SYSTEM]
CPU
SELECT [ResourceID], [TimeStamp], [Name0], [Manufacturer0], [DeviceID0], [NumberOfCores0], [MaxClockSpeed0], [Is64Bit0] FROM [v_GS_PROCESSOR]
File System
SELECT [LD].[ResourceID], [LD].[TimeStamp], [LD].[Description0], [LD].[DeviceID0], [LD].[Size0], [LD].[FreeSpace0], [LD].[Compressed0], [LD].[FileSystem0], [LD].[VolumeSerialNumber0], [EV].ProtectionStatus0 FROM [v_GS_LOGICAL_DISK] as [LD] LEFT OUTER JOIN [v_GS_ENCRYPTABLE_VOLUME] as [EV] ON [LD].[ResourceID] = [EV].[ResourceID] WHERE LD.DriveType0 = '3'
Application
SELECT [ResourceID], [TimeStamp], [DisplayName0], [InstallDate0], [Publisher0], [Version0] FROM [v_GS_ADD_REMOVE_PROGRAMS_64]
SELECT [ResourceID], [TimeStamp], [DisplayName0], [InstallDate0], [Publisher0], [Version0] FROM [v_GS_ADD_REMOVE_PROGRAMS]
SELECT [ResourceID] ,[TimeStamp], [NormalizedName] AS [DisplayName0], [InstallDate0], [NormalizedPublisher] AS [Publisher0], [NormalizedVersion] AS [Version0], [InstalledLocation0] FROM [v_GS_INSTALLED_SOFTWARE_CATEGORIZED]
Application Service
SELECT [ResourceID], [TimeStamp], [DisplayName0], [Description0], [ServiceType0], [StartMode0], [Started0], [StartName0], [PathName0] FROM [v_GS_SERVICE]
File
SELECT [ResourceID], [ProductId], [FileID], [FileName], [FileDescription], [FileVersion], [FilePath], [FileSize], [FileModifiedDate] FROM [v_GS_SoftwareFile]
Site
SELECT [SiteCode], [SiteName], [ReportingSiteCode], [Version], [BuildNumber], [ServerName], [InstallDir], [Type] FROM [v_SiteAndSubsites]
Site → Site Role
SELECT DISTINCT [Role], [SiteCode] FROM [v_SiteSystemSummarizer]
Site Role
SELECT DISTINCT [System_Roles0] FROM [v_RA_System_SystemRoles]
Boundary Group
SELECT [CreatedOn], [GroupID], [GroupGUID], [Name], [Description], [DefaultSiteCode], [CreatedBy], [ModifiedBy], [ModifiedOn] FROM [vSMS_BoundaryGroup]
Boundary Group → Boundaries
SELECT DISTINCT [GroupID], [BoundaryID] FROM [vSMS_BoundaryGroupMembers]
Boundary
SELECT [CreatedOn], [BoundaryID], [DisplayName], [Value], [CreatedBy], [ModifiedBy], [ModifiedOn], [BoundaryType] FROM [vSMS_Boundary]
Distribution Point
SELECT [IdentityGUID], [Name], [ShareName], [Drive], [NALPath], [SiteCode], [Description], [Version], [IsPeerDP], [IsPullDP], [BitsEnabled], [IsMulticast], [IsProtected], [PreStagingAllowed], [ResourceType], [SiteName], [OperatingSystem], [Communication], [GroupCount], [HasRelationship], [InternetFacing], [HealthCheckEnabled] FROM [v_DistributionPointInfo]
Distribution Point → Distribution Point Group
SELECT [GroupID], [DPNALPath] FROM [v_DPGroupMembers]
Distribution Point Group
SELECT [GroupID], [Name], [Description], [SourceSite], [HasMember], [HasRelationship], [CreatedOn], [ModifiedOn] FROM [vSMS_DistributionPointGroup]
Mobile Device
SELECT [DVC].[ResourceID], [DVC].[GroupID], [DVC].[RevisionID], [DVC].[AgentID], [DVC].[TimeStamp], [DVC].[CellularTechnology0], [DVC].[DeviceClientID0], [DVC].[DeviceManufacturer0], [DVC].[DeviceModel0], [DVC].[DMVersion0], [DVC].[FirmwareVersion0], [DVC].[HardwareVersion0], [DVC].[IMEI0], [DVC].[IMSI0], [DVC].[IsActivationLockEnabled0], [DVC].[Jailbroken0], [DVC].[MEID0], [DVC].[OEM0], [DVC].[PhoneNumber0], [DVC].[PlatformType0], [DVC].[ProcessorArchitecture0], [DVC].[ProcessorLevel0] , [DVC].[ProcessorRevision0], [DVC].[Product0], [DVC].[ProductVersion0], [DVC].[SerialNumber0], [DVC].[SoftwareVersion0], [DVC].[SubscriberCarrierNetwork0], [SYS].Name0, [SYS].SID0, [SYS].SMS_Unique_Identifier0, [SYS].Object_GUID0, [SYS].[ResourceType] FROM [v_GS_DEVICE_COMPUTERSYSTEM] AS [DVC] INNER JOIN [v_R_System] AS [SYS] ON [DVC].[ResourceID] = [SYS].[ResourceID]
Application Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize], (SELECT TOP 1 [ContentSource] FROM [vSMS_Content] WHERE [vSMS_Content].[PkgID] = [vSMS_ContentPackage_List].[PkgID] ORDER BY [Content_ID] DESC) as [PkgSourcePath] FROM [vSMS_ContentPackage_List]
Boot Image Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize], [ImageOSVersion], [ProductionClientVersion] FROM [vSMS_BootImagePackage_List]
Device Setting Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize] FROM [vSMS_DeviceSettingPackage_List]
Driver Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize] FROM [vSMS_DriverPackage_List]
Driver
SELECT [DRV].[CI_ID], (SELECT TOP 1 [DM].[ModelName] FROM [vSMS_DriverModel] as [DM] WHERE [DM].[CI_ID] = [DRV].[CI_ID] ORDER BY [DM].[ModelName]) as [Name], [DRV].[DriverType], [DRV].[DriverINFFile], [DRV].[DriverDate], [DRV].[DriverVersion], [DRV].[DriverClass], [DRV].[DriverProvider], [DRV].[DriverSigned], [DRV].[DriverSigner], [DRV].[DriverBootCritical] FROM [v_CI_DriversCIs] as [DRV]
Image Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize], [ImageOSVersion] FROM [vSMS_ImagePackage_List]
Legacy Image Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize] FROM [vSMS_LegacyImagePackage_List]
OS Install Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize], [ImageOSVersion] FROM [vSMS_ImagePackage_List]
Software Updates Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize] FROM [vSMS_SoftwareUpdatesPackage_List]
VHD Package
SELECT [PkgID], [SourceDate], [Name], [Description], [Version], [Language], [Manufacturer], [PreDownloadRule], [StoredPkgPath], [Source], [SourceSite], [RefreshSchedule], [ShareName], [PreferredAddress], [LastRefresh], [UseForcedDisconnect], [ForcedRetryDelay], [ForcedRetryDelay], [DisconnectDelay], [IgnoreSchedule], [SourceSize] FROM [vSMS_VhdPackage_List]
Last modified on Mar 7, 2025
Powered by Confluence and Scroll Viewport.