{"id":470,"date":"2010-06-04T10:53:57","date_gmt":"2010-06-04T08:53:57","guid":{"rendered":"http:\/\/code.openark.org\/forge\/?page_id=470"},"modified":"2010-11-08T12:37:24","modified_gmt":"2010-11-08T10:37:24","slug":"custom-monitoring","status":"publish","type":"page","link":"https:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/custom-monitoring","title":{"rendered":"Custom monitoring"},"content":{"rendered":"<p>As of revision <strong>132<\/strong>, <em>mycheckpoint<\/em> allows for custom monitoring. It provides:<\/p>\n<ul>\n<li>User defined queries (under certain restrictions), which are executed by <em>mycheckpoint<\/em>.<\/li>\n<li>Charts based on said custom queries.<\/li>\n<li>HTML reports for custom queries, via <strong>sv_custom_html_brief <\/strong>and<strong> <\/strong><strong>sv_custom_html<\/strong>.<\/li>\n<li>Alerts for custom query results.<\/li>\n<\/ul>\n<p><em>mycheckpoint<\/em> stored the results of custom queries in the same <strong>status_variables<\/strong> table, used to store all monitored data. Therefore, charting and alerting comes out of the box for custom queries. However, <em>mycheckpoint<\/em> does specifically store and provide:<\/p>\n<ul>\n<li>Custom query result&#8217;s value (stored).<\/li>\n<li>Custom query execution time (stored).<\/li>\n<li>Custom query result&#8217;s rate (derived by views).<\/li>\n<\/ul>\n<p><em>mycheckpoint<\/em> executes all custom queries right after it completes its standard parameters, status variables, replication data &amp; OS data collection.<\/p>\n<h4>Creating custom queries<\/h4>\n<p>Custom queries are stored in the <strong>custom_query<\/strong> table. To add custom queries, <strong>INSERT<\/strong> rows into that table, as in the following example:<\/p>\n<blockquote>\n<pre>INSERT INTO\r\n custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)\r\n VALUES (0, 1, 'SELECT COUNT(*) FROM store.shopping_cart WHERE is_pending=1', 'Number of pending carts', 'value', 0);\r\n\r\nINSERT INTO\r\n custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)\r\n VALUES (1, 1, 'SELECT MAX(shopping_cart_id) FROM store.shopping_cart', 'Created carts rate', 'value_psec', 0);\r\n<\/pre>\n<\/blockquote>\n<p>The columns of the <strong>custom_query<\/strong> table are:<\/p>\n<ul>\n<li><strong>custom_query_id<\/strong>: Unique identifier. This is <em>not<\/em> an AUTO_INCREMENT: you choose the id; you&#8217;ll reference it later on.<\/li>\n<li><strong>enabled<\/strong>: <strong>1<\/strong> for enabled, <strong>0<\/strong> for disabled (query will not get executed), value stored is <strong>NULL<\/strong>.<\/li>\n<li><strong>query_eval<\/strong>: The query to be executed. All tables must be fully qualified with database (Schema) scope. The query must return <em>exactly<\/em> one row, with <em>exactly<\/em> one column, which is a type of INTEGER.<\/li>\n<li><strong>description<\/strong>: A human readable explanation of the nature of the query. Used as title for custom charts.<\/li>\n<li><strong>chart_type<\/strong>: How to graphically represent the custom results. This is a type of <strong>enum(&#8216;value&#8217;,&#8217;value_psec&#8217;,&#8217;time&#8217;)<\/strong>.\n<ul>\n<li><strong>&#8216;value&#8217;<\/strong> means charting the query result&#8217;s value;<\/li>\n<li><strong>&#8216;value_psec&#8217;<\/strong> charts the change per second of the value;<\/li>\n<li><strong>&#8216;time&#8217;<\/strong> charts the time it took to execute the query (regardless of the result).<\/li>\n<\/ul>\n<\/li>\n<li><strong>chart_order<\/strong>: chart position within the HTML reports (works as of revision <strong>160<\/strong>).<\/li>\n<\/ul>\n<h4>Custom query results storage<\/h4>\n<p>The results are stored within the <strong>status_variables<\/strong> table. Each custom query has two columns in this table:<\/p>\n<ul>\n<li><strong>custom_X<\/strong>: value of the query result<\/li>\n<li><strong>custom_X_time<\/strong>: time it took to execute the query<\/li>\n<\/ul>\n<p>Where &#8216;X&#8217; stands for the <strong>custom_query_id<\/strong>.<\/p>\n<p>The <strong>status_variables<\/strong> table is in turn used by the various <em>mycheckpoint<\/em> views. Therefore, an additional column is gained, called <strong>custom_X_psec<\/strong> (just as with all other columns). For example:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT id, ts, created_tmp_tables_psec, custom_0, custom_1_psec FROM sv_sample WHERE ts &gt;= NOW() - INTERVAL 1 HOUR;\r\n+-------+---------------------+-------------------------+----------+---------------+\r\n| id\u00a0\u00a0\u00a0 | ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | created_tmp_tables_psec | custom_0 | custom_1_psec |\r\n+-------+---------------------+-------------------------+----------+---------------+\r\n| 50730 | 2010-05-21 19:05:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16.64 |\u00a0\u00a0\u00a0\u00a0\u00a0 448 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3.02 |\r\n| 50731 | 2010-05-21 19:10:02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20.97 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 89 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.73 |\r\n| 50732 | 2010-05-21 19:15:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15.70 |\u00a0\u00a0\u00a0\u00a0\u00a0 367 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3.56 |\r\n| 50733 | 2010-05-21 19:20:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.32 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 54 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.43 |\r\n| 50734 | 2010-05-21 19:25:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16.42 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 91 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.96 |\r\n| 50735 | 2010-05-21 19:30:02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21.93 |\u00a0\u00a0\u00a0\u00a0\u00a0 233 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2.11 |\r\n| 50736 | 2010-05-21 19:35:02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 14.58 |\u00a0\u00a0\u00a0\u00a0\u00a0 176 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.91 |\r\n| 50737 | 2010-05-21 19:40:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21.61 |\u00a0\u00a0\u00a0\u00a0\u00a0 168 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.93 |\r\n| 50738 | 2010-05-21 19:45:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16.05 |\u00a0\u00a0\u00a0\u00a0\u00a0 241 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2.44 |\r\n| 50739 | 2010-05-21 19:50:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19.70 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 46 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.19 |\r\n| 50740 | 2010-05-21 19:55:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15.85 |\u00a0\u00a0\u00a0\u00a0\u00a0 177 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2.28 |\r\n| 50741 | 2010-05-21 20:00:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19.04 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.82 |\r\n+-------+---------------------+-------------------------+----------+---------------+<\/pre>\n<\/blockquote>\n<h4>Charting<\/h4>\n<p>Since custom values are stored in the <strong>status_variables<\/strong> table, charting works for custom values just as it works for normal variables:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT custom_1_psec FROM sv_report_chart_sample\\G\r\n\r\n<img loading=\"lazy\" decoding=\"async\" title=\"custom_1_psec\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chts=303030,12&amp;chtt=Latest+24+hours:+May+19,+20:10++-++May+20,+20:10&amp;chf=c,s,ffffff&amp;chdl=custom_1_psec&amp;chdlp=b&amp;chco=ff8c00&amp;chd=s:QfXQmZQhXTmWVkWRobPpWUtQPVROaOOUMJPOKdJHQJFJEDJJEGCAIEFJHFFEGGDQHGJGMJPPMNZNRWR_ZUWfR_nSjuUcaXa3OgxRl4UivWZ5UhtWX4VgnUTYktiVW9WanUVxVYlgXwVdicXpb&amp;chxt=x,y&amp;chxr=1,0,5.120000&amp;chxl=0:||+||00:00||+||04:00||+||08:00||+||12:00||+||16:00||+||20:00|&amp;chxs=0,505050,10,0,lt&amp;chg=4.17,25,1,2,3.47,0&amp;chxp=0,3.47,7.64,11.81,15.98,20.15,24.32,28.49,32.66,36.83,41.00,45.17,49.34,53.51,57.68,61.85,66.02,70.19,74.36,78.53,82.70,86.87,91.04,95.21,99.38\" alt=\"\" width=\"400\" height=\"200\" \/><\/pre>\n<\/blockquote>\n<h4>HTML reports<\/h4>\n<p>In the above chart the title is fuzzy, and this is due to the nature of <em>mycheckpoint<\/em> being mostly ignorant of the data contained. However, <em>mycheckpoint<\/em> provides with special HTML report views to visualize custom query charts. The HTML reports bautify the charts, making them more verbose. HTML reports come in two flavors:<\/p>\n<ul>\n<li>Brief reports, featuring last 24 hours, as in the example above. These are handled by the <strong>sv_custom_html_brief<\/strong> view.<\/li>\n<li>Full reports, featuring last 24 hours, last 10 days, known history. These take longer to generate, and are handled by the <strong>sv_custom_html<\/strong> view.<\/li>\n<\/ul>\n<p>See this <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/sv_custom_html_brief\">sample HTML report<\/a>. It was generated by issuing:<\/p>\n<blockquote>\n<pre>SELECT html FROM sv_custom_html_brief;<\/pre>\n<\/blockquote>\n<h4>Alerts<\/h4>\n<p>Since custom query results are stored just as all other parameters, defining and generating alert conditions for custom values is as with normal variables. For example:<\/p>\n<blockquote>\n<pre>INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)\r\n  VALUES ('<strong>custom_1_psec = 0<\/strong>', 'No new shopping carts detected in last 10 minutes', 10);<\/pre>\n<\/blockquote>\n<p>See the <a href=\"http:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/alerts\">Alerts<\/a> page for more on alert conditions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As of revision 132, mycheckpoint allows for custom monitoring. It provides: User defined queries (under certain restrictions), which are executed by mycheckpoint. Charts based on said custom queries. HTML reports for custom queries, via sv_custom_html_brief and sv_custom_html. Alerts for custom query results. mycheckpoint stored the results of custom queries in the same status_variables table, used [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":147,"menu_order":15,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-470","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":35,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/470\/revisions"}],"predecessor-version":[{"id":528,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/470\/revisions\/528"}],"up":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/147"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/media?parent=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}