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

[kmasood]

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?

[erwan_raulet]

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

[erwan_raulet]

Thanks for the link 🙂

[kmasood]

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

[somesoni2]

Why not just this

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

[somesoni2]

Yeah. wish I could type faster like her 🙂

[kmasood]

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

[lguinn2 · Accepted solution]

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

[kmasood]

Thank you, just what I was looking for.