This has been giving me headaches for a long time now, and it's pretty simple. So, for reference, this search works as intended.
sourcetype="pan_threat" (severity="high" OR "critical") | timechart count, first(threshold) as "Maximum Threshold" by date | eval threshold=200
It charts the amount of hits over time, with the threshold of "200" also charted as its own line (as seen in the replicated graph below).
_time count threshold
1 2:00 17 200
2 2:30 19 200
3 3:00 14 200
etc, etc...
Not a problem. Now, I want to dynamically create the threshold value. The only way I know to calculate a sum of a field is the stats command. So what I'm trying is:
sourcetype=pan_threat severity!=informational | stats count as hits by severity | stats sum(hits) as threshold | timechart count, first(threshold) as "Maximum Threshold" by date
...That should theoretically throw back the same kind of thing, where instead of "Maximum Threshold" being 200, "Maximum Threshold" is the sum of the count. It's not, at all. In fact, it's throwing back "No Results Found", even though it shows 4,949 matching events.
Now, when I remove the timechart section, and search
sourcetype=pan_threat severity!=informational | stats count as hits by severity | stats sum(hits) as total_count
It gives me a very simple table with the data I want, something like the following.
Total Count
1 4949
Also the following search throws back two fields, and it's properly setting "ts" to "threshold", as can be seen in the graph I've tried to replicate below it.
sourcetype=pan_threat severity!=informational | stats count as hits by severity | stats sum(hits) as total_count | eval totalcount=total_count
threshold ts
1 4949 4949
I just want to chart the value of ts, just like I did when I had it setting to a raw number above. Anyone have any idea how to do this?
Try this
sourcetype="pan_threat" (severity="high" OR "critical")
| eventstats count as threshold
| timechart count, first(threshold) as "Maximum Threshold" by date
I did it this way because if you count the events by severity (hits) and then add them all up, it is the same as simply counting. But you can calculate the threshold however you want.
The real difference is that I used 'evenstats' where you used 'stats'. The stats
command results in a table - and only that information continues down the pipeline to the following commands. That's why the timechart
didn't work the way you wanted; it didn't receive the data you intended.
eventstats
adds the calculated field to the event, so it does not summarize the data.
Where you're going wrong is that you're putting commands before timechart
that effectively remove all data that timechart
needs to do its job. The last table you show is also all the data that timechart gets - so it only gets one field called "Total count" with the value 4949.
You should use something like eventstats
instead if you want to perform stats on events and still have the possibility to perform other kinds of statistics afterwards. eventstats differs from stats in that it will write its results as field values to each event instead of consuming all event data and only output a table of fields and values.
Also I don't see a need to perform two separate stats calculations here, as you split by severity in the first but then just sum it all up anyway in the second.
sourcetype=pan_threat severity!=informational | eventstats count as threshold | timechart count, first(threshold) as "Maximum Threshold" by date
Awesome, this did the trick. Now, how do I also calculate the standard deviation? After some modifications and tweaks, I've got the following working as intended...
sourcetype=pan_threat severity!=informational | eventstats count as totalcount | eval threshold=(totalcount/25) | timechart span=1h count, first(threshold) as "Maximum Threshold" by date
In the end, I want that eval statement to act as such:
eval threshold=(totalcount/25+(2*standard_deviation))