Hello,
I have the following search string, but "_time" keeps coming up blank. It appears that something is "clearing" it, but I can't figur eout what.
sourcetype=cisco:asa dest_ip="xxx.xxx.0.0/16" NOT "Failover primary closed" | stats count by src_ip,dest_ip | eval Connections = count | iplocation src_ip | where Country = "China" OR Country = "Iran" OR Country = "Iraq" OR Country = "Russia" OR Country = "Brazil" OR Country = "North Korea" | lookup dnsLookup ip as src_ip | eval ExtFQDN=if(isnotnull(host),host,src_ip) | eval ExtIPCity=if(City="","Not available",City) | eval ExtIPRegion=if(Region="","Not available",Region) | table Country ExtIPRegion ExtIPCity src_ip ExtFQDN dest_ip Connections _time | dedup src_ip dest_ip
Thanks,
Jon
@jon.d.irish.ctr, try the following search. Change the span (which is 1m) as per time aggregation that you need.
sourcetype=cisco:asa dest_ip="xxx.xxx.0.0/16" NOT "Failover primary closed"
| bin _time span=1m
| stats count by src_ip,dest_ip,_time
| eval Connections = count
| iplocation src_ip
| where Country = "China" OR Country = "Iran" OR Country = "Iraq" OR Country = "Russia" OR Country = "Brazil" OR Country = "North Korea"
| lookup dnsLookup ip as src_ip
| eval ExtFQDN=if(isnotnull(host),host,src_ip)
| eval ExtIPCity=if(City="","Not available",City)
| eval ExtIPRegion=if(Region="","Not available",Region)
| table Country ExtIPRegion ExtIPCity src_ip ExtFQDN dest_ip Connections _time
| dedup src_ip dest_ip
Thanks for the comments everyone! I did get it working using nikenilay's example.
@jon.d.irish.ctr, try the following search. Change the span (which is 1m) as per time aggregation that you need.
sourcetype=cisco:asa dest_ip="xxx.xxx.0.0/16" NOT "Failover primary closed"
| bin _time span=1m
| stats count by src_ip,dest_ip,_time
| eval Connections = count
| iplocation src_ip
| where Country = "China" OR Country = "Iran" OR Country = "Iraq" OR Country = "Russia" OR Country = "Brazil" OR Country = "North Korea"
| lookup dnsLookup ip as src_ip
| eval ExtFQDN=if(isnotnull(host),host,src_ip)
| eval ExtIPCity=if(City="","Not available",City)
| eval ExtIPRegion=if(Region="","Not available",Region)
| table Country ExtIPRegion ExtIPCity src_ip ExtFQDN dest_ip Connections _time
| dedup src_ip dest_ip
After this part of your query:
| stats count by src_ip,dest_ip
you will be left with three fields: count
, src_ip
, and dest_ip
. If you want _time
to persist through this part of the query, you'll need to restructure it somehow. Given that you're trying to count by src_ip
and dest_ip
but want to retain timestamps, you might find it helpful to do this instead:
| eventstats count by src_ip,dest_ip
| fields src_ip dest_ip _time count
...
That will retain the _time field and also narrow down the fields to the four you need - the three you had before, plus _time
.
You are clearing it here: | stats count by src_ip,dest_ip
- after that, you only have count, src_ip and dest_ip
Regards,
-Kai.