I have a query that digs through Windows perf data:
index=perfjava host=blah ((sourcetype="Perfmon:CPULoad" AND instance=_Total) OR (sourcetype="Perfmon:RunningProcesses" AND instance!=_Total)) counter="% Processor Time" |timechart span=1m limit=0 avg(Value) as CPU by instance |where VALUE_Total>85
This returns a series of occurrences where the total CPU is over 85%. The header looks something like this:
_time VALUE__Total process1, process2, process3, process4
What I'd like to do is take all of the columns and remove any that do not have a row (any row) that has a value over 20. In other words, if one row exists with a value over 20, the column should stay.
Should I be doing something like a transpose, filter, and then transpose again?
Try this, if I understand the question correctly (and yes I realize this is necroing an old post, but this post kept coming up in my searches, so I figure it's doing that for others):
for the fields you want to ignore in your final output, set the fields to null() that you don't want to show up, e.g.
| eval process3a=if(process3<20,null(),process3)
| fields - process3
| rename process3a as process3
This should have the effect you're looking for: if there are no rows in the result with a non-null value for process3, then the process3 column won't even show up.
I wound up doing the following - this allows multiple hosts:
index=perfjava ((sourcetype="Perfmon:CPULoad" instance=_Total) OR (sourcetype="Perfmon:RunningProcesses" AND instance!=_Total AND instance!=Idle)) counter="% Processor Time" |eval CPU=if(instance=="_Total",Value,null())|lookup numcpusperfjava.csv host |eval Value=if(instance=="_Total",Value,(Value/CPUs)) |bin _time span=1m |where Value>=10 |eval Value=round(Value,1)|eval Procs=if(instance=="_Total",null(),(instance+" = "+Value)) |stats avg(CPU) as CPU Values(Procs) as Processes by _time host|eval CPU=round(CPU,1) |where CPU>85 | eval time=strftime(_time, "%m/%d/%y %H:%M")
numcpus.csv is a lookup table that is run nightly:
index=perfjava sourcetype="Perfmon:RunningProcesses" counter="% Processor Time" instance=_Total|stats max(Value) as CPUs by host |eval CPUs=round((CPUs/100),0) |outputlookup numcpus.csv
The timestr at the end is just for Excel compatibility.
I'm not sure if there's a better way to do this. I mean ideally, the contents of Procs that I am setting should each create their own row in the table. Is there a way to split a multi-value element into multiple rows rather than just calling Values() on it?
Duh! mvexpand does that, doesn't it? bah. Well at least it's working now.
Probably the better thing to do is not use timechart
and instead use a combination of makecontinuous span-=1m _time | stats avg(Value) as CPU by _time,instance
, then filter with a more complex where
clause, then use xyseries _time instance CPU
to rotate the rows. (Or you could skip the xyseries command if the resulting format from stats
is fine.)