Hello Splunkers - I am struggling to create a table that shows distinct events that sometimes have the same timestamp:
_time | vulnerability | asset | ipAddress | vendor | cvssScore | lastFound | supportContact |
2022-05-12 05:23:24 | CVE-2022-1234 | host1 | ip1 | vendor1 | score1 | 2022-05-12 | support1 |
2022-05-12 05:23:24 | CVE-2021-5678 | host2 | ip2 | vendor2 | score2 | 2022-05-12 | support2 |
2022-05-12 05:23:24 | CVE-2016-1234 | host3 | ip3 | vendor3 | score3 | 2022-05-12 | support3 |
I can't find the right way to search these events so that all distinct events show. Based on one of the many answers I read here, I've tried using eventstats, but it's not working as I'd hoped. Here's the query:
| eventstats latest(_time) as lastFound | where lastFound=_time
| table _time, vulnerability, asset, ipAddress, vendor, cvssScore, lastFound, supportContact
When I run this I get a table with the latest events by _time, but it does not take into account that there are different values in the other fields. So instead of the 5,000 events I'm expecting, I get a few hundred.
_time | vulnerability | asset | ipAddress | vendor | cvssScore | lastFound | supportContact |
2022-05-12 05:23:24 | CVE-2022-1234 | host1 | ip1 | vendor1 | score1 | lastFoundTime | support1 |
What am I doing wrong?
Your eventstats is not taking the different values of the fields into account
| eventstats latest(_time) as lastFound by vulnerability, asset, ipAddress, vendor, cvssScore, supportContact
| where lastFound=_time
| table _time, vulnerability, asset, ipAddress, vendor, cvssScore, lastFound, supportContact
That did the trick! I had a feeling I was missing something basic, thank you very much!
Can you tell me the difference between using eventstats latest vs. stats latest in this particular case? I can see that it adjusts the number of events I receive, but I'm not sure why.
Eventstats will add fields to the events in the pipeline without removing any events - stats will replace all the events in the pipeline with events with the aggregated values.
Makes sense, thanks again!
Your eventstats is not taking the different values of the fields into account
| eventstats latest(_time) as lastFound by vulnerability, asset, ipAddress, vendor, cvssScore, supportContact
| where lastFound=_time
| table _time, vulnerability, asset, ipAddress, vendor, cvssScore, lastFound, supportContact