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.

Custom SCCM report to help debug MBAM client rollout

Ground control to Major Tom,

Okay, so this might not be the most common thing to be looking for but anyways…

I've just done a rollout of MBAM (Microsoft Bitlocker Administration and Monitoring, part of MDOP) for a customer and found an interesting scenario:

What is the best way to find out which MBAM clients have not contacted the MBAM server?

(For different reasons some of the clients did not register with the MBAM server, even though the client was successfully installed.)

As the customer in this case of course is using SCCM I created a custom SCCM report using the Report Builder that pulls data from the SCCM database containing computers that have the MBAM agent installed and compare this to the clients that have actually reported to the MBAM database.

This could be helpfull when you are doing a large scale rollout of MBAM and want a some help in pointing out the "problem clients".

You can of course change the "look and feel" of the report in the report builder, but here is an example:

I guess the important thing is the SQL queries I used so here we go:

First of all you need two datasources in Report Builder, one for each databaseserver/database.

I then created 4 datasets/queries.

Dataset_MBAM and Dataset_SCCM lists all MBAM clients registerted in the SCCM and MBAM database.

(For MBAM use the DataSource_MBAM and for SCCM DataSource_SCCM of course...)

Dataset_MBAM:

SELECT

ComplianceCore.MachineCompliance.Name

FROM

  ComplianceCore.MachineCompliance

Dataset_SCCM:

Select v_R_System.Netbios_Name0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID 

WHERE v_Add_Remove_Programs.DisplayName0 like '%MBAM%'

The result from these queries are put into two Report Builder parameters (@FromMBAM and @FromSCCM)

These parameters/variables are then used in the two datasets listing out the diffrences between the SCCM and MBAM databases:

Dataset_NotInMBAM: (Listing all MBAM clients that exists in SCCM but not in MBAM DB)

Select v_R_System.Netbios_Name0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID 

WHERE v_Add_Remove_Programs.DisplayName0 like '%MBAM%' AND v_R_System.Netbios_Name0 NOT IN (@FromMBAM)

Dataset_NotInSCCM: (Listing all MBAM clients that exists in MBAM but not in MBAM DB)

SELECT

ComplianceCore.MachineCompliance.Name

FROM

  ComplianceCore.MachineCompliance

WHERE ComplianceCore.MachineCompliance.Name NOT IN (@FromSCCM)

Deploying and multi-homing SCOM 2012 Agent using SCCM

Hello Ground Control,

Well I have just created my first blog so now I guess I have to write something. Why don't I start by sharing what I've just learned a few days ago and that is how to upgrade your SCOM agent to the new 2012 version using SCCM. 

In my scenario I wanted to upgrade the SCOM agent, multi-homing it to report both to my existing SCOM 2007 environment and to my brand spanking new SCOM 2012 Management Group. I started out expecting this would be an "out-of-the-box"-easy thing to do, but ended up using hours on this simple task.

Fist thing I discovered was that some of the parameters from the SCOM 2007 agent install like "MANAGEMENT_GROUP_OPERATION=AddConfigGroup" doesn't seem to be there in 2012. (http://technet.microsoft.com/en-us/library/hh230736.aspx) Secondly I found out that even tough setting the parameters MANAGEMENT_GROUP and MANAGEMENT_SERVER_DNS pointing to the new 2012 environment when running the unattended agent installation, the agent would still only be reporting to the old SCOM 2007 Management Group after the agent upgrade had finished.

The solution to this was using a VB script to make the agent reporting to the new 2012 Management Group:

---------Script----------

Option Explicit

Dim objMSConfig

Set objMSConfig = CreateObject("AgentConfigManager.MgmtSvcCfg")

Call objMSConfig.AddManagementGroup ("<New 2012 MG Name>", "<New 2012 Management server name>",5723)

So here's what I ended up with in :

1. A Task Sequence to install the agent (separate steps for x86/x64), one step running the script pointing the agent to the new 2012 Management Group and finally installing the Update Rollup 1 to the agent.

2. The "Install SCOM 2012 Agent.." steps in the Task Sequence refers to a package created with the SCOM 2012 agent source files.

3. I added WMI queries to the spesific x86/x86 steps in the task sequence:

(Query x86: select * from Win32_Processor where DeviceID="CPU0" and AddressWidth="86")

(Query x64: select * from Win32_Processor where DeviceID="CPU0" and AddressWidth="64")

3. I put the VB script in with the source files in the SCOM 2012 agent package and run it as a command line in the Task Sequence:

(Command line: %windir%\System32\cscript.exe "setMG2012.vbs")

4. I put the SCOM Update Rollup 1 agent files in a separate package and created "update install programs" on the package for x86/x64. I used the following command line:

(x86 Install program: "msiexec /update KB2674695-i386-Agent.msp /qn /norestart")

(x64 install program: "msiexec /update KB2674695-AMD64-Agent.msp /qn /norestart")

Use the same WMI query condition on the x86/x64 steps of course…

Anyways…

This worked for me.