topic Re: Subsearch - clause to match values with main search in Splunk Search

Subsearch - clause to match values with main search

splunk_zen — Fri, 25 Jan 2013 11:49:23 GMT

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

Re: Subsearch - clause to match values with main search

martin_mueller — Fri, 25 Jan 2013 11:55:59 GMT

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

Re: Subsearch - clause to match values with main search

splunk_zen — Fri, 25 Jan 2013 16:50:00 GMT

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

Re: Subsearch - clause to match values with main search

martin_mueller — Fri, 25 Jan 2013 17:03:02 GMT

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.

Re: Subsearch - clause to match values with main search

splunk_zen — Fri, 25 Jan 2013 19:02:35 GMT

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
]

Re: Subsearch - clause to match values with main search

martin_mueller — Fri, 25 Jan 2013 20:42:16 GMT

How would you filter by the fields if they didn't exist?