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

[ohlafl]

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?

[somesoni2 · Accepted solution]

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

[ohlafl]

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 .

[ohlafl]

Never mind. I solved it. Thank you!

[richgalloway]

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.

[ohlafl]

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?

[richgalloway]

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.