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?
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
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
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 .
Never mind. I solved it. Thank you!
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.
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?
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.