eval case like only populates first row of evaluated field

[ohlafl]

I have the following query:

city=* store=*  | stats values(store) by city | eval Role=case(store LIKE "%frt%", "FT", store LIKE "%byt%", "BT", store LIKE "%bea%", "BA", store LIKE "%gwt%", "GT")

This results in:

city                store                role

london                "HT10gwt1"                  GT
                      "HT55gwt2"                              
                      "Ogwt22"                            
                      "ASbet44" 

paris                 "PPgwt11"                  BT
                      "OBbea2"                        
                      ...                         
                      ... etc

Why isn't role getting evaluated properly and getting populated for each store? What am I doing wrong? Every store should have a role

[woodcock · Accepted solution]

The answer is because field store is now a mutlivalued field and case exits once the first condition is true so you need something that evaluates on every test. This will work:

 city=* store=*  | stats values(store) by city | eval Role=mvappend(if(like(store, "%frt%"), "FT", null()), if(like(store, "%byt%"), "BT", null()), if(like(store, "%bea%"), "BA", null()), if(like(store, "%gwt%"), "GT", null()))

But I suspect you will be displeased with that and are probably looking for this:

 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")

[ohlafl]

Thank you, the later example worked as expected but it would be really neat if the result would group by city (as of now each store is within its on cell which I guess is expected since you did an mvexpand). The first example did more lines of role but not for all stores and not always the right role.

[woodcock]

This will do it:

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") | stats list(store) AS store list(Role) AS role BY city

[ohlafl]

I actually have a new question in relation to this, I want to expand the chart by giving each store a Status, the value of "Status" is defined by a field within the event (1 = open, 0 = closed). I would of thought that something like...

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=case(openStatus=1, "Open", openStatus=0, "Closed") | stats list(store) AS store list(Role) AS role list(Status) as Status BY city

... would work but it doesn't, it seems as if the value of openStatus is not properly passed, what am I missing?

[woodcock]

Try this (upvote this comment if it works):

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=case(openStatus=1, "Open", openStatus=0, "Closed") | stats list(store) AS store list(Role) AS role list(Status) as Status BY city

[ohlafl]

Sorry I was really tired yesterday, the "eval Status=" was supposed to be in the original comment as it makes no difference.

[woodcock]

Then you do not have a field called openStatus; check you fields and names (casing).

[ohlafl]

The weird thing is that I absolutely do, I have checked and double checked an even though there are existing events with that field in the exact writing the above code will not evaluate and produce the wanted result.

Edit: It appears as if I can only evaluate fields mentioned within the query such as city, store and role, but when I reference fields that I do know exist in the events and that are parsed as fields in Splunk nothing happens. Even running an [ eval Status=if(openStatus>-1, "yes", "no") ] always produces "no"...

[ohlafl]

Awesome. Worked like a charm. Thank you.