How to compare the IP addresses in DNS lookup DNS.csv with a list of servers and their IP addresses in lookup serverlist.csv?

[Sampathu]

Hi,

I have a problem here with DNS lookup. I have a list of servers and their IP address in a lookup file, say serverlist.csv. I have a separate set of DNS in a lookup file say DNS.csv. I want to compare whether the serverA has same IP address both in my serverlist.csv and DNS.csv. If the IP address for serverA is the same in both sheets, then that's fine, but if it's different in both sheets, then it must display result as "IP address does not match". I tried the searches below, but not getting a proper result.

|inputlookup serverlist.csv | eval ipaddress=ip |eval Server_Name=fqdn |  lookup DNS servername OUTPUT fqdn, ipaddr | where 

ipaddress!=ipaddr OR Server_Name!=fqdn | table Server_Name, fqdn, ipaddr 

Is there any other way to do it?

[woodcock · Accepted solution]

Given your clarification:

I have set up as below
1. serverlist.csv. Fields[ip, servername, fqdn, .........]
2. DNS.csv. Fields[servername, fqdn, ipaddr, .........]
Both lists has same servers and ip address
If server1=10.10.20.39 in serverlist.csv then the same server1 must have the same ipaddress as 10.10.20.39 in dns.csv file too.
If server1 has the same ip address in both files then fine.
If the ip address varies for server1 between files, then it must display an output of that server,
e.g.: serverlist.csv server1=10.10.20.39 DIFFERS FROM dns.csv server1=192.34.24.23

This should work:

| inputlookup serverlist.csv | rename ip AS ipaddr | eval source=serverlist.csv | append [| inputlookup DNS.csv | eval source=DNS.csv] | stats dc(ipaddr) AS numIPs dc(source) AS numSources values(*) AS * by servername | where numIPs>1

[Sampathu]

Hi Woodcock, the command above actually din work for me. Again I am using the one that i already tried. But the bad thing is for the below query I am getting a result, whereas the instead of displaying the NO IP Match servers it displays the IP matching servers...

|inputlookup serverlist.csv | eval ipaddress=ip |eval Server_Name=fqdn | lookup DNS servername OUTPUT fqdn, ipaddr | where
ipaddress!=ipaddr OR Server_Name!=fqdn | table Server_Name, fqdn, ipaddr, ipaddress

[woodcock]

Let me explain what my solution does and why it matches the clarification above it and you tell me where the breakdown is. The first inputlookup pulls in every line of serverlist.csv as its own event and then normalizes the IP address field's name to match the same field in the other file: ipaddr. The second inputlookup pulls in every line of DNS.csv as its own event and adds these events to the first list. The stats command merges these 2 datasets based on the common field servername so that each distinct servername value is its own row and all the other fields grouped in these rows. It might be useful to take a look at the output of this command by stripping off the last | where ... part so that you can see that the merge indeed happened. At this point, the "good" rows where there is only a single value for ipaddr for a particular servername will have the following attributes: numSources is 2 and numIPs is 1 and, since we don't care if an entry is in one file but not the other, we can simplify this down to just numIPs is 1. Any "bad" rows will have the opposite, that is numIPs greater than 1. The search is pretty foolproof so I do not see how it could not be giving a correct result unless you have a billion rows in each file and are running into row limits on inputlookup.

[Sampathu]

Hi, your answer was perfect! I was able to get the differences of the whole data sets on both sheets. And here I had condition to list only some set of servers belonging to each group. Hence I made the code as flexible for me.

This code gave me exact output what i was looking for

|inputlookup serverlist.csv | search Group="Admin Group" | eval Server = fqdn | eval Servers=servername | eval ExistingIP = ip |lookup DNS servername OUTPUT fqdn as FQDN, ipaddr as DNS_IP |where (match(Server,FQDN)) OR (NOT match(ExistingIP, DNS_IP)) | table servername, fqdn, ExistingIP, DNS_IP

[woodcock]

This really should be done with shell-script (outside of Splunk) but this should work:

 | inputlookup serverlist.csv | eval ipaddr=ip | eval type=server |  append [| inputlookup DNS.csv | eval type=DNS ] | stats dc(ipaddr) AS numIPs dc(type) AS numTypes values(*) AS * by fqdn| where numIPs>1

[Sampathu]

Thanks for the reply and it was helpful. The above searching lists all the fields in the DNS.csv and serverlist.csv files. Whereas I am specific in looking for the ipaddress that does not match in DNS and serverlist.
For ex: Server1 is having same ip address in both lists whereas Server2 might have different ip address the lists. So my output must be "Server2" or "Server2's ip address given in serverlist.csv"

i tried modifying the above search and hope I am nearing the result

[woodcock]

Try this:

| inputlookup serverlist.csv | eval ipaddr=ip | eval type=server |  append [| inputlookup DNS.csv | eval type=DNS ] | stats dc(ipaddr) AS numIPs dc(type) AS numTypes values(*) AS * by fqdn | where numTypes=2 AND numIPs>1

[Sampathu]

In the above search query may i know why I am not able to do a comparison between ipaddress of both csv files. I am unable to fetch any details of DNS.csv file which play append here.

[woodcock]

I do not understand your question here. The solution that I gave normalizes the field names to be ipaddr and fqdn in both data sets (both CSV files). It then merges all of the records using fqdn values for correlation. Once this is done, we can slice and dice as we see fit to compare/contrast by modifying the where clause.