topic How to use the where command in Splunk Search

How to use the where command

nielsfranken198 — Tue, 14 Nov 2017 17:28:14 GMT

source="mhn-splunk.log" | where dest like "88ea2fb8-b579-11e7-8239-ce584c37994e" replace 127.0.0.1 WITH 37.139.29.33 IN dest | where dest like "fb60d976-b578-11e7-8239-ce584c37994e" replace 127.0.0.1 WITH 139.59.57.136 IN dest | where dest like "574e6d3e-b579-11e7-8239-ce584c37994e" replace 127.0.0.1 WITH 159.203.116.197 IN dest | top dest | fields dest, count

why doesn't this work?

Re: How to use the where command

richgalloway — Tue, 14 Nov 2017 18:57:05 GMT

The where command accepts a single eval expression. Your query uses two expressions - like and replace. What's more, your query uses the replace command rather than the eval function of the same name (yes, it can be confusing to have two similar behaviors with the same name).

Your query can be replaced with either

... | where dest like "88ea2fb8-b579-11e7-8239-ce584c37994e" | replace 127.0.0.1 WITH 37.139.29.33 IN dest| ...

... |where dest like "88ea2fb8-b579-11e7-8239-ce584c37994e" | eval dest=replace(dest, "127\.0\.0\.1", "37\.139\.29\.33") |...

Be aware the where command discards events that don't match so it doesn't make sense to have multiple where dest like commands since only dest values that match the first expression will be passed to subsequent commands.

Also, it doesn't make sense to compare the dest field to a GUID and then compare it to an IP address. In a single event, the field will contain one or the other.

Re: How to use the where command

nielsfranken198 — Tue, 14 Nov 2017 19:38:19 GMT

so if i am understanding correctly i can't do multiple where, replace's and eval's in one query?

Re: How to use the where command

nielsfranken198 — Tue, 14 Nov 2017 19:39:44 GMT

can a where never be used with replace or eval in same nested query

Re: How to use the where command

niketn — Tue, 14 Nov 2017 21:22:14 GMT

@nielsfranken1989, seems like you are looking for case() eval function, which allows you to evaluate multiple conditions and set the values according to the same. While you have used replace command the same can be used as evaluation function as well. Refer to the documentation:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/ConditionalFunctions#case.28X.2C.22Y.22.2C....29
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/TextFunctions#replace.28X.2CY.2CZ.29

Following is a run anywhere search with some mock dest values. Commands till mvexpand just generate mock data. You would need to place your own base search to get dest field.

| makeresults
| eval dest="88ea2fb8-b579-11e7-8239-ce584c37994e 127.0.0.1;fb60d976-b578-11e7-8239-ce584c37994e 127.0.0.1;574e6d3e-b579-11e7-8239-ce584c37994e 127.0.0.1;11aa2bb3-c456-78d9-1011-ef121g31415h 127.0.0.1;"
| makemv dest delim=";"
| mvexpand dest
| top dest  showperc=f
| eval dest=case(match(dest,"88ea2fb8-b579-11e7-8239-ce584c37994e"), replace(dest,"127.0.0.1","37.139.29.33"),
                 match(dest,"fb60d976-b578-11e7-8239-ce584c37994e"), replace (dest,"127.0.0.1","139.59.57.136"),
                 match(dest,"574e6d3e-b579-11e7-8239-ce584c37994e"), replace(dest,"127.0.0.1","159.203.116.197"),
                 true(),dest)

PS:
As you would notice, I have performed top command before replace. Transforming command should always be placed before streaming command if possible. In simple words, lets say you have 100 events and the top command reduces the same to 10 unique destinations (dest), then eval will be performed only on 10 rows instead of 100. So you can image how much performance improvement will be there if you have thousands or millions of events.

Top command has a parameter called showperc which can be set to either f or false to hide percent column. Hence you would not need a separate fields pipe in the end. 🙂

Re: How to use the where command

richgalloway — Wed, 15 Nov 2017 12:56:16 GMT

Yes, you can use multiple replace and eval commands in the same query. You can also use multiple where commands, with care. Think of each where command as being ANDed with the others. It doesn't make sense to say "where dest=foo | where dest=bar" since a field can only have one value. You can, of course, say "where dest=foo | where src=baz".

@niketnilay has a good suggestion to use case to combine your conditions.

Re: How to use the where command

richgalloway — Wed, 15 Nov 2017 15:27:51 GMT

There are no restrictions on using where, replace, and eval in the same query.
The "where foo replace x with y in z" construct is not valid SPL, however.