Splunk Search

Why are fields that exist in the search result not recognized in the eval if statement?

Communicator

I have the following search:

city=* store=*  | stats values(store) by city | mvexpand store | eval Role=case(store LIKE "%frt%", "FT", store LIKE "%byt%", "BT", store LIKE "%bea%", "BA", store LIKE "%gwt%", "GT") | eval Status=if(openStatus==1, "Open", "Closed") | stats list(store) AS store list(Role) AS role list(Status) as Status BY city

There is a problem with eval Status, I am 100% sure of that the searched query indexes events that contains the field "openStatus" which has the value of either 0 or 1 but for some reason the field Status always evaluates to "Closed" even though I know that a certain store is open and that the field should evaluate to "Open".

Any eval I do containing city, store or Role works (with operations such as LIKE etc), but whenever I try to reference a field that is not mentioned in the search (and I KNOW it is in the event search result), it doesn't work. It's as if it is not recognized. Why?

Tags (3)
1 Solution

SplunkTrust
SplunkTrust

A wild guess. try this

city=* store=* | eval store=store."#".openStatus | stats values(store) by city | mvexpand store | rex field=store "(?<store>.*)#(?<openStatus>.*)" | eval Role=case(store LIKE "%frt%", "FT", store LIKE "%byt%", "BT", store LIKE "%bea%", "BA", store LIKE "%gwt%", "GT") | eval Status=if(openStatus==1, "Open", "Closed") | stats list(store) AS store list(Role) AS role list(Status) as Status BY city

View solution in original post

SplunkTrust
SplunkTrust

A wild guess. try this

city=* store=* | eval store=store."#".openStatus | stats values(store) by city | mvexpand store | rex field=store "(?<store>.*)#(?<openStatus>.*)" | eval Role=case(store LIKE "%frt%", "FT", store LIKE "%byt%", "BT", store LIKE "%bea%", "BA", store LIKE "%gwt%", "GT") | eval Status=if(openStatus==1, "Open", "Closed") | stats list(store) AS store list(Role) AS role list(Status) as Status BY city

View solution in original post

Communicator

This did extract the information but the store column turned out like this: "A2frt#1", "B5byt#0" and so on. The open status was sort of concatenated with the value of the store in the store column. But it is a step in the right way although I have little experience using rex .

0 Karma

Communicator

Never mind. I solved it. Thank you!

0 Karma

SplunkTrust
SplunkTrust

The first stats command is filtering out all fields except store and city so there is no openStatus to evaluate. Since the stats values(store) by city | mvexpand store commands appear to add little value, I suggest removing them so you'll have access to openStatus and the other fields in the event.

---
If this reply helps you, an upvote would be appreciated.

Communicator

Ah, perfect, this was what I suspected. stats values(store) by city | mvexpand store lists the stores by city and enables the eval Role-statement... any ideas on how to maintain this structure but still beeing able to evaluate fields that are not a part of stats values?

0 Karma

SplunkTrust
SplunkTrust

I still don't understand the need for the first stats command, but you know your data better than I do. You might try adding list(openStatus) to the stats command to see if that helps.

---
If this reply helps you, an upvote would be appreciated.
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!