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:
I'm attempting to do the following:
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?
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)
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)
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)
That did it...thank you so much for your assistance!
Thank you, @yuanliu...I'll give this a try.
The "current_sessions" field is explained in this portion of my question: