Use OMS View Designer for SQL Server monitoring

Summary: Learn how to use OMS View Designer to create a custom SQL Server monitoring solution.

Good morning, everyone. Manjnath Ajjampur is here, and, in my role as a Cloud Infrastructure Architect at Microsoft, I focus on our Hybrid Cloud solutions where I partner with our customers who are implementing Microsoft Operations Management Suite. Recently, one customer wanted to understand how to gain better visibility into their SQL Server environment. Specifically, the customer was interested in the SQL Server jobs that failed or succeeded and who was running those jobs. I decided to use the OMS View Designer to create a solution for them.

Design the solution

So here it is. The OMS home page has a tile for SQL Server Monitoring. It shows the number of SQL Servers and the total number of databases being monitoring.

Drilling down into that tile brings you to the SQL Server Monitoring view.

In this blog post, I will show you how to set up OMS and SQL Server to collect and to process specific Event Log entries to leverage the View Designer capability in OMS.

Set up SQL Server to write Agent job events

1. Ensure that the SQL Server Agent is running on the SQL Server instance whose jobs you want to monitor.

2. Ensure that you edit the job’s properties so that it writes to the Windows Event log. In this example, I have a simple job that creates a disk-based backup of a database named “vFirewall”. This job will fail if the backup location (c:\temp) does not exist.

Optionally, you can have the SQL Server job write its output to a text file. More on that later.

Configure OMS

1. Install and configure the OMS Windows Agent on SQL Server. For setup instructions, see Connect Windows computers to Log Analytics.
2. Deploy the SQL Server Assessment solution for OMS.
3. Deploy the View Designer to your OMS workspace. The View Designer is currently in Preview. To add it, go to Settings > Preview Features, and then enable View Designer. Please read the caveats about deploying Preview Solutions.
4. Create a OMS Computer Group that represents the SQL Servers that I want to monitor. I call mine (very creatively) SQL Servers.

Creating the custom view for SQL Server

Now that you have met all the prerequisites, the next step is to use the View Designer to create the SQL Server Monitoring Overview dashboard. The View Designer allows you to create the Overview tile that is viewed on the OMS Home Page. Drilling into this tile reveals the Monitoring dashboard. It relies on Log Analytics search queries.

The View Designer has two tabs – Tile and View. Both the Tile and the View tabs provides ways  to visualize data in a variety of chart types and formats.

Let’s focus on the Tile tab. There are a variety of data visualization options here. I choose Two Numbers.

The queries that I use to populate this tile depend on the SQL Server Assessment Solution.

Label	Query
Number of SQL Servers	Type=SQLAssessmentRecommendation AffectedObjectType="Microsoft.SQLServer.DBEngine" | measure count() by Computer
Number of databases	Type=SQLAssessmentRecommendation | measure count() by DatabaseName

If you drill into this tile, you see the main SQL Server Monitoring Dashboard. If you click the View tab in the View Designer, you see a canvas where you can choose multiple data visualization options.

Here’s what I use to populate the main dashboard. SQL Server Agent job failures are written to the Windows Application Event log as Event ID 208. Event Level of Warning. job successes are written to the same log with Event ID 208 and Event Level of Information.

Name	Type	Query	Navigation query
SQL Server events	Donut & List	Donut: Type=Event Computer IN $ComputerGroups[SQL Servers] |measure count() by EventLevelName List: Type=Event Computer IN $ComputerGroups[SQL Servers] |measure count() by Computer	{selected item} Type=Event
Event distribution by source	Donut & List	Donut: Type=Event Computer IN $ComputerGroups[SQL Servers] |measure count() by Source List: Type=Event Computer IN $ComputerGroups[SQL Servers] |measure count() by Source	{selected item}
Average CPU performance	Line chart, callout, & list	Line Chart: Type=Perf CounterName="% Processor Time" InstanceName="_Total" Computer IN $ComputerGroups[SQL Servers] | measure avg(CounterValue) by Computer Interval 1HOUR List: Type=Perf CounterName="% Processor Time" InstanceName="_Total" Computer IN $ComputerGroups[SQL Servers] | measure avg(CounterValue) by Computer	{selected item}
Job failures	Donut & List	Donut: Type=Event (EventLog=Application) (EventID=208) (EventLevelName=warning) Computer IN $ComputerGroups[SQL Servers] |measure count() by Computer List: Type=Event (EventLog=Application) (EventID=208) (EventLevelName=warning) Computer IN $ComputerGroups[SQL Servers] |measure count() by Computer	{selected item} Type=Event (EventLog=Application) (EventID=208) (EventLevelName=warning)
Job success	Donut & List	Donut: Type=Event (EventLog=Application) (EventID=208) (EventLevelName=information) Computer IN $ComputerGroups[SQL Servers] |measure count() by Computer List: Type=Event (EventLog=Application) (EventID=208) (EventLevelName=information) Computer IN $ComputerGroups[SQL Servers] |measure count() by Computer	{selected item} Type=Event (EventLog=Application) (EventID=208) (EventLevelName=information)

Additionally, I color-coded the donut sectors to provide a visual indication of Errors and Warnings by using the Advanced Color Mapping functionality for each donut. For example, for the SQL Server Events donut, I used these color mappings.

You should save this dashboard so that the main tile and the underlying dashboard can now become part of your OMS workspace.

That’s it! Now you can start to monitor your SQL Server environment for Agent job successes and failures. When I drill into any job failure, I can see what job failed, at what step, at what time, and who ran the job.

Closing thoughts

• You have just extended the already awesome functionality of Log Analytics in OMS with a custom view that is meaningful to your database team.
• This view relies on the SQL Server Assessment solution. By default, the assessment runs every seven days. This means that you will discover new SQL Servers and databases once every seven days. If you have a System Center Operations Manager environment, you can create an override to the collection rule to have it run on a more frequent basis. Our engineering team is looking into providing this override functionality outside such an environment as well.

I invite you to follow me on Twitter and the Microsoft OMS Facebook site. If you want to learn more about Windows PowerShell, visit the Hey, Scripting Guy Blog.

Get a free Microsoft Operations Management Suite (#MSOMS) subscription so that you can test it out. You can also get a free subscription for Microsoft Azure.

Manjnath Ajjampur
Principal Cloud Infrastructure Architect, Microsoft

Comments

• Anonymous
  July 21, 2016
  Great blogpost ! Thanks for sharing :-)
• Anonymous
  July 22, 2016
  This is great! Thank you!
• Anonymous
  July 22, 2016
  What performance counter should be added to OMS Data collection to get "Avg CPU Performance" in your example ? "Processor(_Total)% Processor Time" or some other.
  • Anonymous
    July 22, 2016
    @Pierre,Yes, an example query is "Type=Perf CounterName="% Processor Time" InstanceName="_Total" Computer IN $ComputerGroups[SQL Servers] | measure avg(CounterValue) by Computer Interval 1HOUR"
• Anonymous
  November 11, 2016
  Is it possible to collect waitstats and visual it in powerbi with OMS?
• Anonymous
  November 16, 2016
  Hi!Great post! In Addition, I am looking for a solution, which shows all SQL Server activities, which have been done by our external Providers. Whenever one of their Team members logs on to my database, I Need to see what he/she is doing in Detail. (Not that I am interested in it, this is a given requirement by our compliance department. Any suggestions?Thanks in advance.C
• Anonymous
  November 22, 2016
  The comment has been removed
  • Anonymous
    November 22, 2016
    just realized that copy and paste form your webside is creating the problems, typing it in manually fixes it.
• Anonymous
  November 23, 2016
  Hi Anyone out there who can tell me how to Analyse SQL Server Events? EventID = 33205. I would like to analyse the column 'EventData' more detailed, meaning I do not want to see all Event Rows but all those with a given text in it. (To compare: Within SQL Server, I am able to use the 'LIKE-' command.ThanksChristian
  • Anonymous
    November 24, 2016
    Christian, maybe you can use custom fields for that? I havn´t tested it, but think it could work.
• Anonymous
  February 27, 2017
  The comment has been removed
• Anonymous
  April 13, 2017
  is this just for azure sql databses or on-prem sql databases as well. We installed the OMS agent on windows servers running SQL but logging appears to be pretty limited. are we missing something?
  • Anonymous
    April 13, 2017
    it seems to be showing zero sql servers as the AffectedObjectType seems to only be Microsoft.Windows.Computer and not "Microsoft.SQLServer.DBEngine”. can anyone tell me if i have something configured improperly?
• Anonymous
  April 24, 2017
  This is great! What query can i use to visualize # of SQL Server versions (or editions) in Donut style? Thanks in advance!
• Anonymous
  May 24, 2017
  Can you export the view and make it available as a download*
• Anonymous
  September 29, 2017
  Thank you! This is exactly what I wanted to share to show how to monitor SQL with OMS (MMS) over at https://sqlcommunity.slack.com. The job logging looks great and I appreciate the work it takes to piece together, having done a little to pick up OpenManage events. Looks like there's a preview Azure SQL specific solution now, too.