Understanding SCCM SQL Views

-
The Configuration Manager 2012 / Current Branch come with many built in reports which are sufficient for day to day requirements. However, you still have to deal with SQL query to pull custom details directly from SCCM database based on requirement. As an SCCM administrator you must have good understanding of SQL query in order order to pull these information from SCCM database directly. One of the key requirement here is to have understanding of SCCM DB schema. Once you know which information are stored in which SQL view, you can easily write a SQL query to pull out information. 

Know SCCM SQL Views:

The SCCM SQL View  v_SchemaViews contain the details of all views.  You can use below SQL query to get a list of all views.

Select * from v_SchemaViews


Since, SCCM creates one separate view for each collection, the list will be quite long. You can filter the result by excluding Collection views from query.

Select * from v_SchemaViews where Type <> 'Collection'


Details of SCCM Views and Their Naming Conventions:

These views can be grouped into following.

Resource Views:

Resource views represent discovery data, which include any resources that were discovers on the network. These views name start with V_R.

The four main discovery views are ,  v_R_System for system resources, v_R_User for user resources, v_R_UserGroup for user group resources, and v_R_UnknownSystem for unknown systems.

Resource Array Views:

Resource Array views contain data where there can be more than one value for a resource, such as IP Address or User Organizational Unit (OU) name. The name of most of these views start with V_RA.

Examples:

V_RA_System_IPAddresess : List the IP addresses of discovered system resource
V_RA_System_SystemGroupName : List all system resources which are in an associated Active Directory group.

You can find more details about Discovery Views (Resource & Resource Array) here.


Inventory Views:

Inventory views contain current inventory data for hardware,  software ,clients, files, products, and so forth in the Configuration Manager hierarchy. These views are named as V_GS.

Select * from v_SchemaViews where type = 'inventory'




Inventory History Views:

Inventory History views contain inventory history data for hardware,  software ,clients, files, products, and so forth in the Configuration Manager hierarchy. These views are named as V_HS_.


Other Views


V_Collection : The V_Collection SQL view display the properties of site's collections.

v_Package :  The v_Package SQL view display the properties of site's packages.

v_Advertisements: The v_Advertisements SQL view display the properties of Advertisements.

v_Reports: Display the properties of Reports


You can find complete details of all views in Configuration Manager 2012 here. 

Comments

Post a Comment

Popular posts from this blog

SCCM Software Distribution Troubleshooting

-
Image
1.    Content Monitoring and Validation from Console The below article provide details of content monitoring and validation from console. https://blogs.technet.microsoft.com/inside_osd/2011/04/06/configuration-manager-2012-content-monitoring-and-validation/ 2.    Reports SCCM reports should be the first thing to check the software deployment status. By looking into the report you can determine the scope of failure such as large scale failure, location based failure or client specific issues. A large scale failure may be caused by an issue with package or programe while issue can be related with specific client if deployment failure rate is low and limited to few clients. There are multiple reports available for each category.  All Software Distribution reports can be found in below folders under Monitoring > Reporting > reports. ·          Software Distribution – Collections · ...
Read more

Powershell - List AD Organizational Unit and GPOs linked to them

-
The below script will search Active Directory for all Organizational Unit which contain specific name and list them along with all Group Policies linked to those OUs. Example: The below command will get a list of all OUs which name contains 'Test'. It will also show the details of all GPOs linked to OUs. Usage Example: .\Get-OUList.ps1 –OUName “Test” #Script [ CmdletBinding () ] Param (   [ Parameter (Mandatory = $True ) ]   [ string ] $OUName   ) $invocation = ( Get-Variable MyInvocation ) . Value $directorypath = Split-Path $invocation . MyCommand . Path $outputfile = $directorypath + "\Result.csv" $OUName = "*" + $OUName + "*" $Results = @() $OUList = Get-ADOrganizationalUnit -Filter * | Where-Object -FilterScript { $PSItem . distinguishedname -like $OUName } foreach ( $OU in $OUList ){     $LinkedGPOs = Get-ADOrganizationalUnit -Identity $OU | select -Expa...
Read more

Troubleshooting System Center Endpoint Protection (SCEP) Client

-
Image
Microsoft System Center Endpoint Protection provides an antimalware and security solution for the Microsoft platform. This can be integrated with System Center Configuration Manager. I have consolidated the information which are useful for SCEP client side troubleshooting. 1. Managing Endpoint Protection client with Command Line Interface – MpCmdRun.exe The MpCmdRun.exe (Microsoft Antimalware service command line utility) is very useful tool to manage local Endpoint protection client with command line.  The MpCmdRun.exe is located in C:\Program Files\Microsoft Security Client folder. On Windows 10 machines, the MpCmdRun.exe is available in C:\Program Files\Windows Defender folder. When executed, this command automatically creates a log file named as MpCmdRun.log in C:\Users\ username\AppData\Local\Temp folder. This log file records the commands that are executed using MpCmdRun.exe. Few of most commonly used command lines are listed below. For a complete l list of av...
Read more