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=<
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.)
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"
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"
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).
@recd - awesome. Glad to be able to help.