{"id":1346,"date":"2010-02-23T17:12:47","date_gmt":"2010-02-23T15:12:47","guid":{"rendered":"http:\/\/contoso.se\/blog\/?p=1346"},"modified":"2010-02-23T17:13:25","modified_gmt":"2010-02-23T15:13:25","slug":"query-a-database-with-a-monitor-part-two","status":"publish","type":"post","link":"http:\/\/contoso.se\/blog\/?p=1346","title":{"rendered":"Query a database with a monitor &#8211; part two"},"content":{"rendered":"<p>In <a href=\"http:\/\/contoso.se\/blog\/?p=310\">this<\/a> post I wrote about a script that you can use to query a databas. The script in this post\u00c2\u00a0will count number of\u00c2\u00a0fields, if there are more then four, status of the monitor will be changed. It will also include number of rows in the alert description. I use the following settings on the monitor, a scripting\/timed script two state monitor. In the following example I have a database on my root management server. The database is named TechDaysdb and includes a table named OpsMgrV. The name of the root management server is mobile-opsmgr.<\/p>\n<ul>\n<li>General\n<ul>\n<li>Name: Contoso &#8211; query db monitor<\/li>\n<li>Monitor Target: for Root Management Server<\/li>\n<li>Management Pack: TechDays demo<\/li>\n<\/ul>\n<\/li>\n<li>Schedule\n<ul>\n<li>Run every 2 Minutes<\/li>\n<\/ul>\n<\/li>\n<li>Script\n<ul>\n<li>File Name: TechDaysmonitor.vbs<\/li>\n<li>Script: see below<\/li>\n<\/ul>\n<\/li>\n<li>Unhealthy Expression\n<ul>\n<li>Property[@Name=&#8217;Status&#8217;] does not contain Ok<\/li>\n<\/ul>\n<\/li>\n<li>Healthy Expression\n<ul>\n<li>Property[@Name=&#8217;Status&#8217;]\u00c2\u00a0contain Ok<\/li>\n<\/ul>\n<\/li>\n<li>Health\n<ul>\n<li>Healthy Healthy Healthy<\/li>\n<li>Unhealthy Unhealthy Warning<\/li>\n<\/ul>\n<\/li>\n<li>Alerting\n<ul>\n<li>check Generate alerts fort this monitor<\/li>\n<li>check Automatically resolve the alert when the monitor returns to a healthy state<\/li>\n<li>Alert Name: TechDays &#8211; db monitor<\/li>\n<li>Alert Description: There are to many rows in the database. There are $Date\/Context\/Property[@Name=&#8217;Rows&#8217;]$ rows.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>\u00c2\u00a0<\/p>\n<div><span style=\"font-size: x-small;\"><\/span><\/div>\n<p><span style=\"font-size: x-small;\"><\/p>\n<blockquote><p>Const adOpenStatic = 3<\/p>\n<p>Const adLockOptimistic = 3<br \/>\nSet oAPI = CreateObject(&#8220;MOM.ScriptAPI&#8221;)<br \/>\nSet oBag = oAPI.CreatePropertyBag()<br \/>\nSet objConnection = CreateObject(&#8220;ADODB.Connection&#8221;)<br \/>\nSet objRecordSet = CreateObject(&#8220;ADODB.Recordset&#8221;)<br \/>\nobjConnection.Open _<br \/>\n&#8220;Provider=SQLOLEDB;Data Source=mobile-opsmgr;&#8221; &amp; _<br \/>\n&#8220;Trusted_Connection=Yes;Initial Catalog=TechDaysdb;&#8221; &amp; _<br \/>\n&#8220;User ID=domain\\username;Password=password;&#8221;<br \/>\nobjRecordSet.Open &#8220;SELECT * FROM OpsMgrV&#8221;, _<br \/>\nobjConnection, adOpenStatic, adLockOptimistic<br \/>\nvarNo = objRecordSet.RecordCount<br \/>\nIf varNO &gt; 4 Then<br \/>\nRows = varNo<br \/>\n   Call oBag.AddValue(&#8220;Status&#8221;,&#8221;Error&#8221;)<br \/>\n   Call oBag.AddValue(&#8220;Rows&#8221;,Rows)<br \/>\n   Call oAPI.Return(oBag)<br \/>\nElse<br \/>\n   Call oBag.AddValue(&#8220;Status&#8221;,&#8221;Ok&#8221;)<br \/>\n   Call oAPI.Return(oBag)<br \/>\nEnd If<\/p><\/blockquote>\n<p><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post I wrote about a script that you can use to query a databas. The script in this post\u00c2\u00a0will count number of\u00c2\u00a0fields, if there are more then four, status of the monitor will be changed. It will also include number of rows in the alert description. I use the following settings on the &hellip; <a href=\"http:\/\/contoso.se\/blog\/?p=1346\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[19],"tags":[],"_links":{"self":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1346"}],"collection":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1346"}],"version-history":[{"count":8,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1346\/revisions"}],"predecessor-version":[{"id":1354,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1346\/revisions\/1354"}],"wp:attachment":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1346"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}