I'm having trouble using a condition to match a subsearch results with the main search ones,
running each one individually, the subsearch returns,
BusyHourDay BusyHour
13-01-19 18
13-01-23 13
13-01-24 13
....
while the main search (excluding the where clause) returns,
day AvgUsedCpuPct
13-01-23 35.846345
13-01-24 48.795962
If I statically force one of the subsearch output lines in the where clause: day="13-01-24" AND date_hour=18
MAIN_SEARCH | append
[search SUBSEARCH
| rename day AS BusyHourDay, date_hour AS BusyHour
| fields BusyHourDay BusyHour | sort BusyHourDay
]
| convert timeformat="%y-%m-%d" ctime(_time) as day
| where day="13-01-24" AND date_hour=18
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day
I do get the expected output,
day AvgUsedCpuPct
13-01-24 48.795962
but I'm failing to get any output if using,
| where day=BusyHourDay AND date_hour=BusyHour
how should I rewrite it to cross the subsearch output with the main one and get a chart of CPU Usage over each day Busiest Hour?
EDIT
Ended up using,
MAIN_SEARCH [search SUB_SEARCH
| fields date_month, date_mday, date_hour
]
| eval CpuUsedPct=USED_CPU
| timechart avg(CpuUsedPct) as AvgUsedCpuPct
It looks to me as if you wanted to use the results of the subsearch as a filter for the main search, not to append the results as new events to the main search. Consider this: http://docs.splunk.com/Documentation/Splunk/5.0.1/Search/Usesubsearchtocorrelateevents
How would you filter by the fields if they didn't exist?
Does that mean the fields returned by the subsearch must exist in the main one?
I'm getting,
Error in 'chart' command: The argument '( ( BusyHour=13 AND BusyHourDay=13-01-23 ) OR ( BusyHour=17 AND BusyHourDay=13-01-25 ) OR .... OR ( BusyHour=19 AND BusyHourDay=13-01-20 ) )' is invalid.
after modifying it to,
MAIN_SEARCH | eval CpuUsedPct=USED_CPU
| chart avg(CpuUsedPct) as AvgUsedCpuPct over BusyHourDay by BusyHour
[search SUBSEARCH
| rename day AS BusyHourDay, date_hour AS BusyHour
| fields BusyHourDay BusyHour | sort BusyHourDay
]
The subsearch returns a filter, so you do not need (and cannot) write the day=foo and date=bar filters. Just make sure the fields match, it's all in the docs.
Thanks Martin. That's exactly the goal,
but if piping the subsearch results this way, how would I pass the
day=BusyHourDay AND date_hour=BusyHourDay
Considering I've to define 'day' with the | convert function ?
does something wrong in the following expression pops out ?
MAIN_SEARCH day=BusyHourDay AND date_hour=BusyHourDay [search SUBSEARCH
| rename day AS BusyHourDay, date_hour AS BusyHour
| fields BusyHourDay BusyHour | sort BusyHourDay
]
| convert timeformat="%y-%m-%d" ctime(_time) as day
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day
?