How to edit my search to rename a field for a timechart, and display a trendline based on a certain variable?

[JoshuaJohn]

I am trying to get a line graph that displays response time by datacenter. I am having issues:
1) my chart is not renaming the field max(time) to Max_Resp
2) I cannot display trendline by my variable datacenter like I am doing for Max_resp

index="nitro_prod_email" "ET Request Took" earliest=-20m | rex ".*ET Request Took: (?.*?) ms" | eval datacenter=if(like(host,"%plg%"),"GT","MF") | timechart max(time) as Max_Resp by datacenter | eval Max_Resp = round(Max_Resp,2)  | trendline sma5(Max_Resp) as Max_Trend by datacenter

Any solutions?

[sundareshr · Accepted solution]

timechart transforms your data into a table where each row is _time and column is one of the values of datacenter(GT or MF) field. There is no field called Max_Resp. Also, trendline does not accept the split by clause (by datacenter). So, your option are

index="nitro_prod_email" "ET Request Took" earliest=-20m | rex ".*ET Request Took: (?<time>.*?) ms" | eval datacenter=if(like(host,"%plg%"),"GT","MF") | timechart max(time) as Max_Resp by datacenter | foreach * [eval <<FIELD>> = if(<<FIELD>>=_time, _time, round(<<FIELD>>,2))]  | foreach * [eval <<FIELD>> = if(<<FIELD>>=_time, _time, round(<<FIELD>>,2))] | addtotals | trendline sma5(Total) as sma_Total | fields - Total

*OR**

index="nitro_prod_email" "ET Request Took" earliest=-20m | rex ".*ET Request Took: (?<time>.*?) ms" | eval datacenter=if(like(host,"%plg%"),"GT","MF") | bin _time | stats max(time) as Max_Resp by _time datacenter | eval Max_Resp= round(Max_Resp,2) | trendline sma5(Max_Resp) as sma_Max_Resp

[JoshuaJohn]

Hmm so there would be no way to separate the trendlines by datacenter? I don't really need the total trendline. I will try appending with something along the lines of host =gt all of the lines, then host = mf and all of the lines?

[JoshuaJohn]

So I wrote this:

index="nitro_prod_email" host="plg*" "ET Request Took" | rex ".*ET Request Took: (?<time>.*?) ms" | timechart max(time) as Max_gt_Resp| eval Max_gt_Resp= round(Max_gt_Resp,2) | trendline sma5(Max_gt_Resp) as Max_gt_Trend | append [ search index="nitro_prod_email" host="plm*" "ET Request Took" | rex ".*ET Request Took: (?<time>.*?) ms" | timechart max(time) as Max_mf_Resp| eval Max_mf_Resp= round(Max_mf_Resp,2) | trendline sma5(Max_mf_Resp) as Max_mf_Trend ]

This gives me exactly what I wanted but it separates the two into two different graphs, I want them to overlay on top of each other. Any solutions?

[sundareshr]

Not sure I understand, but I think you mean overlay the sma values over the max values. In the format options for chart, there is a Chart Overlay, you can use that to pick the sma fields as overlay values.

http://docs.splunk.com/Documentation/Splunk/6.4.2/Viz/Chartcontrols#Chart_overlay_example_.28dual_ax...

[JoshuaJohn]

Didn't really work as I needed it to, but what I have is actually better than what my original goal was so I am happy. Thanks for the help!