Download SCCM Configmgr 2012 SQL Views Documentation

Posted on April 6, 2013 by | 12 Comments | 7,349 Views

Configuration Manager Queries SQL Server views in the Configmgr site database when retrieving the information displayed in reports .Configmgr database contains large information about computer,User related resource and other components as well.

If you are looking for some particular information while creating reports and if you are not sure which table the information has,you will stuck there else you will have guess the table/view to get what is desired.

SQL views contain the information you need for your reports to make reporting easier.

I cont find any document or related resources for SCCM 2012 SQL views .

Russ from Microsoft replied on the forums to get SQL views in SCCM 2012.

Here is the SQL query to get list of views with its columns Available in Configuration Manager 2012 Database.

Select distinct table_name,COLUMN_NAME From Information_Schema.columns

where table_name LIKE ('v_%')

Order by table_name

Download the excel sheet exported with Pivot Table from here

Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/en-us/download/details.aspx?id=22052

Related Posts

12 Responses to "Download SCCM Configmgr 2012 SQL Views Documentation"

  2. lonny coop · Edit

    Good analysis - I loved the points - Does someone know where my business can acquire a fillable a form version to fill out ?

    Reply
  3. Pawan · Edit

    Hello Eswar,

    Could you please help me with some document that will help me what these views signify.

    I mean to understand the database better and prepare better reports.

    Reply
    1. Pawan · Edit

      Could you please help me with some document that will help me understand what these views signify.

      I mean so that i can understand the database better and prepare better reports.

      Reply
      1. Eswar Koneti · Edit

        All the names are self explanatory .for ex: V_R_system view contains information about computer,IP address,AD site,User name etc. What are you expecting more on the document ? This sheet will just help you to identity the information available in what view nothing more.

        Reply
    2. Eswar Koneti · Edit

      All the names are self explanatory .for ex: V_R_system view contains information about computer,IP address,AD site,User name etc. What are you expecting more on the document ? This sheet will just help you to identity the information available in what view nothing more.

      Reply
      1. David Park · Edit

        I can see how from someone with experience in this, the names and fields are self explanatory. However from the perspective of someone who does not have experience in this, the names are not self explanatory at all. It would be helpful to have a naming convention along with a roadmap to what the data is in the fields.

        Taking from your example: V_R_system, what does this mean? I can assume (we all know what happens when we assume) this may stand for virtual remote system, which I do not think it does. I just do not know and I cannot find any documentation which spells this out.

        Then you state the V_R_system view has IP address information. Which field is this? Looking through the list there is not a field which has IP Address as a title.

        Another example, v_PackagesStatusRootSummarizer, what does this mean? In fact what does the 'v' mean period? What information can I expect to find under this table?

        This is the break down that is really needed. If you can point me to a resource which has those clarifications, that would be extremely helpful.

        I appreciate any assistance you can give.

        Reply
        1. Eswar Koneti · Edit

          Hi David,
          I can understand where you are coming from. There are primarily 3 things in the SQL Views to note. Anything that starts with v_r_,comes from AD discovery methods. anything that starts with v_gs_ comes from inventory and v_hs_ is history which is again discovery and inventory based on the view (R or GS).
          coming to the 2nd question ,V_R_system view has IP address information ? If you look at the excel ,look for column name ,search with ipaddress,you will find few SQL views that are from v_gs_,v_hs_ etc which can help you to join with other views.
          I dont see any detail document for each sql view with its description and you need to understand with its name or by looking at what information does it store init.

          Thanks,
          Eswar

          Reply

Post Comment