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.

Screenshot of the OMS home page and the SQL Server Monitoring tile.

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

Screenshot of 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.

Screenshot of SQL Server Agent in Object Explorer.

  1. 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.

3. Screenshot of the Job Step Properties – Step 1 dialog box

3. Screenshot of the Job Properties – vFirewall Backup dialog box

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

Screenshot of value in “On success action” and “On failure action” fields in the property dialog box.

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.

Screenshot of the Overview page that has the Tile and View tabs.

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.

Screenshot of the View tab.

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.

Screenshot of the Advanced Color Mapping options.

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.

Screenshot of results from the dashboard.

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 (15)

  1. Great blogpost ! Thanks for sharing 🙂

  2. This is great! Thank you!

  3. Pierre Boya says:

    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.

    1. Manjnath Ajjampur says:

      @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"

  4. Magnus says:

    Is it possible to collect waitstats and visual it in powerbi with OMS?

  5. Christian says:

    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

  6. Thomas says:

    Hi, i have tried your setup here and it looks great, but i simply cannot make it work in any way.

    just the simpel query "Type=SQLAssessmentRecommendation AffectedObjectType=”Microsoft.SQLServer.DBEngine” | measure count() by Computer" failes with Invalid syntax. Unexpected 'SQLServer' at position 63. and the sql assesment blade haws been installed. Any idea why?

    1. Thomas says:

      just realized that copy and paste form your webside is creating the problems, typing it in manually fixes it.

  7. Christian says:

    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.
    Thanks
    Christian

    1. Magnus says:

      Christian, maybe you can use custom fields for that? I havn´t tested it, but think it could work.

  8. Robert Caretta says:

    When I attempt to add the information to the Navigation query I get an error stating "Invalid syntax". Has anyone else come across this?

  9. scott says:

    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?

    1. scott says:

      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?

  10. Mark says:

    This is great! What query can i use to visualize # of SQL Server versions (or editions) in Donut style? Thanks in advance!

  11. Marin says:

    Can you export the view and make it available as a download*

Skip to main content