Why are the blocked data counts not showing up?

[Yaichael]

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!

[DavidHourani]

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

[Yaichael]

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

[Vijeta]

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

[Yaichael]

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

[Vijeta]

@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