How can I count events by location with a list of SERVERNAME's?

[GEB]

Our splunk implementation has SERVERNAME as a preset field, and there are servers in different locations, but there is no location field. How can I count errors by location? I envision something like this but cannot find a way to implement:

index=some_index "some search criteria"
| eval PODNAME="ONTARIO" if SERVERNAME IN ({list of servernames})
| eval PODNAME="GEORGIA" if SERVERNAME IN ({list of servernames})
| timechart span=30min count by PODNAME

Any ideas?

[richgalloway · Accepted solution]

You have the right idea. Here's how to do that in SPL.

index=some_index "some search criteria"
| eval PODNAME=case(in(SERVERNAME, {list of servernames}), "ONTARIO",
                    in(SERVERNAME, {list of servernames}), "GEORGIA",
                    1==1, "unknown" )
| timechart span=30min count by PODNAME

Now, when servers are added or removed you just need to edit the lookup file rather than change SPL.  I recommend the Splunk App for Lookup File Editing to modify CSV files.

There's a better way, though, since the above doesn't scale well with many locations and may become hard to maintain if the code is used in many places. Use a lookup table.

Create a CSV file with SERVERNAME and PODNAME columns then use the lookup to map server name to location.

index=some_index "some search criteria"
| lookup serverlocation.csv SERVERNAME OUTPUT PODNAME
| timechart span=30min count by PODNAME

[GEB]

Thanks, but I haven't quite got it.  The query is accepted, but the PODNAME is not being set (everything is under DANG). 

index=some_index
| eval PODNAME=case(in(SERVERNAME, "servername1", "servername2", "servername3"), "ONTARIO",
in(SERVERNAME, "servername4", "servername5", "servername6"), "GEORGIA",
1==1, "DANG" )
| timechart span=10min count by PODNAME

[bowesmana]

That looks ok, so it means your field called SERVERNAME is not exactly matching those strings. The in() eval function is an exact match. If you just do

index=some_index
| table SERVERNAME

Do you see exactly those strings?

If it's an upper/lower case think, you can do

... in(lower(SERVERNAME),"servername1"...

[GEB]

You are correct, Thanks for the solution:  The names must be in quotes AND they are case sensitive.