How to overlay a line of the overall average on a chart?

[renanprado96]

I have a line chart. The chart is the consumption of the week for 3 printers. I wanted to put a line (which will probably be straight) with the overall average on the chart. How do I do that?

[woodcock]

Append this to your existing search:

| multireport
[ rename Comment AS "Keep the original timechart data as-is" ]
[
    rename Comment AS "Modify original dataset to get overall average"
   | stats values(*) AS * by _time
   | foreach * [ eval fieldCount = if(isnull(fieldCount), 0, fieldCount) + 1
                       | eval fieldSum = if(isnull(fieldSum), 0, fieldSum) + $<<FIELD>>$ ]
   | eval overallAvg = fieldSum / fieldCount
   | fields _time overallAvg
]

[somesoni2]

Can you provide your current search?

[renanprado96]

index=ip5000_3 |  
table pkNmArq, "Total K1 Ink Usage _cc",jobname, PaginasA4Ricoh,"Total K2 Ink Usage _cc", _time |
join type=inner max=0 pkNmArq [search index=cmp  idEtapa=3 CentroImpressao="Alphaville" Maquina="*IP5000_3*" | 
table pkNmArq, FormatoPapel, ClienteERP, ClienteAplicacao,PaginasA4CMP, _time] |

dedup PaginasA4Ricoh |

search PaginasA4Ricoh>=1000 |

eval CustoMilheiroRicohColor=
((('Total K1 Ink Usage _cc'*0.2980)/PaginasA4Ricoh )*1000) + 
((('Total K2 Ink Usage _cc'*0.2980)/PaginasA4Ricoh )*1000) |

fillnull value=0 |

timechart avg(CustoMilheiroRicohColor) as CustoMédioRicohBlack span=1week useother=f |

appendcols [search index=ricoh |  
table pkNmArq, "Total K Ink Usage _cc",jobname, PaginasA4Ricoh, "Total C Ink Usage _cc","Total Y Ink Usage _cc","Total M Ink Usage _cc", _time |
join type=inner max=0 pkNmArq [search index=cmp  date_year>=2015 idEtapa=3 CentroImpressao="Alphaville"   | 
table pkNmArq, FormatoPapel, ClienteERP, ClienteAplicacao,PaginasA4CMP, _time] |

dedup PaginasA4Ricoh |

search PaginasA4Ricoh>=500 |

eval CustoMilheiroRicoh=
((('Total K Ink Usage _cc'*0.2980)/PaginasA4Ricoh )*1000) + 
((('Total C Ink Usage _cc'*0.3514)/PaginasA4Ricoh )*1000) + 
((('Total Y Ink Usage _cc'*0.3514)/PaginasA4Ricoh )*1000) + 
((('Total M Ink Usage _cc'*0.3514)/PaginasA4Ricoh )*1000) |

fillnull value=0 |

timechart avg(CustoMilheiroRicoh) as CustoMédioRicohColor span=1week useother=f] | 

appendcols [search index=prisma printername="CS3900*" |
table pkNmArq, jobname, sheets, printername, colorantname_1, inkusagevolumefront_1, inkusagevolumeback_1, colorantname_2, inkusagevolumefront_2, inkusagevolumeback_2, colorantname_3, inkusagevolumefront_3, inkusagevolumeback_3, colorantname_4, inkusagevolumefront_4, inkusagevolumeback_4, _time |
join type=inner max=1 pkNmArq,  [search index=cmp idEtapa=3 CentroImpressao="Alphaville" Maquina="CS3900*"| 
table pkNmArq, ClienteAplicacao, PaginasA4CMP, FormatoPapel, _time] |

fillnull value=0 |

eval PaginasA4Prisma=case(FormatoPapel="A5",(sheets), FormatoPapel="A4",(sheets*2), FormatoPapel="A3",(sheets*4), FormatoPapel="Carta",(sheets*2), FormatoPapel="Papel A4 Branco 210mmX297mm 75gr",(sheets*2), FormatoPapel="247mm X 200mm",(sheets*2), FormatoPapel="285mm X 220mm",(sheets*2), FormatoPapel="Etiqueta",(0), FormatoPapel="Etiqueta Branca 343 x 305 mm",(0), FormatoPapel="Personalizado",(sheets*2)) |

eval Color1=substr(colorantname_1,1,1) |
eval Color2=substr(colorantname_2,1,1) |
eval Color3=substr(colorantname_3,1,1) |
eval Color4=substr(colorantname_4,1,1) |

eval CustomedioBlack = case( 
Color1="K",((((inkusagevolumefront_1 + inkusagevolumeback_1)/(PaginasA4Prisma))*0.2650)*1000), 
Color2="K",((((inkusagevolumefront_2 + inkusagevolumeback_2)/(PaginasA4Prisma))*0.2650)*1000), 
Color3="K",((((inkusagevolumefront_3 + inkusagevolumeback_3)/(PaginasA4Prisma))*0.2650)*1000), 
Color4="K",((((inkusagevolumefront_4 + inkusagevolumeback_4)/(PaginasA4Prisma))*0.2650)*1000)) | 

eval CustomedioCyan = case( 
Color1="C",((((inkusagevolumefront_1 + inkusagevolumeback_1)/(PaginasA4Prisma))*0.3880)*1000), 
Color2="C",((((inkusagevolumefront_2 + inkusagevolumeback_2)/(PaginasA4Prisma))*0.3880)*1000), 
Color3="C",((((inkusagevolumefront_3 + inkusagevolumeback_3)/(PaginasA4Prisma))*0.3880)*1000), 
Color4="C",((((inkusagevolumefront_4 + inkusagevolumeback_4)/(PaginasA4Prisma))*0.3880)*1000)) | 

eval CustomedioMagenta = case( 
Color1="M",((((inkusagevolumefront_1 + inkusagevolumeback_1)/(PaginasA4Prisma))*0.3880)*1000), 
Color2="M",((((inkusagevolumefront_2 + inkusagevolumeback_2)/(PaginasA4Prisma))*0.3880)*1000), 
Color3="M",((((inkusagevolumefront_3 + inkusagevolumeback_3)/(PaginasA4Prisma))*0.3880)*1000), 
Color4="M",((((inkusagevolumefront_4 + inkusagevolumeback_4)/(PaginasA4Prisma))*0.3880)*1000)) | 

eval CustomedioYellow = case( 
Color1="Y",((((inkusagevolumefront_1 + inkusagevolumeback_1)/(PaginasA4Prisma))*0.3880)*1000), 
Color2="Y",((((inkusagevolumefront_2 + inkusagevolumeback_2)/(PaginasA4Prisma))*0.3880)*1000), 
Color3="Y",((((inkusagevolumefront_3 + inkusagevolumeback_3)/(PaginasA4Prisma))*0.3880)*1000), 
Color4="Y",((((inkusagevolumefront_4 + inkusagevolumeback_4)/(PaginasA4Prisma))*0.3880)*1000)) | 
fillnull value=0 |

eval CustoMilheiroOCE=(CustomedioBlack+CustomedioCyan+CustomedioMagenta+CustomedioYellow) |             
eval CustoArquivo=((PaginasA4Prisma/1000)*CustoMilheiro) |    

timechart avg(CustoMilheiroOCE) as CustoMilheiroOCE span=1week useother=f]  | 

appendcols[search index=screen |

eval ConsumoBlack=(Black/Paginas)*1000 |
eval ConsumoCyan=(Cyan/Paginas)*1000 |
eval ConsumoYellow=(Yellow/Paginas)*1000 |
eval ConsumoMagenta=(Magenta/Paginas)*1000 |
eval Total=(ConsumoBlack+ConsumoCyan+ConsumoYellow+ConsumoMagenta) |

eval CustoMilheiroScreen=
(((Black*0.3608)/Paginas)*1000) + 
(((Cyan*0.4165)/Paginas)*1000) + 
(((Yellow*0.4165)/Paginas)*1000) + 
(((Magenta*0.4165)/Paginas)*1000) |

fillnull value=0 |

timechart avg(CustoMilheiroScreen) as CustoMédioScreen span=1week useother=f ] |

[sundareshr · Accepted solution]

Try this... Your current query would looks something like this

 index=printerusage | timechart sum(hours) as usage by printer

to this, add the following

| eval _c=0 | foreach * [eval _c=_c+1] | addtotals fieldname=_t | eval avg=_t/_c |

This will give you average for all printers by the same time span.

You can then use an overlay to show a line for avg http://docs.splunk.com/Documentation/Splunk/6.1.8/Viz/Chartcontrols#Chart_overlay_example_.28single_...

[muebel]

Hi renanprado96, I believe this is the ideal use for eventstats : http://docs.splunk.com/Documentation/Splunk/6.4.0/SearchReference/eventstats

something like:

| eventstats avg(consumption) as totalAvgConsumption

This will give you the avg consumption for the whole data set, with totalAvgConsumption as a new field containing that value for each event.

Please let me know if this answers your question!

[renanprado96]

I have avg(consumptionLineRed) , avg(consumptionLineYellow) and avg(consumptionLineBlue)

how use "| eventstats avg(consumption) as totalAvgConsumption" to find the average of 3?

This would create a fourth line?

Thanks!

[renanprado96]

Example