I have a simple timechart query
index = netflow flow_dir= 0 |timechart sum(bytes) by src_ip
I'm wondering how I would rename top source IPs to the result of actual DNS lookups. Theoretically, I could do DNS lookup before the timechart
index = netflow flow_dir= 0
| lookup dnslookup clientip as src_ip OUTPUT clienthost as DST_RESOLVED
| timechart sum(bytes) by DST_RESOLVED
but in this way I would have to lookup every src IP (very slow) in the query not just top values. Is there any recommended approach for that problem?
Try this
*UPDATED*
index = netflow flow_dir= 0 | timechart sum(bytes) AS bytes by src_ip | untable _time src_ip bytes | lookup dnslookup clientip as src_ip OUTPUT clienthost as DST_RESOLVED | table _time DST_RESOLVED bytes | xyseries _time DST_RESOLVED bytes
| timechart span=1m eval(sum(is_slow)/count) by v
| rename NULL as ratioOfSlow
did the job for me.
Try this
*UPDATED*
index = netflow flow_dir= 0 | timechart sum(bytes) AS bytes by src_ip | untable _time src_ip bytes | lookup dnslookup clientip as src_ip OUTPUT clienthost as DST_RESOLVED | table _time DST_RESOLVED bytes | xyseries _time DST_RESOLVED bytes
First of all I guess you meant to write "sum(bytes) AS bytes" instead of "sum(bytes) a bytes". Anyway
index = netflow flow_dir= 0 | bin span=15m _time | stats sum(bytes) AS bytes by _time src_ip
may return hundreds of thousands results so to be honest I don't think performance is going to be better than doing DNS lookup before timechart unless you select top values. And in such case (selecting top values) you have to take care of calculating "OTHER" column which will probably make query quite complex.
Try the updated query
I think your idea is magnificent! 🙂
I wasn't really aware of untab and xyseries functions.
I would just do something like
| lookup dnslookup clientip as src_ip OUTPUT clienthost as DST_RESOLVED
|eval DST_RESOLVED=if(isnull(DST_RESOLVED),src_ip,DST_RESOLVED)
in order not to loose "OTHER" value.
Thank you very much
maybe something like:
index = netflow flow_dir= 0
| timechart sum(bytes) as bytes by src_ip
| sort - bytes
| join src_ip [|inputlookup dnslookup |rename clientip as src_ip clienthost as DST_RESOLVED|table src_ip DST_RESOLVED]
| streamstats count
| eval src_ip=if(count<11,DST_RESOLVED,src_ip)
| fields - count - DST_RESOLVED
It does not seem to be working for me and I guess the reason for it is that |inputlookup dnslookup is going to work only for static lookup tables. Also I'm not sure if that query would handle correctly "OTHER" column produced by timechart.