SQL Report Builder and System Center Custom Reports

Hi again,

Thought I'd give another example of what you could do with Reporting Services/Report Builder and System Center. Here I have created a report and called it "Server Lookup Details" where the idea was to get info on a server from the different data sources a particular customer was using (SCCM/FEP/OpsMgr) and putting it into one report.

This is an example of what the report might look like:

All data in this report except "FEP" and "Alerts Operations Manager" comes from SCCM.

General: General computer info from SCCM like Machine Name, Domain, Last loged on user, OS version, HW Model, etc.

Computer Discovery info: Info on last Heartbeat, AD discover, etc.

To get more info you click the "+" next to "Creation Date":

Client Health: Requires that you have installed SCCM client monitoring. Will then display messages like in the picture below.

Agent Scan Cycle: Gives last HW/SW and Scan (Software Updates) interval on the agent.

Software Updates: Gives status on the updates/patches installed, etc. on the server.

For more info you click the "+" next to i.e. "Security Updates"

Collection Membership: Well… gives you just that. Collection membership for the machine in SCCM

Advertisement Status (Machine): Tells you status of machine based advertisement towards the server:

 

(Linked to standard SCCM report for detailed error messages by clicking the "Advertisement Name")

(For desktops/laptops I also created an "Advertisement Status (User)" field in my "Client Lookup Report" where you would get the advertisements that was targeted the user, based on the AD security groups that the specific user was member of. This was a bit more tricky to do, and I ended up creating a new SQL view on the SCCM Database. For servers it makes little sense to have "User targeted" advertisements so I kept it out of this report.)

Disk: Tells you the disks of the server. (DeviceID, Interface Type, Manucaturer, etc…)

FEP: Fetches info from the FEP database (This is a SCCM 2007/FEP 2010 installation) about Signature version and last update time:

Add/Remove Programs content / Products: Displays data from the HW / SW inventory agent in SCCM about products installed on the server. (Againg you click on the "+" next to the servername to list the products…)

And finally:

Alerts Operations Manager: Here I connect to the SCOM database and checks for any open alerts on the server.

If there seem to be any interest around this topic I could do another blog on the actual SQL queries and how I created the report.