Splunk Search

What is the best way to rename timechart columns?

Path Finder

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?

1 Solution

Legend

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

View solution in original post

Legend

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

View solution in original post

Path Finder

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.

0 Karma

Legend

Try the updated query

0 Karma

Path Finder

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

0 Karma

Super Champion

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
0 Karma

Path Finder

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.

0 Karma