topic Re: Stats to Display Counts of FQDNs and IP addresses in Same Column in Splunk Search

Stats to Display Counts of FQDNs and IP addresses in Same Column

kmasood — Fri, 10 Oct 2014 16:00:10 GMT

Hello,

I have this query, which takes an ip address, returns FQDN and count columns:

base search | `ip2fqdn(ip)` | stats count by FQDN

However, there are some ip addresses that do not resolve to FQDNs, and those show up as "No Reverse Lookup". How do I get the ip addresses to appear for those entries in the above query? The result would look like:

FQDN (or IP)          Count
www.domain.tld         100
10.1.2.3               75
10.1.2.4               70
example.domain.tld     66

I've looked at coalesce and hoping to avoid doing

base search | `ip2fqdn(ip)` | stats count by FQDN,ip

Update

Using this query, I've been been able to get what I need:

base search | `ip2fqdn(ip)`
| eval myfield=FQDN." ".ip
| rex mode=sed field=myfield "s/No Reverse Lookup//g"
| eval myfield=replace(myfield,"(\w+) \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}","\1")
| stats count by myfield

Is there a more efficient way of doing this?

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

lguinn2 — Fri, 10 Oct 2014 20:42:06 GMT

This should do it - I don't know if it will be a lot faster, but it is a lot more simple.

base search 
| `ip2fqdn(ip)`
| eval myfield = if(FQDN=="No Reverse Lookup",ip,FQDN)
| stats count by myfield

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

somesoni2 — Fri, 10 Oct 2014 20:44:47 GMT

Why not just this

base search | `ip2fqdn(ip)` | eval FQDN=if (FQDN="No Reverse Lookup", ip,FQDN) |stats count by FQDN

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

kmasood — Fri, 10 Oct 2014 20:49:00 GMT

Thanks for dropping in, lguinn beat you by 3 mins (-:

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

kmasood — Fri, 10 Oct 2014 20:49:19 GMT

Thank you, just what I was looking for.

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

somesoni2 — Fri, 10 Oct 2014 20:53:16 GMT

Yeah. wish I could type faster like her 🙂

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

erwan_raulet — Wed, 15 Oct 2014 14:35:06 GMT

Hello, is it possible to know what the search macro ip2fqdn(ip)does because I am very interesting to implement the same feature?

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

kmasood — Wed, 15 Oct 2014 14:47:56 GMT

See the external fields lookup example (http://docs.splunk.com/Documentation/Splunk/6.1.3/Knowledge/Addfieldsfromexternaldatasources#External_fields_lookup_example): -- that ships with Splunk Enterprise

Re: Stats to Display Counts of FQDNs and IP addresses in Same Column

erwan_raulet — Wed, 15 Oct 2014 15:32:19 GMT

Thanks for the link 🙂