How to search and table IP addresses to see which ones are active?

[m8733]

Hello,
I am trying to do a complex search for almost 500 IP addresses to see which ones are active. My query looks like this:
index=DEVICE | table srcip IP OR IP OR IP and so on.
However; the table with the source iP addresses that I got back has IP address for each event. Is there anyone to get the IP address only once to check if it's active or not? Also, I am not sure if there is any efficient query that I could use instead of all ORs?

[reed_kelly]

index=DEVICE |dedup srcip |...

will eliminate duplicate IPs.

As to the searching for a big list of addresses, you want to look into "lookup" tables or "input CSV" files. By the way, you can use CIDR blocks in the search, lookup file or input CSV file.

You can create an input CSV file by putting a file in the:
$SPLUNK_HOME/var/run/splunk/
folder and call it something like myips.csv.

The first line of the file should be srcip and each line after that can be an IP address. Then you can limit your search as follows:

index=DEVICE [|inputcsv "myips.csv"] | dedup srcip | table srcip

This will give you a list of the srcip entries that matched those in the file and only print one of each. You can do something in the reverse to find those that are missing from your file:

|inputcsv "myips.csv" NOT [index=DEVICE |dedup srcip|fields srcip]

This last search will work for a small time range, but the subsearch may time out on longer ones. A more complete solution to that is a bit more involved, but can be done.

[lguinn2]

(srcip=IP1 OR IP2 OR IP3)

is not the same as

(srcip=IP1 OR srcip=IP2 OR srcip=IP3)

if what you want is the second one - then that is what you must type.

[Jeff_Lightly_Sp]

Whatever your source file is that feeds into the DEVICE index. Perhaps its a .csv or a .txt file that lists all of those IPs??

[m8733]

How do I get those data or where from?

[Jeff_Lightly_Sp]

Couple of ideas:

1 - Post some anonymized data per Martin_Mueller's suggestion so that we can get a better idea of what you are working with

and/or

2 - Try this search (to verify whether or not the correct index is being referenced)

• (srcip=IP1 OR IP2 OR IP3) | dedup srcip | table srcip

[m8733]

Like the second one.
index=DEVICE (srcip=IP1 OR IP2 OR IP3) | dedup srcip | table srcip

[Jeff_Lightly_Sp]

Quick question and just to help clarify - Does your search look like this:

index=DEVICE (srcip=IP1 OR srcip=IP2 OR srcip=IP3) | dedup srcip | table srcip

or, does it look like this:

index=DEVICE (srcip=IP1 OR IP2 OR IP3) | dedup srcip | table srcip

[m8733]

Didn't work. No results.

[m8733]

I ran this
index=DEVICE (srcip=IP1 OR ...) | dedup srcip | table srcip
I'll see if it works. Thanks

[reed_kelly]

To upload a lookup file, you can follow the instructions here: http://docs.splunk.com/Documentation/Splunk/latest/SearchTutorial/Usefieldlookups#Upload_the_lookup_...

Is your field srcip or src_ip (with an underscore)? In the later case, follow lguinn's instructions along with the above docs.

[reed_kelly]

The CSV input file or lookup file as lguinn suggested are the way to go. If you cannot use either of those approaches, then searching for srcip in a large set should be done at the front of the search. You also have to put OR conditions in parenthesis. Something like:

index=DEVICE (srcip=IP1 OR srcip=IP2 OR ... OR srcip=IP500)

The dedup command requires another pipe | as follows:

index=DEVICE (srcip=IP1 OR ...) | dedup srcip

To put the results in a table, you need to add the table command:

index=DEVICE (srcip=IP1 OR ...) | dedup srcip | table srcip

[m8733]

Thanks for the all replies.
Ok, so my query now looks like this
index=DEVICE | dedup srcip IP OR IP OR IP
When I start searching,I get "Error in 'dedup' command: The field 'OR' is specified multiple times. It should only be specified once. " I am not sure why?

[lguinn2]

[updated to replace src_ip with srcip]

Try this

index=DEVICE | stats count by srcip

assuming that srcip is an actual field that represents the IP addresses

If you need to compare the list of IP addresses in the index with a fixed list of 500 IPs, then my suggestion is this:

First, put the IP addresses in a lookup table

Then, use the following to restrict your search to only those IP addresses

index=DEVICE [ | inputlookup ip_lookup.csv ] | stats count by srcip

which assumes that ip_lookup.csv contains a list of srcip addresses like so

srcip
10.1.3.154
192.168.0.1
135.15.24.79
etc

[m8733]

"Unknown sid"

[m8733]

Didn't work.

[m8733]

No, I'll do that now. I am new to this.

[martin_mueller]

Did you remove the underscores from my query?

[m8733]

srcip which is what I need

[martin_mueller]

...which is the reason why I asked for sample data. Do your events have a field for the IP, and what's the name of that field?

[m8733]

"No results found." It didn't work. It should have found results.