Splunk Search

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

GEB
Explorer

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?

Labels (1)
Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

GEB
Explorer

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
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...