SCCM Database ERD
You should provide an entity relationship diagram (ERD) of the SCCM database. I realize all over your website you give the usual feedback about the views being sufficient for reporting, and that we should use the console, etc. However, this is complete nonsense in the age of big data. Database analysts like myself need to harvest data for custom use beyond typical reporting and console options. Not having an ERD means reverse engineering the database. This becomes very time consuming as we must attempt to figure it out for ourselves, then perform data validation to make sure we figured it out correctly. This is a pretty basic ask, and I do not understand why it cannot be provided.
With the exception of documented views, via the SMS Provider, or through the Data Warehouse feature, the database is not an extensibility point for the product.
The database itself is meant to be a black box and we don’t support any changes made to it outside of the product itself. Underlying tables, stored procedures, triggers, and functions can and do change from release to release.
With that said, I’d like to flip this request around. What scenarios are you trying to accomplish today that you cannot do with existing and documented functionality? We could look into ways to better enable these through supported channels.
Understood -- but everything you need should be available through documented views (v_* or v*).
If you're having to go directly to tables to get what you need, that's something we may be interested in addressing. Do you have any specific instances where what you need is only available by going directly to tables versus using views?
Sean Tressler commented
We have many client/server platforms in our environment: SCCM; Symantec Endpoint Protection (SEP); and others. All of these have a database backend into which a client based agent records data. Most of these are security-related, even SCCM which is critical for pushing out MS security updates. We harvest this data to paint a picture of security compliance in our environment. Compliance for a given platform could be something like: is the agent installed; is the agent functional (has it reported to the server in the last 30 days); are other aspects of the agent compliant (are virus definitions current in the case of SEP). This data is cross-checked against other internal tools that generate their own data; like how often a system was on the network in the last 30 days. Such internal tools allow us to set reasonable expectations of client compliance from third party tools like SCCM and SEP based upon network connectivity and other factors.
All of the results from all of the different platforms are warehoused to not only produce a compliance percentage per platform, but also a compliance percentage for all systems across all platforms. This is only one example of how we use a data rich platform like SCCM in conjunction with other data intensive platforms. Limiting the results to reporting tools is just one more spoke in the wheel we do not want or need. It is one more thing that can break when it is more reliable to pull the data straight from source.
At the end of the day, I will get the data I need out of the database by figuring it out on my own sans ERD. I have yet to meet a database yet where I could not do it. That said, it would be much easier for your clients - considering they pay for this product - to be given what they need to perform such data extractions rather than taking a lot of time to figure it out on their own or jump through the hoops of a reporting tool.