subsearch - Busy Hour CPU usage

[splunk_zen]

I am running into some difficulties graphing some servers Used CPU % in the Busy Hour,

(spanning at least 2 months) for that I am trying to join a subsearch to a main search,

index=resources_reporting source=*/output/ora_queries/client07/OS_metrics.csv HOST_NAME=*sdp* | eval CpuUsedPct=USED_CPU
| convert timeformat="%y-%m-%d" ctime(_time) as day
| append
[search index=resources_reporting source="*output/ora_queries/client07/SdpTermCauses.csv" PLATFORM=P1 
| convert timeformat="%y-%m-%d" ctime(_time) as day
| stats sum(SERVICE_EVOCATIONS) as ServiceEvocationsSum by date_hour day
| eventstats max(ServiceEvocationsSum) as MaxServiceEvocationsSum by day
| where ServiceEvocationsSum=MaxServiceEvocationsSum
| rename day AS BusyHourDay, date_hour AS BusyHour
| fields BusyHourDay BusyHour
] 
| convert timeformat="%y-%m-%d" ctime(_time) as day
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day
| where date_hour=BusyHour day=BusyHourDay

How can I get the AvgUsedCpuPct for each day Busy Hour ?

I've tried Ayn incremental functions suggestion but I'm really having trouble getting the where clause right.

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

[Ayn]

You're tacking the subsearch directly onto the eval command. A subsearch works very much like backticks in *NIX systems, in that it runs first of all and returns its values to the outer search. In your case, you have

... | eval CpuUsedPct=USED_CPU [search ...

which after the subsearch returns its results will expand to something like

... | eval CpuUsedPct=USED_CPU ((BusyHourDay= ...

This will of course make eval think you want to call USED_CPU as some kind of function, which won't work since there's no such function to eval.

Perhaps you meant to append the second search to the first?

... | eval CpuUsedPct=USED_CPU | append [search ...

[Ayn]

The thing is that when you've run the chart command, the fields you're trying to filter on do not exist anymore. The only fields you have available after the last chart command are the ones it produces - AvgUsedPct and day.

[splunk_zen]

I've tried a series of combinations but I'm really having trouble with the where clause. Can you point me in the right direction ?

PS: I've replaced
| stats avg(CpuUsedPct) as AvgUsedCpuPct by day, date_hour
by
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day

[Ayn]

I think you should break down your search into parts and add one command in the search pipeline at a time, so you can troubleshoot where things go wrong.

[splunk_zen]

Thanks again Ayn.
the solution must be close,
but the search is now returning 0 results.

[Ayn]

I think you need to add an "AND" between your statements in the where command.

... | where date_hour=BusyHour AND day=BusyHourDay

[splunk_zen]

Thanks for the explanation Ayn !
Makes more sense now,
I've slightly changed the search but I am still getting the error,

Error in 'where' command: The operator at 'day=BusyHourDay' is invalid.