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?
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_...
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
]
Can you provide your current search?
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 ] |
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_...
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!
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!