topic Re: How to compare the output of one lookup to another lookup csv error in Splunk Search

How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:19:13 GMT

This following search works just fine:

| inputlookup assets.csv | inputlookup append=true all_ vulnerabilities.csv | lookup  application_ip_list ip as "IP Address" OUTPUT application | lookup server_ip_list.csv ip as "IP Address" OUTPUT bob_ownership | lookup ip_summary.csv "IP Address" OUTPUT "OS CPE"
| search application="$application$" Plugin!="19506" | `ownership`
| search ownership="$field3$" Plugin=$field4$
| rename ownership as Ownership
| table"IP Address","MAC Address","DNS Name","NetBIOS Name", Ownership

Results:
192.168.1.241 01:50:56:94:46:b3
192.168.1.240 02:60:66:84:45:b11
192.168.1.239 05:51:56:64:13:b16

However, I want compare the results of the above search to another CSV called test_data_ip_changes.csv and get the search to output the differences. I tried using the following search:

| inputlookup assets.csv | inputlookup append=true all_ vulnerabilities.csv | lookup  application_ip_list ip as "IP Address" OUTPUT application | lookup server_ip_list.csv ip as "IP Address" OUTPUT bob_ownership | lookup ip_summary.csv "IP Address" OUTPUT "OS CPE" 
| lookup test_data_ip_changes.csv "IP Address" OUTPUTNEW "IP Address" as "IP Differences Found" | where isnull("IP Differences Found")
| search application="$application$" Plugin!="19506" | `ownership`
| search ownership="$field3$" Plugin=$field4$
| rename ownership as Ownership
| table"IP Address","MAC Address","DNS Name","NetBIOS Name", Ownership

I get the following error:

“Error in ‘lookup’ command: Could not find all of the specified fields in the lookup table"

I should get the added IP addresses because the output of the first search does not have them. I should get the following:

192.168.1.245 01:50:56:94:45:b8
192.168.1.244 02:60:66:84:45:b10
192.168.1.243 05:51:56:64:13:b15

Re: How to compare the output of one lookup to another lookup csv error

harsmarvania57 — Mon, 17 Sep 2018 15:44:43 GMT

It looks like you are missing which field needs to compare with source data and lookup data in below portion of your second query.

| lookup ip_summary.csv "IP Address" OUTPUT "OS CPE" 
| lookup test_data_ip_changes.csv "IP Address" OUTPUTNEW "IP Address" as "IP Differences Found" | where isnull("IP Differences Found")

It will be something like this

| lookup ip_summary.csv ip as "IP Address" OUTPUT "OS CPE" 
| lookup test_data_ip_changes.csv ip as "IP Address" OUTPUTNEW "IP Address" as "IP Differences Found" | where isnull("IP Differences Found")

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:16:29 GMT

That did not work =(. I still get:

“Error in ‘lookup’ command: Could not find all of the specified destination fields in the lookup table"

lookup test_data_ip_changes.csv and ip_summary.csv dont not have "ip" it uses "IP Address" in the field.

Re: How to compare the output of one lookup to another lookup csv error

harsmarvania57 — Mon, 17 Sep 2018 19:18:21 GMT

I gave an example, if you don't have ip field in your lookup file then you need to map correct field from lookup file.

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Mon, 17 Sep 2018 20:07:31 GMT

Yes, I did change the syntax. That would the same search I had before =(

| lookup test_data_ip_changes.csv "IP Address" OUTPUTNEW "IP Address" as "IP Differences Found" | where isnull("IP Differences Found")

Re: How to compare the output of one lookup to another lookup csv error

rvany — Tue, 18 Sep 2018 08:56:54 GMT

Could you please provide the header lines of your lookup files?
Do you have defined automatic lookups?

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:16:49 GMT

server_ip_list.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
assets.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
lookup server_ip_list.csv - ip,MAC Address,DNS Name,NetBIOS Name,bob_ownership
lookup ip_summary.csv - ip address, MAC Address,DNS Name,NetBIOS Name
test_data_ip_changes.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership

There are some slight differences, but they mostly the same. I know the test_data_ip_changes.csv has 3 more ips/MAC address that are not in the others, so I'm trying to get Splunk to display just the 3 new that are in test_data_ip_changes.csv.

Re: How to compare the output of one lookup to another lookup csv error

rvany — Wed, 19 Sep 2018 12:26:34 GMT

In lookup ip_summary.csv - do you have "ip address" or "IP Address"?

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:17:15 GMT

"IP Address"

I has a copy/paste error:

server_ip_list.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
assets.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
lookup server_ip_list.csv - Ip,MAC Address,DNS Name,NetBIOS Name,bob_ownership
lookup ip_summary.csv - IP Address, MAC Address,DNS Name,NetBIOS Name
test_data_ip_changes.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership

Ignore Ownership, we are just trying to get IP Address and MAC Address.

Re: How to compare the output of one lookup to another lookup csv error

rvany — Tue, 29 Sep 2020 21:17:18 GMT

I just realize, there's also a "application_ip_list" - seems to be a lookup definition (no .csv appended). What fields are inside this one? All "ip" fields in all csv files are lowercase?

And - you have server_ip_list.csv two times in your post (one with a preceding "lookup") but with a different last fieldname: Ownership vs. bob_ownership

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:17:21 GMT

Sorry it was a typo:

assest.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
all_ vulnerabilities.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
application_ip_list.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
server_ip_list.csv - ip,MAC Address,DNS Name,NetBIOS Name,Ownership
ip_summary.csv - IP Address,MAC Address,DNS Name,NetBIOS Name,Ownership
test_data_ip_changes.csv - IP Address,MAC Address,DNS Name,NetBIOS Name,Ownership

Re: How to compare the output of one lookup to another lookup csv error

rvany — Tue, 29 Sep 2020 21:17:23 GMT

But if you are outputting ownership or Ownership or bob_ownership - it has to exist in your lookup file and it has to exist in the right case.

So if you have Ownership in server_ip_list.csv and you output bob_ownership - that's it where your error comes from.

So you couldn't ignore it...

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:17:26 GMT

| inputlookup assets.csv | inputlookup append=true all_vulnerabilities.csv | lookup application_ip_list ip as "IP Address" OUTPUT application 
| search application="$application$" Plugin!="19506" | `ownership`
| search ownership="*" Plugin=*
| rename ownership as Ownership
| table"IP Address","MAC Address","DNS Name","NetBIOS Name", Ownership

Okay, so chopped the search up to make it a little easier to understand. The above revised search returns:
IP Address Mac Address DNS Name NetBIOS Name Ownership
192.168.1.241 01:50:56:94:46:b3 test.com EXTERNAL\TEST TEST-APPS
192.168.1.240 02:60:66:84:45:b11 test.com TEST-APPS
192.168.1.239 05:51:56:64:13:b16 test.com TEST-APPS

Headers of CSV's are follows:
assets.csv ip,MAC Address,DNS Name,NetBIOS Name,Ownership
application_ip_list Ip,MAC Address,DNS Name,NetBIOS Name,application

I want to compare the results to a test csv I made called test_data_ip_changes.csv with the same headers. I want a search to display just the ips/MAC Addresses that are not in the above revised search.

The headers of the test_data_ip_changes.csv are as follows:
test_data_ip_changes.csv ip,MAC Address,DNS Name,NetBIOS Name,application

I tired the following below:

| inputlookup assets.csv | inputlookup append=true all_vulnerabilities.csv | lookup  application_ip_list ip as "IP Address" OUTPUT application | lookup test_data_ip_changes.csv ip OUTPUTNEW ip as TestField | rename ip as "IP Diffference Found" | where isnull(TestField)
| search application="$application$"  Plugin!="19506" | `ownership`
| search ownership="*" Plugin=*
| rename ownership as Ownership
| table "IP Diffference Found" , "MAC Address"

"No Results Found"

The following should have been shown:

192.168.1.245 01:50:56:94:45:b8
192.168.1.244 02:60:66:84:45:b10
192.168.1.243 05:51:56:64:13:b15

Re: How to compare the output of one lookup to another lookup csv error

rvany — Wed, 19 Sep 2018 19:39:37 GMT

Ok, that's a step further - the lookup-error has gone 🙂

But there are still a lot of missing details:

$application$ is a token from some dashboard input, right?
where does the Plugin-field/-value come from?
what's the content of ownership
the first field of application_ip_listis ip, and not Ip, right?

I have to rebuild this scenario in my test environment to give you correct advice. If you give all the necessary details with accuracy I am willing to investigate further. But I'm not going on wasting my time guessing. Sorry.

Take your time and put together all the information one needs to reproduce your specific scenario, make it as short as possible (is "all_vulnerabilities.csv" really needed? Is "Plugin"?) and I will be happy to help you as far as I can - I'm still learning every day 😉

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Tue, 29 Sep 2020 21:17:51 GMT

Yes, I think we are getting closer! 😃

I chopped the search even more to simply it even more:

| inputlookup assets.csv | inputlookup append=true all_vulnerabilities.csv | lookup         application_ip_list ip as "IP Address" OUTPUT application 
 | search application="$application$"  `ownership`
 | rename ownership as Ownership
 | table"IP Address","MAC Address","DNS Name","NetBIOS Name", Ownership

The above results return:

IP Address Mac Address DNS Name NetBIOS Name Ownership
192.168.1.241 01:50:56:94:46:b3 test.com EXTERNAL\TEST TEST-APPS
192.168.1.240 02:60:66:84:45:b11 test.com TEST-APPS
192.168.1.239 05:51:56:64:13:b16 test.com TEST-APPS

To answer your questions:

• $application$ is a token from some dashboard input, right? Yes, so when application is selected from a dropdown, it populates that system and it attributes .
• where does the Plugin-field/-value come from? I took that out of the update search (before it showed plugins)
• what's the content of ownership? I took that out, in the revised search it just returns the owner of the system.
• what's the content of ownership the first field of application_ip_listis ip, and not Ip,
right? yes, ip

 | inputlookup assets.csv | inputlookup append=true all_vulnerabilities.csv | lookup  application_ip_list ip as "IP Address" OUTPUT application | lookup test_data_ip_changes.csv ip OUTPUTNEW ip as TestField | rename ip as "IP Diffference Found" | where isnull(TestField)
 | search application="$application$"  | `ownership`
 | rename ownership as Ownership
 | table "IP Diffference Found" , "MAC Address"

"No Results Found"

The following should have been shown (the 3 new ips from test_data_ip_changes.csv):
192.168.1.245 01:50:56:94:45:b8
192.168.1.244 02:60:66:84:45:b10
192.168.1.243 05:51:56:64:13:b15

Re: How to compare the output of one lookup to another lookup csv error

rvany — Fri, 21 Sep 2018 06:30:31 GMT

Ok, let's do it step by step - the first three statements of your spl are:

| inputlookup assets.csv
| inputlookup append=true all_vulnerabilities.csv
| lookup  application_ip_list ip as "IP Address" OUTPUT application

This only gives complete results if "IP Address" is either in assets.csv or in all_vulnerabilities.csv. According to your information above there is no "IP Address" in either of these files (but only ip).

Re: How to compare the output of one lookup to another lookup csv error

rvany — Fri, 21 Sep 2018 09:12:35 GMT

To make it really simple:

assets1.csv:

ip,mac_address,dns_name
192.168.1.241,01:50:56:94:46:b3,a.test.com
192.168.1.240,02:60:66:84:45:b1,b.test.com
192.168.1.239,05:51:56:64:13:b6,c.test.com

test_data_ip_changes.csv:

ip,mac_address,dns_name
192.168.1.241,01:50:56:94:46:b3,a.test.com
192.168.1.240,02:60:66:84:45:b1,b.test.com
192.168.1.239,05:51:56:64:13:b6,c.test.com
192.168.1.111,00:11:22:33:44:01,a.test.de
192.168.1.112,00:11:22:33:44:02,b.test.de
192.168.1.113,00:11:22:33:44:03,c.test.de

spl:

| inputlookup test_data_ip_changes.csv
| lookup assets1.csv ip outputnew ip as ip1, mac_address as ma1
| where isnull(ip1)

The way you formed your spl:

| lookup test_data_ip_changes.csv ip OUTPUTNEW ip as TestField
| where isnull(TestField)

Splunk only picks up the already existing ip-addresses vom your test_data...csv

Re: How to compare the output of one lookup to another lookup csv error

umdterps02 — Mon, 24 Sep 2018 15:07:57 GMT

I need more time to test your SPL because we are having issues with our server. I will update this thread once issues are resolved. 😃