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

[beetlegeuse]

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:

_time	server_name	LRU expired	lifetime	inactive	del	add	miss	hit	current_sessions
18:00:00	ServerX	0	0	21157	297	21967	8801	79198	513
18:00:00	ServerY	0	1	21085	230	21861	8880	74540	545
18:05:00	ServerX	6100	0	71624	6122	80511	190	6239	2765

...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:

_time	server_name	metric_type	metric_value
2022-08-02 18:00:00	ServerX	LRU expired	0
2022-08-02 18:00:00	ServerX	lifetime	0
2022-08-02 18:00:00	ServerX	inactive	21157
2022-08-02 18:00:00	ServerX	del	297
2022-08-02 18:00:00	ServerX	add	21967
2022-08-02 18:00:00	ServerX	miss	8801
2022-08-02 18:00:00	ServerX	hit	79198
2022-08-02 18:05:00	ServerX	LRU expired	0
2022-08-02 18:05:00	ServerX	lifetime	1
2022-08-02 18:05:00	ServerX	inactive	21085
2022-08-02 18:05:00	ServerX	del	230
2022-08-02 18:05:00	ServerX	add	21861
2022-08-02 18:05:00	ServerX	miss	8880
2022-08-02 18:05:00	ServerX	hit	74540
2022-08-02 18:00:00	ServerY	LRU expired	6100
2022-08-02 18:00:00	ServerY	lifetime	0
2022-08-02 18:00:00	ServerY	inactive	71624
2022-08-02 18:00:00	ServerY	del	6122
2022-08-02 18:00:00	ServerY	add	80511
2022-08-02 18:00:00	ServerY	miss	190
2022-08-02 18:00:00	ServerY	hit	6239

How should I adjust my query to accommodate my requirements?

[somesoni2]

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)

[yuanliu · Accepted solution]

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)

[beetlegeuse]

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

[beetlegeuse]

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".