Can you help me with this subsearch Question using Splunk's TimeWrap command?

[bobbieluturner]

I have this query that uses the timewrap command that I want to insert a subsearch instead of a 'fixed' value ( 193 ) .

I can't get the subsearch to work to insert the hostcount value inplace of the fixed value of 193.

Can anyone help me solve my query?

This is my subsearch:

index=blah  host=blah | fields host | dedup host  |table host | stats count as hostcount

This is the query where I need to insert the 'hostcount' instead of the fixed value of 193:

index=wineventlogapp  EventCode=21 
| dedup host 
| timechart span=1w count(host) as Failures 
| timewrap w series=short 
| addtotals * 
| table Failures* Total 
| eval Week0%=round((193-Failures_s0)/193*100,1) 
| eval Week1%=round((193-Failures_s1)/193*100,1)  
| eval Week2%=round((193-Failures_s2)/193*100,1) 
| eval Week3%=round((193-Failures_s3)/193*100,1) 
| eval Week4%=round((193-Failures_s4)/193*100,1)
| eval Monthly%=tostring(round((('Week0%' + 'Week1%' + 'Week2%' + 'Week3%'+ 'Week4%')/5),1)) + "%" 
| eval Week0%=tostring(round((193-Failures_s0)/193*100,1)) +"%"
| eval Week1%=tostring(round((193-Failures_s1)/193*100,1)) +"%"
| eval Week2%=tostring(round((193-Failures_s2)/193*100,1)) +"%"
| eval Week3%=tostring(round((193-Failures_s3)/193*100,1)) +"%"
| eval Week4%=tostring(round((193-Failures_s4)/193*100,1)) +"%"
| rename Failures_s0 as Week1_count, Failures_s1 as Week2_count, Failures_s2 as Week3_count, Failures_s3 as Week4_count, Failures_s4 as Week5_count, Week0% as Week1_Cum%, Week1% as Week2_Cum%, Week2% as Week3_Cum%, Week3% as Week4_Cum%, Week4% as Week5_Cum%, Total as "Total Monthly SolidCore Failures", Monthly% as "Monthly SolidCore ScoreCard %" 
| table "Monthly SolidCore ScoreCard %" "Total Monthly SolidCore Failures" Week1_count Week1_Cum% Week2_count Week2_Cum% Week3_count Week3_Cum% Week4_count Week4_Cum% Week5_count Week5_Cum%

I can't get it to work - please help....

[bobbieluturner]

Both of these suggestion worked and taught me new ways to do the same thing - Thanks! Great Great information.

Would you happen to know how to get the name of the month (date_month) into the output?
Example: have the table output say "October Monthly Scorecard %" etc..

[woodcock]

You can do it like this:

| makeresults | eval count = [ search index=blah  host=blah | fields host | dedup host  |table host | stats count as hostcount | return $hostcount]
| map search="search index=wineventlogapp  EventCode=21 
| dedup host 
| timechart span=1w count(host) as Failures 
| timewrap w series=short 
| addtotals * 
| table Failures* Total 
| eval Week0%=round(($count$-Failures_s0)/$count$*100,1) 
| eval Week1%=round(($count$-Failures_s1)/$count$*100,1)  
| eval Week2%=round(($count$-Failures_s2)/$count$*100,1) 
| eval Week3%=round(($count$-Failures_s3)/$count$*100,1) 
| eval Week4%=round(($count$-Failures_s4)/$count$*100,1)
| eval Monthly%=tostring(round((('Week0%' + 'Week1%' + 'Week2%' + 'Week3%'+ 'Week4%')/5),1)) + \"%\" 
| eval Week0%=tostring(round(($count$-Failures_s0)/$count$*100,1)) +\"%\"
| eval Week1%=tostring(round(($count$-Failures_s1)/$count$*100,1)) +\"%\"
| eval Week2%=tostring(round(($count$-Failures_s2)/$count$*100,1)) +\"%\"
| eval Week3%=tostring(round(($count$-Failures_s3)/$count$*100,1)) +\"%\"
| eval Week4%=tostring(round(($count$-Failures_s4)/$count$*100,1)) +\"%\"
| rename Failures_s0 as Week1_count, Failures_s1 as Week2_count, Failures_s2 as Week3_count, Failures_s3 as Week4_count, Failures_s4 as Week5_count, Week0% as Week1_Cum%, Week1% as Week2_Cum%, Week2% as Week3_Cum%, Week3% as Week4_Cum%, Week4% as Week5_Cum%, Total as \"Total Monthly SolidCore Failures\", Monthly% as \"Monthly SolidCore ScoreCard %\" 
| table \"Monthly SolidCore ScoreCard %\" \"Total Monthly SolidCore Failures\" Week1_count Week1_Cum% Week2_count Week2_Cum% Week3_count Week3_Cum% Week4_count Week4_Cum% Week5_count Week5_Cum% "

[renjith_nair · Accepted solution]

@bobbieluturner,

Have you tried using appendcols which will give you an extra field with the desired value and then you can use it in your calculations?
For e.g.

index=wineventlogapp  EventCode=21 
 | dedup host 
 | timechart span=1w count(host) as Failures 
 | timewrap w series=short 
 | addtotals * 
 | table Failures* Total 
 | appendcols[ index=blah  host=blah | fields host | dedup host  |table host | stats count as hostcount]|filldown hostcount
 | eval Week0%=round((hostcount-Failures_s0)/hostcount*100,1)
 | .....rest of your search...