topic Re: How to search and table IP addresses to see which ones are active? in Splunk Search

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

m8733 — Tue, 15 Jul 2014 17:56:18 GMT

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?

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

martin_mueller — Tue, 15 Jul 2014 17:58:14 GMT

Could you post some sample data?

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

m8733 — Tue, 15 Jul 2014 18:12:04 GMT

Sample data?
That's my query
index=DEVICE (source device) | table srcip IP OR IP OR IP
I am getting a table with source IP address from each even. I am trying to remove duplicates.

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

martin_mueller — Tue, 15 Jul 2014 18:14:10 GMT

I'm asking for sample data because I don't understand what ... | table srcip IP OR IP OR IP is meant to do. Neither do I know what kind of data you have in your DEVICE index.

Take a few lines of your source file, anonymize sensitive data, and paste it here.

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

lguinn2 — Tue, 15 Jul 2014 18:57:08 GMT

[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

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

reed_kelly — Tue, 15 Jul 2014 19:07:44 GMT

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.

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

m8733 — Tue, 15 Jul 2014 19:26:58 GMT

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?

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

reed_kelly — Tue, 15 Jul 2014 19:45:42 GMT

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

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

reed_kelly — Tue, 15 Jul 2014 19:53:08 GMT

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

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

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

m8733 — Tue, 15 Jul 2014 20:00:38 GMT

Thank for your reply. How would the query look like if I want to type in the IP addresses instead of using .csv file?

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

martin_mueller — Tue, 15 Jul 2014 20:03:26 GMT

Like this:

index=DEVICE (src_ip="1.1.1.1" OR src_ip="1.1.1.2" OR src_ip="1.1.1.3" OR ...) | stats count by src_ip

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

m8733 — Tue, 15 Jul 2014 20:22:15 GMT

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

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

m8733 — Tue, 15 Jul 2014 20:22:55 GMT

I ran this one as well. Thanks.

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

m8733 — Wed, 16 Jul 2014 12:05:01 GMT

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

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

m8733 — Wed, 16 Jul 2014 12:07:55 GMT

Didn't work. No results.

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

martin_mueller — Wed, 16 Jul 2014 12:18:36 GMT

...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?

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

m8733 — Wed, 16 Jul 2014 12:56:06 GMT

srcip which is what I need

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

martin_mueller — Wed, 16 Jul 2014 12:57:00 GMT

Did you remove the underscores from my query?

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

m8733 — Wed, 16 Jul 2014 13:06:51 GMT

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

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

m8733 — Wed, 16 Jul 2014 13:11:40 GMT

Didn't work.