Splunk Search

How to use eval within stats for data from tstats

kcheek_umich
New Member

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!

0 Karma

stoomart
Explorer

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

 

0 Karma

woodcock
Esteemed Legend

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

woodcock
Esteemed Legend

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
0 Karma

kcheek_umich
New Member

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

0 Karma

woodcock
Esteemed Legend

You do see the total_connections field, right?

0 Karma

kcheek_umich
New Member

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

0 Karma

jaime_ramirez
Communicator

Hi

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

alt text

Hope it helps

0 Karma

kcheek_umich
New Member

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)

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...