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?
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?
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?