Datazen – Dashboard Initial Configuration

Now that we have the product installed, the next step is to determine what data needs to be displayed.  Since I am going to be consuming SCOM data, I am going to create a simple dashboard that contains information that is pertinent to the health of the SCOM environment itself.  In order to accomplish this, the next steps are to grant access to the appropriate users, create a hub, create a data source and then use that data source to create views into the data.  It sounds like a lot, but it is really not bad at all.

For my DZ install, I created a new user named DZAuthor.  This is the account I am going to use to do the bulk of the authoring work.  I already had a SCOMUser account in my lab, so I am going to grant that user access to the dashboard I am creating in order to simulate my end user experience.


The DZ install has a couple of URLs you need to access.

http://<servername> and http://<servername>/cp.  The first is the end user page (where the dashboards and KPIs will be displayed) and the second one is the control panel (cp for short).  The control panel is where we need to go to get started.  I open the CP URL and then enter the admin account and password (specified during install) in order to get logged in the first time.

Once I get logged in, the first step is to grant access to my DZAuthor account.  This is the account that I am going to use for creating the dashboards and all of the supporting components.  On the home tab in the CP portal I hit create user.  As you can see here, there is also a batch import process here that allows you to create users in bulk as well.


After I hit Create User, I fill in the details for the DZAuthor account.  Since I specified Active Directory settings during the install, the necessary information is pretty minimal.


Now that I have created my authoring user, I now need to create a hub.  A hub is basically a container for dashboards.  Users are assigned permissions at the hub level.  Once I have my dashboard created, I should be able to grant access to the hub SCOMUser account and that user should be able to then view the contents of the hub.


I hit Create BI Hub and then create a hub named SCOM Health Hub.  This is where the dashboards pertaining to the health of the SCOM infrastructure itself are going to be stored.


I give it a max of 10 users.  For lab, this should be fine.  For prod, you would need to make sure and check scaling for the IIS servers you have hosting these sites.  Once the hub is created, I logoff the Admin account and login with my DZAuthor account.

Once I am logged back in as DZAuthor, I need to make sure the SCOM Health Hub is selected in my navigation pane.  Since this is the first and only hub in the environment, this is the case:


The next step is to create a User Group for this hub.  I hit User Groups in the navigation pane and then hit the “Create New User Group” button at the top


and then:


I create a new group for the SCOM Health Hub


By default, DZAuthor is automatically dropped into this group since DZAuthor is the hub owner.  We will come back later and add in the SCOMUser account in order to grant permissions to the published dashboards.

Speaking of dashboards (the reason we are here), the next step is to create a group for the dashboards.


I navigate to Dashboards and then hit “Create a New Group” at the top.


I’m going to create a group for the core infrastructure.  Later on, I will circle back and create a group that specifically pertains to agent health.  In step 2, I select “Allow Access” for the recently created hub user group:


That’s more or less the framework.  Now comes the work that pertains directly to what we want to display on the screen.  In order to get at any data, we need to create a Data Source.  I navigate to Data Sources:


and then hit “New Folder” at the top:


I give it a generic name for holding all of my SCOM information.  I click on the folder that gets created in order to drop in a level, and then I hit “New Data Connection” button at the top:


At this time, there are 13 different types of data providers available.  These range all the way from Azure SQL Databases to Excel spreadsheets.  For connecting to SCOM, we’re going to be querying the SQL databases.  For what I want to accomplish, I am going to be querying the OperationsManager database directly and going around the SDK.  Is this supported?  Nope.  Do I care?  Nope.  Should you care?  Great question.  The database layout for the bulk of the data we would want to dashboard from the OpsDB hasn’t really changed that much (or at all) from the first days of SCOM 2007 through the most recent UR for SCOM 2012 R2.  Is this supported?  Nope.  Does it work?  Yep.

The next step is to grant access to the data source:


At this point, the screen should look something like this:


Now, we click into OperationsManager in order to start mining some data.  Once inside, we hit “New Data View” button:


Since we are under the OperationsManager data source we created, these views are all going to be query based and will be driven off the OperationsManager database.  Now, we need to figure out how to get at the data we need.  A great place to always start is Kevin Holman’s “Useful Operations Manager 2007 SQL queries” blog post when querying either the OperationsManager of OperationsManagerDW databases.  Another option is to actually find the data you want in the SCOM console and use SQL Profiler to see what query SCOM is executing in order to retrieve the data for the SDK.

View #1:

Number of Alerts per day

SELECT Convert(DateTime,CONVERT(VARCHAR(20), TimeAdded, 102)) AS DayAdded, COUNT(*) AS NumAlertsPerDay
WHERE TimeRaised is not NULL
Having Convert(DateTime,CONVERT(VARCHAR(20), TimeAdded, 102)) > DATEADD(d,-7,GetUTCDate())

I stole this one from Kevin’s blog and then tweaked it a bit for the dashboard.  First, note the datetime column returned from the query needs to contain an actual datatime value from SQL or Datazen will not treat it as a datetime.  I got stuck here for a bit since the data looks good.  In Kevin’s query, the datetime had actually been converted to a varchar so DZ was just treating that column as a normal string. In order to deal with this and keep it simple, I just wrapped the varchar field with an additional Convert() function and set it back to a datetime.

Additionally, I removed the row with the total and only returned the previous 7 days.  I am happy with the results from the query in SQL Management Studio, so I move back to the Control Panel and fill in the rest of the data for the Alerts/Day view:


Notice I set the Refresh Frequency to every 30 minutes.  You may want to set this more or less frequent depending on your environment and business needs.  There are a couple of other interesting things on this screen such as parameterizing filters and personalizing the query by adding in the username to the query.  I don’t need these for this dashboard, but they are options to explore later.

I repeat the steps 3 more times:

View #2 – Events Per Day

SELECT Convert(DateTime,CONVERT(VARCHAR(20), TimeAdded, 102)) AS DayAdded,
COUNT(*) AS EventsPerDay
FROM EventAllView
Having Convert(DateTime,CONVERT(VARCHAR(20), TimeAdded, 102)) > DATEADD(d,-7,GetUTCDate())

View #3 – Performance Samples Per Day

SELECT Convert(DateTime,CONVERT(VARCHAR(20), TimeSampled, 102)) AS DaySampled, COUNT(*) AS PerfInsertPerDay
FROM PerformanceDataAllView with (NOLOCK)
Having Convert(DateTime,CONVERT(VARCHAR(20), TimeSampled, 102)) > DATEADD(d,-7,GetUTCDate())

View #4 – State Changes Per Day

SELECT Convert(DateTime,CONVERT(VARCHAR(20), TimeGenerated, 102)) AS DayGenerated, COUNT(*) AS StateChangesPerDay
FROM StateChangeEvent WITH (NOLOCK)
Having Convert(DateTime,CONVERT(VARCHAR(20), TimeGenerated, 102)) > DATEADD(d,-7,GetUTCDate())
ORDER BY DayGenerated DESC

After following the same logic for each of the views, my Control Panel now shows the 4 views I created:


At this point, I am ready to create an actual dashboard and publish it!

The next post will focus on utilizing these views in a simple dashboard using the Datazen Publisher app available from the Windows Store.

Presentation, SC Operations Manager

Datazen – Lab Installation

Per my previous post, MS has acquired a BI solution capable of providing dashboards using many different types of data sources. From a System Center perspective, we have been waiting for a scalable solution that meets the performance needs of our customers. Seeing this solution has a very nice look and feel, I was interested in getting my hands on the software to take it for a test drive.


Reading through the documentation, I noticed that there are many options for scaling this product for a large scale customer. For lab, however, a single server scenario seems like the way to go. Just to make sure this would perform at least OK, I spun up a Basic_A3 VM in Azure (4 cores, 7GB of RAM) and downloaded the product and kicked off the install.



Since this a single server install, I leave all of the features selected. I do, however, not know how the product will react to the disk caching on the C drive. I decide to leave it as the C drive for lab purposes but would expect to move this off to a different drive if I was doing a production install.


After reading a little bit about security in from the included PDF, I decide to go with a domain account to run the core services. This is how you would approach the situation if this was a distributed deployment and better follows security best practices.


The Control Panel for the application (administrative web portal) has a default admin user. The user name is “admin” and here I set the password for that account.


I want to integrate with AD. I configure this connection to leverage the same service account I created and set to run the core services. In a production install, you would most likely use different accounts.


I copy off the encryption key and store it in a safe place.


I copy off the instance ID just in case I decide to add additional servers to the overall install in the future.


I choose to “Use Core Service credentials” in the next step for simplicity.


I want this server to host the websites so I leave the host name field blank.


I configure the Exchange settings so that the service can send emails.




At this point, the install takes quite a while (10-15 minutes) as the necessary windows features are added to the machine and product is ultimately installed.


Happy dashboarding! The next post will cover getting started with getting a hub set up, creating a data source, and building some views in order to populate the dashboards.


Dashboards–MS Acquires BI Dashboard Solution

Dashboards!  Cloud ready, mobile ready and free for a bunch of customers.  Read more about it here:

Microsoft acquires mobile business intelligence leader Datazen

As of today, SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the Datazen Server software at no additional cost.

From the perspective of Datazen:

Download here:

Publisher Application available via the Windows Store:

Happy dashboarding!

Presentation, SC Operations Manager, SC Orchestrator, SC Service Manager

SCOM vNext CTP – Module Changes

I recently installed the CTP for Operations Manager that was released in October on MSDN.  Using the same method I used to scan the R2 product for changes, I was able to find just a few nuggets of info.  This method simply looks at the out of the box management packs, snags all of the module types and looks at their definitions.  I then do a simple compare between the outputs (CTP vs. 2012 R2 UR4) to see if there are any new MPs, new modules, or changes to the parameters on existing modules.

Script for scanning: Here

The volume of changes were minimal and only seemed to apply to xPlat.  There were no new management packs, and there did not seem to be any changes to the parameters on existing modules (I was hoping for another change like was done to the ExpressionFilter).

Here are the net new changes:

MP: Microsoft.Unix.Library
Microsoft.Unix.WSMan.TimedEnumerate.LogicalDisk.DiscoveryData ? DataSourceModuleType
“TargetSystem” “Uri” “Filter” “SplitItems” “Interval” “SyncTime” “ExcludeFileSystemName” “ExcludeFileSystemType” “ClassId” “InstanceSettings” “OutputErrorIfAny”

MP: Microsoft.Unix.LogFile.Library
Microsoft.Unix.Invoke.Script.ProbeAction ? ProbeActionModuleType
“TargetSystem” “UserName” “Password” “Script” “ScriptArgs” “TimeOut” “TimeOutInMS”

Microsoft.Unix.Invoke.Script.DataSource ? DataSourceModuleType
“Interval” “SyncTime” “TargetSystem” “UserName” “Password” “Script” “ScriptArgs” “TimeOut” “TimeOutInMS” “FilterExpression”

With the CTP, there are two new DataSources and one new ProbeAction out of the box.  The CTP was purely the base install with no additional MPs imported.  There could be other changes or net new MPs/Modules that provide new functionality.  This is most likely the case as we start looking forward along with the integration out to Azure Operational Insights.

Management Packs, MP Authoring, SC Operations Manager, Uncategorized

SCOM 2012–Connect to the SDK from PowerShell in an MP

I’ve had a few different scenarios in the past where the need to connect to the SCOM SDK and retrieve data either as part of a monitor or as part of a discovery is necessary.  In most cases, the standard “Import-Module OperationsManager” gets the job done, the SCOM CmdLets are accessible, and whatever workflow I have executing seems to function just fine.  However, recently, I had a customer scenario where this wasn’t working.  The discovery was running on a SCOM management server.  The module was available and functioning outside of a SCOM MP workflow.  For some reason, the Import-Module simply wasn’t working in that specific environment when the PS code was embedded into a management pack.

Troubleshooting wasn’t getting us anywhere so I scanned the existing MPs to see if there was another way that other MPs were loading the OperationsManager module.  I found a few different ways, however, inside of the “Microsoft SystemCenter OperationsManager Summary Dashboard” management pack a connection to the SDK is made like this:

$SCOMPowerShellKey =
“HKLM:\SOFTWARE\Microsoft\System Center Operations Manager\12\Setup\Powershell\V2”
$SCOMModulePath =
Join-Path (Get-ItemProperty $SCOMPowerShellKey).InstallDirectory “OperationsManager”
Import-module $SCOMModulePath

While we didn’t nail down the root cause of the Import-Module failing, this route was successful in connecting to the SDK.

Management Packs, MP Authoring, PowerShell, SC Operations Manager