Questions

[alnamlahk]

Hello everyone,

I have a lookup table which have multiple fields, one of the fields is IP Address of an asset. Additionally, I have an index which contain a list of IPs, that I extract with a certain query. I want to compare the two list of IPs, and then create a new column in my lookup table with ( 0 or 1) value, indicating if there is a match between the IPs.

Any idea how to do that?

Thanks

[gcusello]

Hi @alnamlahk,

I hint to see the inputlookup command (https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchReference/Inputlookup).

Anyway, you can insert the inputlookup command in a subsearch and use the results to filter the main search.

The only rule is that the field name in main and sub search must be the same, otherwise you have to rename the one in subsearch.

So if they has the same field name (e.g. "ip_address"), you could use a search like this:

index=your_index [ | inputlookup your_lookup.csv | fields ip_address ]
| ...

if instead they have a different field name (e.g. in lookup the field name is "ip"), you have to rename the second one:

index=your_index [ | inputlookup your_lookup.csv | rename ip AS ip_address 
 | fields ip_address ]
| ...

 Ciao.

Giuseppe

P.S.: see the Splunk Search Tutorial (https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchTutorial/WelcometotheSearchTutorial)

[alnamlahk]

Thank you for your reply.

I used this query: "index=logs [ | inputlookup Asset.csv | rename src AS IP_Address | fields IP_Address ] | table IP_Address | dedup IP_Address"

This will give me all the matched IPs. Now, I want to insert a new column in my lookup table which have "0 or 1" values. 

if the IP address is present in my query it will have "1" value, otherwise it will have 0 value.

[gcusello]

Hi @alnamlahk,

this is a different thing, try something like this:

index=logs 
| stats count BY IP_Address 
| append [ | inputlookup Asset.csv | rename src AS IP_Address | eval count=0 | fields IP_Address count ] 
| stata sum(count) AS total BY IP_Address 
| eval status=if/total=0,"0","1")
| rename IP_Address AS src 
| table src status
| outputlookup Asset.csv

but in this way you have in ths lookup the status field only of the last running.

Ciao.

Giuseppe

[alnamlahk]

Thank you Giuseppe, really appreciate it my friend.

I used the following query (Adding "dedup IP_Address":

"

index=logs | dedup IP_Address | stats count BY IP_Address
| append [ | inputlookup Asset.csv | rename src AS IP_Address | eval count=0 | fields IP_Address count ]
| stats sum(count) AS total BY IP_Address
| eval status=if(total=0,"0","1")
| rename IP_Address AS src
| table src status

"

but this way, it's outputting all the IPs from the index, while I want only the IPs from "Asset.csv" (Those are the IPs that I'm interested in)

[gcusello]

Hi @alnamlahk,

at first you don't need the dedup before stats!

then, if you want only the IPs from the lookup, you have to filter the results of the main search:

index=logs [ | inputlookup Asset.csv | rename src AS IP_Address | fields IP_Address ]
| rename IP_Address AS src
| stats count BY src
| append [ | inputlookup Asset.csv | eval count=0 | fields src count ]
| stats sum(count) AS total BY src
| eval status=if(total=0,"0","1")
| table src status

Ciao.

Giuseppe

[alnamlahk]

Thank you.

I have minor issue here, some of the IPs in the csv file are not present in my index, so the resulted IPs in the query less than what I have in my CSV. Any idea how to solve this? 

[gcusello]

Hi @alnamlahk,

let me understand: the problem is that many of the IPs in the lookup to monitor aren't in the index?

If this is your problem I don't know how to help you because the problem in in the sources: the only hint is analyze your sources to understand if you're receiving logs from all the target systems and, if not, why.

A search like the one I hinted is usually used just to understand if there are some machines in the perimeter that don't send logs, usually I do an alert with a similare search to notice to the administrators when a source is stopping.

Tell me if I can give you more help, otherwise, please accept the answer for the other people of community.

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉