Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Transpose Multi Value Fields

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

alacercogitatus

SplunkTrust

03-31-2015
05:42 AM

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?

1 Solution

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

alacercogitatus

SplunkTrust

03-31-2015
06:10 AM

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?

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

alacercogitatus

SplunkTrust

03-31-2015
06:10 AM

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?

Get Updates on the Splunk Community!

This blog post is part 3 of 4 of a series on Splunk Assist. Click the links below to see the other ...

November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...