Combine search and subsearch into one search to enable real-time charting

[anjafischer]

Hello,

I am trying to figure out how to combine the following search and subsearch into one search such that I can use real-time charts.

system=cics | lookup trans_app_lookup.csv trans_id as tran OUTPUT app_id | timechart sum(count) by app_id | appendcols [search system=cics | timechart sum(cputime) as "overall CPU Time"]

I stumbled over this page: http://answers.splunk.com/answers/81836/realtime-combined-search
where someone is also trying to merge both searches into one, however, this person has different search prefixes for the outer and the subsearch. I, however, am interested in teh exact same data, I only want to chart different functions...

So, what i want to achieve is this:
I want to chart a table of this format "_time app1 ... app_n totalCPU" where the columns app_X represent certain applications and they are filled with data representing the amount of program execution within an application (an application is just a grouping of certain programs)

So I could use an eval to modify the app_id to also contain the ominous last column "overall CPU Time", but based on what information? So what I'm actually missing is the boolean expression for the if() function...

[norbert_hamel · Accepted solution]

Hi,

maybe this approach can help to get into the right direction.

The following base search should result in one column per app_id with the number of program executions named "count: app_X", and one column per app_id with the cum of CPU time named "sum(cputime): app_x".

system=cics | lookup trans_app_lookup.csv trans_id as tran OUTPUT app_id 
| timechart count sum(cputime) by app_id

Then you can calculate the total amount of CPU time by using addtotals, which will add one more column with the sum of all CPU times:

| addtotals sum*

To remove the unwanted columns from the resulting table for a chart you can use:

| fields _time, count*, Total

If this will not help in your application, you might want to check out the | appendpipe command. This can perform other operations than sum, e.g. avg values. And appendpipe can run in real-time searches 🙂

Cheers
Norbert

[anjafischer]

never mind, the hiddenChartFormatter is fine with real-time searches. However, ResultvalueSetters really don't seem to like real-time searches, so i just had figure out how to get rid of that evil module between my initial real-time search and the final chart

[anjafischer]

Hi Norbert,

thanks a lot! 🙂 Calculating both sums and then removing one of them from the resultset later on actually did the trick.

If I insert my final query into the search app of splunk I can really see the real-time results now, however, if I use this query (which worked in the search app) as input for a HiddenChartformatter which then feeds a FlashChart with it within my dashboard/view, i am still able to chart "normal" time ranges but selecting any real time range from a TimeRangePicker leads to the chart either disappearing entirely or simply not updating...