How to display palo threat over a month with severity again allowed & blocked actions?

[mkshah]

Hi ,

how to do i display number of blocked and allowed threats with different severities in a timeframe(e.g monthly).

Something like this output,

Month                    action               critical            high                medium               low

2022-11              allowed               9                        22                  45                        100 

                                blocked                20                     400           44345                   23423

2022-10              allowed               39                        22                  4                        100 

                                blocked                20                     500           4445                   23423

I can get to either of below output but not able to get as above,,

----

index=palo-network threat sourcetype="pan:threat" severity!=informational| bucket _time span=1month | eval Date=strftime('_time',"%Y-%m")| stats values(severity) count by _time,action

----

index=palo-network threat sourcetype="pan:threat" severity!=informational | bucket _time span=1month | eval Date=strftime('_time',"%Y-%m") | chart count over action by severity

Thank you.

[yuanliu]

You gave an excellent mockup of desired output.  What you should have explained is that the rest of columns (critical, high, medium, low) are values of the field named "severity".  This finer point may seem obvious to you, but is not always obvious to other people who may be able to help.  Pro tip: Always explain your data, generally illustrate sample data in text if possible. (Anonymize as necessary.)

Back to your search.  First off, the stats you are really looking for is to combine what you have already tried:

index=palo-network threat sourcetype="pan:threat" severity!=informational
| bucket _time span=1month
| eval Date=strftime('_time',"%Y-%m")
| stats values(severity) count by _time, action, severity

Now, you notice that the output is not in the format you wanted.  So, try to massage the output into the desired format - but only after get the stats correct.  To do so, you can populate severity levels into a diagonal matrix. (This may not be the most efficient method but is the most visually clear.)

``` your base search above ```
| foreach low medium high critical
    [eval <<FIELD>> = if(severity == "<<FIELD>>", count, 0)]
| stats sum(critical) as critical sum(high) as high sum(medium) as medium sum(low) as low by Date action

Put them together,

index=palo-network threat sourcetype="pan:threat" severity!=informational
| bucket _time span=1month
| eval Date=strftime('_time',"%Y-%m")
| stats count by Date, action, severity
| foreach low medium high critical
    [eval <<FIELD>> = if(severity == "<<FIELD>>", count, 0)]
| stats sum(critical) as critical sum(high) as high sum(medium) as medium sum(low) as low by Date action

Hope this helps.