Remove columns that meet a criteria

[ttanasovski]

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?

[capnjosh]

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.

[ttanasovski]

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?

[ttanasovski]

Duh! mvexpand does that, doesn't it? bah. Well at least it's working now.

[gkanapathy]

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.)