{"id":431,"date":"2019-10-04T05:06:00","date_gmt":"2019-10-03T16:06:00","guid":{"rendered":"https:\/\/blog.wiseowls.co.nz\/?p=431"},"modified":"2026-03-08T00:49:18","modified_gmt":"2026-03-07T11:49:18","slug":"monitoring-sql-server-fiddling-with-sp_whoisactive-output","status":"publish","type":"post","link":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/","title":{"rendered":"Monitoring SQL Server: fiddling with sp_whoisactive output"},"content":{"rendered":"

Building up on from basic telegraf inputs<\/a> we now want some actual SQL data. And this time around it’s going to be not just numeric telemetry, we’d gain some insights into actual queries that get run.<\/p>\n

Our choice of Elastic<\/h2>\n

Up to this point our choice of data back-end has been unjustified. Telegraf is intended to work with time series DBs<\/a> like Influx. These are heavily optimised based on assumption what kind of data they store and query (i.e. timed series of events). Elastic is however a full text search. So it’s about the time we started to use it for what it was designed for, right?<\/p>\n

Finding active sessions with queries and wait reasons<\/h2>\n

Taking a step back, we must acknowledge work of Adam Machanic<\/a> and call out sp_whoisactive<\/a> as probably the most useful tool every DBA should be aware of. This script provides great overview of what the system is doing right now, how long the session have been running for and what they are waiting on. It even allows for dumping data into predefined tables out of the box! Again, if we were looking to utilise SEL server for monitoring we could have easily gone this way and with a bit of SQL Agent scheduling we’d be done in no time.<\/p>\n

\"sp_whoisactive<\/figure>\n

There’s one slight issue with sp_whoisactive<\/code> – it’s not meant to work with anything else but SQL table as data store. For our case it is not ideal . It also does not really know anything about SQL Server Query Store<\/a> which we think is a nice to have.<\/p>\n

It’s a known fact we can’t join results of a stored proc with other tables unless we store results in an interim temp table first. So we’d use sp_whoisactive<\/code>‘s own temp table scripting facility to store results while we loop through query stores on all our databases to grab some extra bits of data we think might be useful:<\/p>\n

DECLARE @table_schema NVARCHAR(MAX);\nDECLARE @temp_table NVARCHAR(MAX);\nDECLARE @result_table NVARCHAR(MAX);\nSET NOCOUNT ON;\n\/*EXEC sp_WhoIsActive @get_outer_command = 1,\n\t\t\t\t\t@find_block_leaders = 1,\n\t\t\t\t\t@return_schema = 1,\n\t\t\t\t\t@format_output = 0,\n\t\t\t\t\t@schema = @table_schema OUTPUT;*\/\nSET @table_schema = N'CREATE TABLE <table_name> ( [session_id] smallint NOT NULL,[sql_text] nvarchar(max) NULL,[sql_command] nvarchar(max) NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] int NULL,[tempdb_allocations] bigint NULL,[tempdb_current] bigint NULL,[blocking_session_id] smallint NULL,[blocked_session_count] smallint NULL,[reads] bigint NULL,[writes] bigint NULL,[physical_reads] bigint NULL,[used_memory] bigint NOT NULL,[status] varchar(30) NOT NULL,[open_tran_count] smallint NULL,[percent_complete] real NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)';\nSET @temp_table = REPLACE(@table_schema, '<table_name>', '#whoisactive_monitoring');\nSET @result_table = REPLACE(@table_schema, '<table_name>', '#result');\nSET @result_table = REPLACE(@result_table, 'NOT NULL)', 'NOT NULL,[duration] bigint NOT NULL DEFAULT 0)'); -- add placeholder for duration column \nSET @result_table = REPLACE(@result_table, 'DEFAULT 0)', 'DEFAULT 0,[query_text_id] bigint NULL)'); -- add placeholder for query_text_id from Query Store\n---\nDECLARE @db_name NVARCHAR(120);\nDECLARE @enhance_sql NVARCHAR(MAX);\n\nSET NOCOUNT ON\n-- here we just build a list of databases on the server using known table Users as an anchor. Your databases will have other well known tables\nDECLARE @dbs table ([db_name] sysname)\nDECLARE @db_query_sql nvarchar(4000)\nSET @db_query_sql='select ''?'' as [db_name] from [?].sys.tables t WHERE t.name = ''Users'''; \nINSERT INTO @dbs ([db_name]) EXEC sp_msforeachdb @db_query_sql\nSET NOCOUNT OFF\n\nDECLARE db_name_cursor CURSOR FOR\nSELECT [db_name]\nFROM @dbs\nFOR READ ONLY;\nOPEN db_name_cursor;\nFETCH NEXT FROM db_name_cursor\nINTO @db_name;\nWHILE @@FETCH_STATUS = 0\nBEGIN\n\tSET @enhance_sql\n\t\t= CONCAT(\n\t\t\t\t\t@enhance_sql,\n\t\t\t\t\tCHAR(13),\n\t\t\t\t\tCHAR(10),\n\t\t\t\t\tCAST(N'USE [' + @db_name + ']\n\t\t\t\t\t\tIF EXISTS(SELECT 1 FROM sys.database_query_store_options WHERE actual_state_desc <> ''OFF'')\n\t\t\t\t\t\tUPDATE #result \n\t\t\t\t\t\tSET query_text_id = qsqt.query_text_id \n\t\t\t\t\t\tFROM #result wm \n\t\t\t\t\t\tLEFT JOIN sys.query_store_query_text qsqt ON wm.sql_text COLLATE DATABASE_DEFAULT = qsqt.query_sql_text COLLATE DATABASE_DEFAULT \n\t\t\t\t\t\tWHERE wm.database_name = ''' + @db_name + ''';' AS NVARCHAR(MAX))\n\t\t\t\t);\n\tFETCH NEXT FROM db_name_cursor\n\tINTO @db_name;\nEND;\nCLOSE db_name_cursor;\nDEALLOCATE db_name_cursor;\n---\nDECLARE @main_script NVARCHAR(MAX);\nSET @main_script\n\t= CAST(CONCAT(\n\t\t\t\t\t\t@temp_table, N';',\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\t'USE [master]; EXEC [master].[dbo].sp_WhoIsActive @get_outer_command=1, @find_block_leaders=1,@format_output=0, @destination_table=''#whoisactive_monitoring'';',\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\t@result_table, ';',\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\tN'INSERT INTO #result SELECT\twm.*, \n\t\t\t\t\t\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN DATEDIFF(hour, wm.start_time, wm.collection_time) > 576 THEN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tDATEDIFF(second, wm.collection_time, wm.start_time)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE DATEDIFF(ms, wm.start_time, wm.collection_time)\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND AS duration, \n\t\t\t\t\t\t\t\t\t\t\t\t\tNULL \n\t\t\t\t\t\t\t\t\t\t\tFROM #whoisactive_monitoring wm;',\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\t@enhance_sql,\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\tN'DROP TABLE #whoisactive_monitoring;',\n\t\t\t\t\t\tCHAR(13),\n\t\t\t\t\t\tCHAR(10),\n\t\t\t\t\t\tN'SELECT * FROM #result;DROP TABLE #result;'\n\t\t\t\t\t) AS NVARCHAR(MAX));\n--PRINT @main_script\nEXEC (@main_script);<\/code><\/pre>\n
Now that we’ve got the data<\/h2>\n
We want Logstash to push the output of this query into Elastic. This way we will always have some idea what’s been going on the DB server. One way to run queries against SQL is JDBC driver<\/a> – download .jre8.jar<\/code>, unpack and remember the location. <\/p>\n
Defining pipeline<\/p>\n
After we’ve got the driver, everything else is easy: the pipeline is literally one input<\/a> and one output:<\/p>\n
input {\n\t############################################################# Master sp_WhoIsActive ############################################################################\n\tjdbc {\n\t\tid => \"master_spWhoIsActive\"\n\t\tjdbc_driver_library => \"path\\to\\jdbc\\lib\\mssql-jdbc-7.2.1.jre8.jar\"\n\t\tjdbc_driver_class => \"com.microsoft.sqlserver.jdbc.SQLServerDriver\"\n\t\tjdbc_connection_string => \"jdbc:sqlserver:\/\/<your connection string>\"\n\t\tjdbc_user => nil\n\t\tschedule => \"* * * * *\" ## runs each minute\n\t\tstatement => \"-- literally paste sql code from above inside quotes here\"\n\t\tadd_field => {\n\t\t\t\t\t\t\"database\" => \"master\" ## optionally add more fields to distinguish where data comes from\n\t\t\t\t\t\t\"instance\" => \"MY-SQL-Server\"\n\t\t\t\t\t}\n\t}\n\t################################################################################################################################################################\t\n}\noutput {\n\telasticsearch {\n\t\thosts => \"elasticsearch:9200\"\n\t\tindex => \"whoisactive-%{+YYYY.MM}\"            \n\t}\n}<\/code><\/pre>\n
Restart Logstash and watch Kibana updating with new events!<\/p>\n","protected":false},"excerpt":{"rendered":"
Building up on from basic telegraf inputs we now want some actual SQL data. And this time around it’s going to be not just numeric telemetry, we’d gain some insights into actual queries that get run. Our choice of Elastic Up to this point our choice of data back-end has been unjustified. Telegraf is intended … Continue reading “Monitoring SQL Server: fiddling with sp_whoisactive output”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":465,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[7],"tags":[38,59],"class_list":["post-431","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-infrastructure","tag-monitoring","tag-sql"],"yoast_head":"\nMonitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates<\/title>\n<meta name=\"description\" content=\"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Monitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates\" \/>\n<meta property=\"og:description\" content=\"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/\" \/>\n<meta property=\"og:site_name\" content=\"Timur and associates\" \/>\n<meta property=\"article:published_time\" content=\"2019-10-03T16:06:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-07T11:49:18+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"778\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"timur\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@TimurKh\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/\"},\"author\":{\"name\":\"timur\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\"},\"headline\":\"Monitoring SQL Server: fiddling with sp_whoisactive output\",\"datePublished\":\"2019-10-03T16:06:00+00:00\",\"dateModified\":\"2026-03-07T11:49:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/\"},\"wordCount\":419,\"image\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/elk_1920.jpg\",\"keywords\":[\"monitoring\",\"sql\"],\"articleSection\":[\"Infrastructure\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/\",\"url\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/\",\"name\":\"Monitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/elk_1920.jpg\",\"datePublished\":\"2019-10-03T16:06:00+00:00\",\"dateModified\":\"2026-03-07T11:49:18+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\"},\"description\":\"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#primaryimage\",\"url\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/elk_1920.jpg\",\"contentUrl\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/elk_1920.jpg\",\"width\":1920,\"height\":778},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/index.php\\\/2019\\\/10\\\/04\\\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Monitoring SQL Server: fiddling with sp_whoisactive output\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#website\",\"url\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/\",\"name\":\"Timur and associates\",\"description\":\"Notes of an IT contractor\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/blog.wiseowls.co.nz\\\/#\\\/schema\\\/person\\\/34d0ed30d573b5bc317ea990bd2e0c59\",\"name\":\"timur\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg\",\"caption\":\"timur\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/TimurKh\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Monitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates","description":"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/","og_locale":"en_US","og_type":"article","og_title":"Monitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates","og_description":"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.","og_url":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/","og_site_name":"Timur and associates","article_published_time":"2019-10-03T16:06:00+00:00","article_modified_time":"2026-03-07T11:49:18+00:00","og_image":[{"width":1920,"height":778,"url":"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg","type":"image\/jpeg"}],"author":"timur","twitter_card":"summary_large_image","twitter_creator":"@TimurKh","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#article","isPartOf":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/"},"author":{"name":"timur","@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59"},"headline":"Monitoring SQL Server: fiddling with sp_whoisactive output","datePublished":"2019-10-03T16:06:00+00:00","dateModified":"2026-03-07T11:49:18+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/"},"wordCount":419,"image":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#primaryimage"},"thumbnailUrl":"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg","keywords":["monitoring","sql"],"articleSection":["Infrastructure"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/","url":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/","name":"Monitoring SQL Server: fiddling with sp_whoisactive output - Timur and associates","isPartOf":{"@id":"https:\/\/blog.wiseowls.co.nz\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#primaryimage"},"image":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#primaryimage"},"thumbnailUrl":"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg","datePublished":"2019-10-03T16:06:00+00:00","dateModified":"2026-03-07T11:49:18+00:00","author":{"@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59"},"description":"Feeding sp_whoisactive output into Elasticsearch via Logstash JDBC. Now we can see active queries over time.","breadcrumb":{"@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#primaryimage","url":"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg","contentUrl":"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2020\/03\/elk_1920.jpg","width":1920,"height":778},{"@type":"BreadcrumbList","@id":"https:\/\/blog.wiseowls.co.nz\/index.php\/2019\/10\/04\/monitoring-sql-server-fiddling-with-sp_whoisactive-output\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blog.wiseowls.co.nz\/"},{"@type":"ListItem","position":2,"name":"Monitoring SQL Server: fiddling with sp_whoisactive output"}]},{"@type":"WebSite","@id":"https:\/\/blog.wiseowls.co.nz\/#website","url":"https:\/\/blog.wiseowls.co.nz\/","name":"Timur and associates","description":"Notes of an IT contractor","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.wiseowls.co.nz\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blog.wiseowls.co.nz\/#\/schema\/person\/34d0ed30d573b5bc317ea990bd2e0c59","name":"timur","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/23d55e17d4f0990ee4d12bc6e5dcfb58a292934efd62a185756876379e780b16?s=96&r=pg","caption":"timur"},"sameAs":["https:\/\/x.com\/TimurKh"]}]}},"_links":{"self":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/comments?post=431"}],"version-history":[{"count":7,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/431\/revisions"}],"predecessor-version":[{"id":1367,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/posts\/431\/revisions\/1367"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/media\/465"}],"wp:attachment":[{"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/media?parent=431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/categories?post=431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.wiseowls.co.nz\/index.php\/wp-json\/wp\/v2\/tags?post=431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}