Splunk Search

Why are the blocked data counts not showing up?

Yaichael
Communicator

Good day,

I've the following query where I want to show the amount of times a category was notified "Blocked"
out of the "Detected" ones, but the "Blocked" column isn't bringing up any results.

index=test_csv (source="Detected*.csv" OR source="Stopped*.csv") sourcetype="csv"
| eval cat = if(cat=="new", "web", cat)
| eval action = if( like( notif , "%Deny%" ) OR like( notif , "%Block%" ), "Blocked" , "Detected" )
| stats count(action) as tcount by rep , cat
| sort -tcount
| stats values(rep) as "Rep" , list(tcount) as Detected , list(eval(action=="Blocked")) as Blocked by cat
| sort -Detected
| rename cat as categories

What am I missing?

Thanks!

Tags (1)
0 Karma

DavidHourani
Super Champion

Hi @Yaichael,

Nice query you've got there, just needs a minor tweak, try it as follows :

 index=test_csv (source="Detected*.csv" OR source="Stopped*.csv") sourcetype="csv"
 | eval cat = if(cat=="new", "web", cat)
 | eval action = if( like( notif , "%Deny%" ) OR like( notif , "%Block%" ), "Blocked" , "Detected" )
 | stats values(rep) as "Rep",  count(action) as total, count(eval(action="Blocked")) as BlockedCount by cat
 | rename cat as categories

Let me know if that's what you're looking for.

PS: Also if you only want the count of blocked events regardless of the detected ones you should simply use that as a filter in your initial query as follows :

index=test_csv (source="Detected*.csv" OR source="Stopped*.csv") sourcetype="csv" (notif="*Deny*" OR notif="*Block*")

Cheers,
David

0 Karma

Yaichael
Communicator

After seeing you guys suggestions, I was able to come up with the subsequent query; giving me the expected results. Thanks for shedding some light on me.

index=test_csv (source="Detected*.csv" OR source="Stopped*.csv") sourcetype="csv"
| eval cat = if(cat=="new", "web", cat)
| eval action = if( like( notif , "%Deny%" ) OR like( notif , "%Terminat%" ) OR like( notif , "%Block%" ), "Blocked" , "Detected" )
| stats count(action) as detected , count(eval(action=="Blocked")) as blocked by rep , cat
| sort -detected , -blocked
| stats values(rep) as "Rep" , list(detected) as Detected , list(blocked) as Blocked by cat
| sort -Detected
| rename cat as categories
0 Karma

Vijeta
Influencer

Your first stats command does not have action field so second stats command will not be able to eval on action.
Try changing your first stats command to

| stats count(action) as tcount by rep , cat, action
0 Karma

Yaichael
Communicator

Doing what you suggested made the statistics show incorrect values for Detected and Blocked.

0 Karma

Vijeta
Influencer

@Yaichael Why are you using list(eval(action="Blocked")). If you want to show Detected and Blocked count you can use below query

 index=test_csv (source="Detected*.csv" OR source="Stopped*.csv") sourcetype="csv"
 | eval cat = if(cat=="new", "web", cat)
 | eval action = if( like( notif , "%Deny%" ) OR like( notif , "%Block%" ), "Blocked" , "Detected" )

 | stats values(rep) as "Rep" , count(action) as Detected , count(eval(action="Blocked")) as Blocked by cat
 | sort -Detected
 | rename cat as categories
0 Karma