As we all know there are tons of reports readily available in SCCM for Administrators or stakeholders to refer with. All these reports are driven using SQL views encapsulated with tabular functions in SQL. Recently I stepped on to a situation where I have to visualize the same SCCM data from a Hadoop base. Business demands are always like that. Though we have a ever updating SQL SCCM db readily available, they prefer not to touch the LIVE data, but use a non-transactional db like Hadoop which can hold the data and we generate report from it. Sync between Hadoop and SQL is administrator’s job, and I leave that for later (BTW, Sqoop is the best tool to do that) .
So inorder to show the data from Hadoop, we have to first move the data from SQL, right ? Rather than how to move the data, here the big question was what all from SQL is to be moved to Hadoop. For that, from customer requirements, we came to know its specifically some 20+ SCCM reports they want to play around. So, its time to identify where these reports gets data from.
Nowhere in online (till date), I could see a concrete reference/dictionary that says this SCCM report corresponds to this many views. As SCCM is a highly normalized db model and it gets evolved in every release, maintaining such a document for hundreds of views in every release is not feasible (even for Microsoft it seems). So its up to us, to go deep dive and found it. For that, you need to have
- A running SCCM server (check Configuration Manager console for error-free running of SCCM)
- SSRS DB installed and configured (with at least read only rights to your user)
- Reporting service running under services
- Reporting enabled in SCCM. Ref this to enable reporting
- SQL Report Builder installed, so as to look into queries behind SCCM report
Once you have the above pre-requisites met, Just go into the identified SCCM report, right click it and press EDIT
This will open the report in Report Builder where we are going to find the query which this report is depending upon. Every report can have one or more dataset its connected with. So we have to find the dataset, then query behind each. Refer below image on finding the dataset and its query against.
In SCCM’s case most of the reports are getting data from a tabular valued function. SO once you get that table valued function, Go to SSMS (SQL Management Studio), find this function under the SCCM database (Programmability -> Functions -> Table-valued functions). Right click that function and click “View dependencies”. Check the radio button “Objects on which <your function> depends”. Viola, you got all tables and views that is used to fetch data to SCCM report. These are the views/tables you need to import to Hadoop.
The logic that displays the SCCM report is, Come on…. You know where to look for 🙂 My intention here was to just show you how to identify the views/table connected with any SCCM report.
Best in online I could get such views list was based on report categories, not on exact report names. That’s here from Microsoft