Transpose Multi Value Fields

alacercogitatus — Mon, 28 Sep 2020 19:19:21 GMT

So I'm working on a new App, one that generates summary data based on eventtypes and fields. The summary data looks like this:

03/31/2015 08:20:00 -0400, search_name=kpi_5minute_gen, search_now=1427804700.000, info_min_time=1427804400.000, info_max_time=1427804700.000, info_search_time=1427804701.062, avgmetric_thermostat:_:current_humidity="33.333333", avgmetric_thermostat:_:current_temperature="22.100000", avgmetric_thermostat:_:target_temperature="23.116333", interval=perfiveminute, maxmetric_thermostat:_:current_humidity=34, maxmetric_thermostat:_:current_temperature="22.47", maxmetric_thermostat:_:target_temperature="24.599", minmetric_thermostat:_:current_humidity=33, minmetric_thermostat:_:current_temperature="21.36", minmetric_thermostat:_:target_temperature="22.375", app=mykpi

But I wanted to be able to graph it by each field (here represented by target_temperature, current_humidity, and current_temperature) AND by the metadata contained within the field name itself (here my metric type is "min", "max", "avg") and the correlated eventtype is "thermostat".

So why not do this, you ask?

| timechart avg(avgmetric_*) as * | rename thermostat:_:* as *

Well, because that doesn't help me perform visualizations on the eventtype. I need my data in a form like this (this is simplified of course):

_time             interval       metric_type         eventtype               field                 value
3/20/2014       perfiveminute      avg            thermostat       current_temperature          22.47
3/21/2014       perfiveminute      max          thermostat      current_humidity                 34

and so on.

So here's my question: How do I extract metadata from fields and correlate them with their values across several thousand data points while keeping it abstract to apply to each new summary field?

Re: Transpose Multi Value Fields

alacercogitatus — Tue, 31 Mar 2015 13:10:35 GMT

Ok, so here is what I came up with.

index=summary app=mykpi | table * | fields avg* max* min* std* interval _time | foreach * [ eval <<FIELD>>_values = '<<FIELD>>'.",".interval.","._time] | stats values(*_values) as * | transpose 1000000000|rename column as metric "row 1" as values| eval values = replace(values," ",";") | makemv delim=";" values | mvexpand values | makemv delim="," values | eval value = mvindex(values,0) | eval interval = mvindex(values,1) | eval _time = mvindex(values,2) |  fields _time metric interval value | search NOT metric="interval" | eval a1 = split(metric,":_:") |eval a2=mvindex(a1,0)| rex field=a2 "_(?<eventtype>.*)" | eval metric_type = substr(a2,0,3) | eval metric_type = if(metric_type=="std","stdevp",metric_type) | eval field = mvindex(a1,1) | fields - a1 a2

So let's discuss. This gives us the events in a table form with only the fields I want to use.

index=summary app=mykpi | table * | fields avg* max* min* std* interval _time

This foreach gives us a new field that has the values I want to transition separated by commas. The FIELD on the right side of the eval, when expanded, is actually the value (24 or whatever) that we want. So a line of this looks like: "24.32,perfiveminute,199384330".

foreach * [ eval <<FIELD>>_values = '<<FIELD>>'.",".interval.","._time]

Next, stats. We only really want the new fields, so stats is a quick way to get only those. Transpose takes the columns and rows, and flips them. The rows become columns, and the columns become sharks. No wait, rows.

stats values(*_values) as * | transpose 1000000000|rename column as metric "row 1" as values

We now have a table that looks like this:

metric                                   values
avgmetric_thermostat:_:current_humidity 30.666667,perfiveminute,1427806800
avgmetric_thermostat:_:current_temperature  21.460000,perfiveminute,1427806800

So now, we will split apart each value, make them mulitvalue, expand each multivalue event into single events, and to some evaluations to get our final result:

_time   metric  interval    value   eventtype   field   metric_type
2015-03-31 09:00:00 avgmetric_thermostat:_:current_humidity perfiveminute   30.666667   thermostat  current_humidity    avg
2015-03-31 09:00:00 avgmetric_thermostat:_:current_temperature  perfiveminute   21.460000   thermostat current_temperature  avg

So simple! well, maybe not. But that's how I did it. Anyone see some optimizations?

topic Transpose Multi Value Fields in Splunk Search

Transpose Multi Value Fields

Re: Transpose Multi Value Fields