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
12 Comments
Pingback: SCCM Configmgr SQL views – SCCM not so common issues
Good analysis - I loved the points - Does someone know where my business can acquire a fillable a form version to fill out ?
What are you expecting from this?
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.
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.
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.
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.
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.
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
Pingback: SQL query to obtain list of SCCM SQL views for report building. | System Center Guru
Good one..., thanks
Great one eshwar...........
Thanks to you and Russ Rimmerman