Excel TEXTJOIN

One type of efficient splunk search for ips is to use TERM instead of listing the ips. The ips contain a period, which splunk interprets differently, so we use the word “TERM”. When we get a list of ips, they are not wrapped in this format. There are a few excel hacks that will give you a list like this:

index=firewall TERM(1.2.3.4) OR TERM(2.3.4.5) OR TERM(4.3.2.1)

TEXTJOIN

Text join combines things.

The one we are going to focus on is “TEXTJOIN”. if you have a list of ips in Column A, then you put this formula in B1. It will render the string you need like above.

We are going to use textjoin to combine the word “TERM() OR " with the data we have in column A (A series of IP addresses)

We are combining TEXTJOIN with Filter and skipping any blanks with the < and > tags.

The actual wrapper on our ip is the literal word “TERM() “:

Term is Not Excel

"TERM(" & & ")"

Filtering

We only want to see specific cells, so we use a filter. The > and < tell us to skip blanks. Here we are looking at the first 300 rows to build our list of ips:

FILTER(A1:A300,A1:A300<>"")

Full Formula for List to CSV

=TEXTJOIN(", ", TRUE, A1:A300)

Full Formula For Splunk

Put in B1 and it will ready from A1:A300

=TEXTJOIN(" OR ", TRUE, "TERM(" & FILTER(A1:A300,A1:A300<>"") & ")") 

Textjoin is much like concatenate, which is also worth reading about.