Splunk Search

Wildcard field names for "search" or "where"

redc
Builder

I'm looking at a count of server events over time and need to find all servers where there are more than 1 event per month. This means I need to be able to search a series of columns (whose headers change each month).

Search:
index="myindex" sourcetype="test" | search Event=Type1 | timechart span=1mon limit=0 count by "Server Name" | eval Time=strftime(_time, "%m/%Y") | transpose 6 column_name="Server Name" header_field=Time

A sample of results:
Server Name 09/2017 10/2017 11/2017 12/2017 01/2018 02/2018
Server1 1 1 2 1 1 1
Server2 2 1 0 0 0 0
Server3 1 2 1 2 1 1
Server4 1 1 1 0 0 0

In this sample, Server1, Server2, and Server3 would be returned, but not Server4.

I've tried using a foreach:
| foreach "/" [eval newfield=<>] | where newfield>1

But that only returns values where December (12 - being the largest number, and therefore the last field to be looped through in the foreach command) is 2; in the sample, Server3 only. I've tried putting the where command inside the brackets and I get no results. I've also tried adding a <> and putting the where command inside the brackets, but that also returns no results. I've also tried using the search command instead of the where command (both inside and outside the brackets), to no avail.

How can I get it to search each month/year column and return any results that match on any column, not just on the last one to be looped through?

(I could have it use the month name and drop the year, then hardcode as "search (January>1 OR February>1 OR ... December>1)", since I won't have it returning more than 12 months' of data at a time, but since it will wrap around the end of years, I'd rather have month AND year.)

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Try this to flatten out the chart, then you can work with the individual records...

| untable ServerName month count
| where count > 0 

However. you would be better off to go up a few lines and fix the query from there.

index="myindex" sourcetype="test" Event=Type1 
| bin _time span=1mon
| stats count by _time "Server Name"
| where count>1
| eval month=strftime(_time,"%Y-%m")." count=".count
| sort 0 "Server Name" - month
| stats values(month) as month by  "Server Name"

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try this to flatten out the chart, then you can work with the individual records...

| untable ServerName month count
| where count > 0 

However. you would be better off to go up a few lines and fix the query from there.

index="myindex" sourcetype="test" Event=Type1 
| bin _time span=1mon
| stats count by _time "Server Name"
| where count>1
| eval month=strftime(_time,"%Y-%m")." count=".count
| sort 0 "Server Name" - month
| stats values(month) as month by  "Server Name"
0 Karma

redc
Builder

Well, option #2 wouldn't have quite worked for the format I needed (there are some other things that get pulled in that were extraneous to the problem I was trying to solve), but it got me in the ballpark. Final result:

index="myindex" sourcetype="test"
 | search Event=Type1
 | bin _time span=1mon
 | stats count by _time "Server Name"
 | search count>1
 | eval Time=strftime(_time, "%Y-%m")
 | chart limit=12 values(count) as count over "Server Name" by Time

This shows just the servers with count>1 and sorts the columns in "time" order (oldest month first, newest month last).

DalJeanis
SplunkTrust
SplunkTrust

@recd - awesome. Glad to be able to help.

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...