Splunk Search

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

renanprado96
Path Finder

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?

0 Karma
1 Solution

sundareshr
Legend

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_...

View solution in original post

woodcock
Esteemed Legend

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
]
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you provide your current search?

0 Karma

renanprado96
Path Finder
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 ] |
0 Karma

sundareshr
Legend

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
SplunkTrust
SplunkTrust

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
Path Finder

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
Path Finder

alt text

Example

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...