topic Re: How to use eval within stats for data from tstats in Splunk Search

How to use eval within stats for data from tstats

kcheek_umich — Tue, 23 Jul 2019 19:38:26 GMT

I'm trying to use eval within stats to work with data from tstats, but it doesn't seem to work the way I expected it to work. I'm hoping there's something that I can do to make this work.

Here's a simplified version of what I'm trying to do:

| tstats summariesonly=t allow_old_summaries=f prestats=t count from datamodel=Network_Traffic.All_Traffic where (All_Traffic.action!="unknown") by _time,sourcetype,All_Traffic.action span=1h 
| `drop_dm_object_name("All_Traffic")` 
| stats count as total_connections count(eval(action="allowed")) as allowed count(eval(action="blocked" OR action="dropped")) as blocked by _time, sourcetype

When I run that, I see valid numbers for total_connections, but the "allowed" and "blocked" values are all just "0"

The following works for me:

| tstats summariesonly=t allow_old_summaries=f prestats=f count from datamodel=Network_Traffic.All_Traffic where (All_Traffic.action!="unknown") by _time,sourcetype,All_Traffic.action span=1h 
| `drop_dm_object_name("All_Traffic")`

However, that doesn't present the data in the way I want it. I'd like to add things like percentage blocked per sourcetype, etc., with additional eval statements.

Any suggestions for how to get the stats command to work with those nested eval statements? Is that unsupported? (I've read that nested eval within tstats isn't supported, but that it is supported within stats)

Thanks!

Re: How to use eval within stats for data from tstats

jaime_ramirez — Tue, 23 Jul 2019 23:43:08 GMT

Try without the prestats option (it works as desired for me):

| tstats summariesonly=t allow_old_summaries=f count from datamodel=Network_Traffic.All_Traffic where (All_Traffic.action!="unknown") by _time,sourcetype,All_Traffic.action span=1h 
| `drop_dm_object_name("All_Traffic")` 
| stats count as total_connections count(eval(action="allowed")) as allowed count(eval(action="blocked" OR action="dropped")) as blocked by _time, sourcetype

Hope it helps

Re: How to use eval within stats for data from tstats

kcheek_umich — Wed, 24 Jul 2019 19:05:44 GMT

While that does produce numbers in more of the fields, they aren't correct numbers when I try that. Instead of counting the number of network traffic events, stats just counts the number of distinct values of "action" per sourcetype that match each eval statement. (so, in my case, the calculated values from the stats command are all 0, 1, 2, or 3)

Re: How to use eval within stats for data from tstats

woodcock — Sat, 27 Jul 2019 17:24:21 GMT

This works for me (using prestats is a deep, dark magic):

| tstats summariesonly=t allow_old_summaries=f count
FROM datamodel=Network_Traffic.All_Traffic 
WHERE (All_Traffic.action!="unknown") BY sourcetype All_Traffic.action _time span=1h 
 | rename All_Traffic.* AS *
 | stats count As total_connections count(eval(action=="allowed")) AS allowed count(eval(action=="blocked" OR action=="dropped")) AS blocked BY _time, sourcetype
| eval pct_blocked = 100 * blocked / total_connections

Re: How to use eval within stats for data from tstats

kcheek_umich — Mon, 29 Jul 2019 14:23:53 GMT

For me, this has the same result as the suggestion from jaime.ramirez - I don't get counts of network events. Instead, I get what appears to be the count of unique values for "action" for each "stats eval" for each sourcetype (0, 1, 2, or 3).

Re: How to use eval within stats for data from tstats

woodcock — Mon, 29 Jul 2019 18:05:32 GMT

You do see the total_connections field, right?

Re: How to use eval within stats for data from tstats

kcheek_umich — Mon, 29 Jul 2019 18:12:36 GMT

Yep - and it only has the values 0, 1, 2, or 3 - it doesn't actually contain the total number of connections unless I use prestats=t

Re: How to use eval within stats for data from tstats

woodcock — Mon, 29 Jul 2019 18:54:20 GMT

OK, I finally get it. Try this:

| tstats summariesonly=t allow_old_summaries=f count
FROM datamodel=Network_Traffic.All_Traffic 
WHERE index=* BY sourcetype All_Traffic.action _time span=1h 
| rename All_Traffic.* AS * 
| stats count As total_connections count(eval(action=="allowed")) AS allowed count(eval(action=="blocked" OR action=="dropped")) AS blocked BY _time, sourcetype 
| eval pct_blocked = 100 * blocked / total_connections

Re: How to use eval within stats for data from tstats

stoomart — Thu, 06 Oct 2022 20:17:57 GMT

The trick is to use "case" eval function:

count(eval(action="allowed")) as allowed count(eval(action="blocked" OR action="dropped")) as blocked values(eval(case(action="allowed", count))) as allowed values(eval(case(action="blocked" OR action="dropped", count))) as blocked