Splunk Search

Creating a table with common columns for multiple fields

jamesklassen
Path Finder

I have performance data captured with Splunk with fields and data like this:

DatabaseCachePercentHit=0
DatabaseCacheSizeMB=22839
DatabasePageFaultStallsPersec=0
DatabasePageFaultsPersec=0
IODatabaseReadsAttachedAverageLatency=0
IODatabaseReadsRecoveryAverageLatency=0
ComputerName=MAIL1S1

I need to turn this into a table with the columns: Name,Latest,Max(last 24h),Average

How can I use stats to create the same columns for these different fields? And group them each into a row?

Tags (1)
0 Karma
1 Solution

southeringtonp
Motivator

Here's one approach.

For simplicity, this assumes that your max is calculated over the same time period as your reporting range. It pulls everything into field values rather than field names, since stats and most other transformative commands want to operate against values:

sourcetype=whatever
| rex field=_raw max_match=10 "(?<kvpair>\w+=\d+)"
| mvexpand kvpair
| rex field=kvpair "(?<key>\w+)=(?<value>\d+)"
| stats first(value) as "Latest" max(value) as "Max" avg(value) as "Average" by host,key
| sort host,key

If your intent was to have the 'Max' value for only the last 24 hours, regardless of the overall search time window, you can mask out the older values:

...
| eval todayvalue=if(now()-86400<_time, todayvalue, null)
| stats first(value) as "Latest" max(todayvalue) as "24h Max" avg(value) as "Average" by host,key

View solution in original post

0 Karma

southeringtonp
Motivator

Here's one approach.

For simplicity, this assumes that your max is calculated over the same time period as your reporting range. It pulls everything into field values rather than field names, since stats and most other transformative commands want to operate against values:

sourcetype=whatever
| rex field=_raw max_match=10 "(?<kvpair>\w+=\d+)"
| mvexpand kvpair
| rex field=kvpair "(?<key>\w+)=(?<value>\d+)"
| stats first(value) as "Latest" max(value) as "Max" avg(value) as "Average" by host,key
| sort host,key

If your intent was to have the 'Max' value for only the last 24 hours, regardless of the overall search time window, you can mask out the older values:

...
| eval todayvalue=if(now()-86400<_time, todayvalue, null)
| stats first(value) as "Latest" max(todayvalue) as "24h Max" avg(value) as "Average" by host,key
0 Karma

jamesklassen
Path Finder

This works perfectly, exactly what I wanted. Thank you.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...