How to use the where command

[nielsfranken198]

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?

[niketn]

@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....
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/TextFunctions#replace.28X.2CY.2CZ...

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

[richgalloway]

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

or

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

[nielsfranken198]

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

[richgalloway]

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.

[nielsfranken198]

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

[richgalloway]

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.