How can I visualize this data set in Kibana?

I am collecting wait statistics from an RDBMS and would like to visualize them in Kibana. Once a minute I am getting the list of wait type and the duration of the wait. From one interval to the next the type of waits happening can vary and there are dozens of possible wait types.

I started out with an index like this:

put /mssql/
{
  "mappings": {
    "waits" : {
      "properties": {
        "timestamp" : { "type" : "date", "format": "yyyy-MM-dd HH:mm:ss" },
        "sql_instance_name" : { "type" : "text" },
        "waits_ms": { "type" : "object" }
      }
  }
  }
}

And then indexes data like this:

POST mssql/waits
{
  "timestamp" : "2017-11-10 14:56:05",
"sql_instance" : "MYSERVER\\INTERNAL",
  "waits_ms": [ 
    {
      "SOS_SCHEDULER_YIELD" : 100
    },
    {
      "WRITELOG" : 284
    },
    {
      "PAGEIOLATCH_SH" : 11
    },
    {
      "PREEMPTIVE_OS_AUTHENTICATIONOPS" : 446
    },
    {
      "PAGEIOLATCH_EX" : 109
    }
  ]
}

The problem is that when I visualize it in Kibana I need to select the field names and the field names could be different on the next document. For example it could look like:

POST mssql/waits
{
  "timestamp" : "2017-11-10 14:57:05",
"sql_instance" : "MYSERVER\\INTERNAL",
  "waits_ms": [ 
    {
      "ASYNC_NETWORK_IO" : 971
    },
    {
      "CMEMTHREAD" : 663
    },
    {
      "SOS_SCHEDULER_YIELD" : 400
    },
    {
      "ASYNC_IO_COMPLETION" : 389
    },
    {
      "WRITELOG" : 277
    }
  ]
}

So my field choices in Kibana show up as waits_ms.SOS_SCHEDULER_YIELD, waits_ms.ASYNC_IO_COMPLETION, etc. I don't see a way to say ALL waits_ms fields, something like waits_ms.*.

Is there a better approach here?

Thanks

I don't think what you want to do is possible with your data structure. Since your using nested object data, there could be more than one field in that object, so it wouldn't really make sense to do an aggregation over wait_ms.*. For instance, if your data instead was:

 "waits_ms": [ 
    {
      "ASYNC_NETWORK_IO" : 971,
     "name": "hi",
    },
    {
      "CMEMTHREAD" : 663,
     "name": "bye",
    }, 
]

Kibana wouldn't be able to aggregate on wait_ms.*

Could you perhaps add a new field during ingestion to put all the values into an array?
"waits_ms_all": [ 971, 663, ]?

Then I think you could probably do aggregations on the array values.

You might also be able to use a scripted painless field to create a new field that manually does a calculation all those values up, though thats a bit labor intensive depending on how many types of wait_ms.FIELD_NAME's there are.

I'm not sure if that was helpful. It might be better if you explain what your end goal is to do with all the values returned by wait_ms.*?

Hi Stacey,

Thanks for your reply. I'm just starting on this so I can go about this however makes the most sense. Basically what I'm trying to visualize is the various RDBMS wait types occurring in each time interval. For example at 10:15 AM I might have the following wait types and times:

  • CPU - 830 milliseconds
  • Disk - 561 milliseconds
  • Network - 432 milliseconds
  • Backup - 128 milliseconds

Then at 10:16 AM I might have these wait types, some the same, some different

  • CPU - 775 milliseconds
  • Buffer - 145 milliseconds
  • Page Latch - 227 milliseconds
  • Network - 560 milliseconds

I was envisioning a stacked bar chart, one bar for each minute, with each different type stacked in the bar.

I suppose I could have a field type for every single known wait type like this:

put /mssql/
{
  "mappings": {
    "waits" : {
      "properties": {
        "timestamp" : { "type" : "date", "format": "yyyy-MM-dd HH:mm:ss" },
        "sql_instance_name" : { "type" : "text" },
        "SOS_SCHEDULER_YIELD": { "type" : "long" },
        "WRITELOG": { "type" : "long" },
        "PAGEIOLATCH_SH": { "type" : "long" },
        "PREEMPTIVE_OS_AUTHENTICATIONOPS": { "type" : "long" },
        "PAGEIOLATCH_EX": { "type" : "long" }
      }
  }
  }
}

And then select every one of the fields in Kibana but since there are dozens if not over a hundred that could be tedious.

Ah, what if you indexed each type and time as a separate document? I think this might give you what you want.

e.g. here is example data:

POST mssql2/waits/_bulk
{ "index" : { } }
{  "type": "SOS_SCHEDULER_YIELD", "wait_time": 100, "timestamp": "2017-09-27T13:00:00.000Z" }
{ "index" : { } }
{  "type": "WRITELOG", "wait_time": 200, "timestamp": "2017-09-27T13:00:00.000Z" }
{ "index" : { } }
{  "type": "PAGEIOLATCH_SH", "wait_time": 11, "timestamp": "2017-09-27T13:00:00.000Z" }
{ "index" : { } }
{  "type": "PREEMPTIVE_OS_AUTHENTICATIONOPS", "wait_time": 446, "timestamp": "2017-09-27T13:00:00.000Z" }
{ "index" : { } }
{  "type": "ASYNC_IO_COMPLETION", "wait_time": 389, "timestamp": "2017-09-28T13:00:00.000Z" }
{ "index" : { } }
{  "type": "WRITELOG", "wait_time": 300, "timestamp": "2017-09-28T13:00:00.000Z" }
{ "index" : { } }
{  "type": "PAGEIOLATCH_SH", "wait_time": 9, "timestamp": "2017-09-28T13:00:00.000Z" }
{ "index" : { } }
{  "type": "CMEMTHREAD", "wait_time": 663, "timestamp": "2017-09-28T13:00:00.000Z" }

and here is a chart I can make with that data that sounds like what you are looking for:

Then you can filter for instance on type:PAGEIOLATCH_SH to drill down.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.