Splunk Search

How to use the where command

nielsfranken198
Engager

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?

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

nielsfranken198
Engager

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

nielsfranken198
Engager

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...