Splunk Search

Collect different key-value pairs from separate events for a given host and show in a table?

beetlegeuse
Path Finder

I have the following events that arrive every five minutes from a pool of servers (two servers' events shown):

 

Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache LRU expired : 0
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache lifetime : 0
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache inactive : 21157
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache del : 297
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache add : 21967
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache miss : 8801
Aug  2 18:00:23 ServerX stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache hit : 79198
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache LRU expired : 0
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache lifetime : 1
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache inactive : 21085
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache del : 230
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache add : 21861
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache miss : 8880
Aug  2 18:00:32 ServerY stats.pdweb.sescache 2022-08-02-18:00:00.000-05:00I----- pdweb.sescache hit : 74540
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache LRU expired : 6100
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache lifetime : 0
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache inactive : 71624
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache del : 6122
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache add : 80511
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache miss : 190
Aug  2 18:05:23 ServerX stats.pdweb.sescache 2022-08-02-18:05:00.000-05:00I----- pdweb.sescache hit : 6239

 

The server names (in this case, "ServerX" and "ServerY") are extracted at index time as a field called "server_name". In addition, two other field extractions are performed at index time:

  • "metric_type": In this example, the values are "LRU expired", "lifetime", "inactive", "del", "add", "miss" and "hit".
  • "metric_value": The numeric value at the end of each event.

I'm attempting to do the following:

  • Collect the "metric_value" values aligned with the seven metric types for each server in five minute increments and display all values in a table (each row reflecting the unique time, server name, and values for each metric type)
  • Perform arithmetic operations against four of the metric types (add - (del + inactive + lifetime)) to create a new value "current_sessions".

I envision the output to look like this:

_timeserver_nameLRU expiredlifetimeinactivedeladdmisshitcurrent_sessions
18:00:00ServerX002115729721967880179198513
18:00:00ServerY012108523021861888074540545
18:05:00ServerX610007162461228051119062392765

...and so on...

Here's what I've put together so far:

 

index=foo sourcetype=bar stats_category="pdweb.sescache"  
| bin span=5m _time 
| stats values(*) AS * by server_name, metric_type, _time | table _time, server_name, metric_type, metric_value

 

The resulting table shows me the following:

_timeserver_namemetric_typemetric_value
2022-08-02 18:00:00ServerXLRU expired0
2022-08-02 18:00:00ServerXlifetime0
2022-08-02 18:00:00ServerXinactive21157
2022-08-02 18:00:00ServerXdel297
2022-08-02 18:00:00ServerXadd21967
2022-08-02 18:00:00ServerXmiss8801
2022-08-02 18:00:00ServerXhit79198
2022-08-02 18:05:00ServerXLRU expired0
2022-08-02 18:05:00ServerXlifetime1
2022-08-02 18:05:00ServerXinactive21085
2022-08-02 18:05:00ServerXdel230
2022-08-02 18:05:00ServerXadd21861
2022-08-02 18:05:00ServerXmiss8880
2022-08-02 18:05:00ServerXhit74540
2022-08-02 18:00:00ServerYLRU expired6100
2022-08-02 18:00:00ServerYlifetime0
2022-08-02 18:00:00ServerYinactive71624
2022-08-02 18:00:00ServerYdel6122
2022-08-02 18:00:00ServerYadd80511
2022-08-02 18:00:00ServerYmiss190
2022-08-02 18:00:00ServerYhit6239

 

How should I adjust my query to accommodate my requirements?

Labels (4)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

My goto shortcut is to cheat😉.  Something like

 

index=foo sourcetype=bar stats_category="pdweb.sescache"  
| bin span=5m _time 
| foreach "URL expired" "lifetime" "inactive" "del" "add" "miss" "hit"
    [eval <<FIELD>> = if(metric_type == "<<FIELD>>", metric_value, null())]
| stats sum(*) AS * by _time server_name
| table _time server_name "URL expired" lifetime inactive del add miss hit
| eval current_sessions = add - (del + inactive + lifetime)

 

  Note I do not know how current_sessions is derived. (Updated)

View solution in original post

Tags (1)

somesoni2
Revered Legend

Give this a try

index=foo sourcetype=bar stats_category="pdweb.sescache"  
| fields _time server_name metric_type metric_value
| eval {metric_type}=metric_value
| bin span=5m _time 
| stats values(*) as * by _time server_name 
| eval current_sessions = add - (del + inactive + lifetime)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

My goto shortcut is to cheat😉.  Something like

 

index=foo sourcetype=bar stats_category="pdweb.sescache"  
| bin span=5m _time 
| foreach "URL expired" "lifetime" "inactive" "del" "add" "miss" "hit"
    [eval <<FIELD>> = if(metric_type == "<<FIELD>>", metric_value, null())]
| stats sum(*) AS * by _time server_name
| table _time server_name "URL expired" lifetime inactive del add miss hit
| eval current_sessions = add - (del + inactive + lifetime)

 

  Note I do not know how current_sessions is derived. (Updated)

Tags (1)

beetlegeuse
Path Finder

That did it...thank you so much for your assistance!

0 Karma

beetlegeuse
Path Finder

Thank you, @yuanliu...I'll give this a try.

The "current_sessions" field is explained in this portion of my question:

  • Perform arithmetic operations against four of the metric types (add - (del + inactive + lifetime)) to create a new value "current_sessions".
0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...