Hello!
I'm trying to build a table showing the monthly averages of a calculation for "OEE" by a Machine field.
I then want to drill down on the month selected to show worst offending machine and the downtime reasons (both fields in the data).
The data is coming from DBX and therefore the date is contained in two fields "StartTime" and "StopTime" the events and not _time.
This search provides the table I need for August:
index=dbx MachID=oven* | where StartTime>=1564617600 AND StopTime<1567296000 AND StopTime>0
| lookup machines.csv MachID OUTPUTNEW Machine
| eval StartTime_Human=strftime(StartTime,"%a %B %d %Y %H:%M:%S"), StopTime_Human=strftime(StopTime,"%a %B %d %Y %H:%M:%S"), TotTimeHrs=TotTime/3600, DownTimeHrs=(DownTime/3600), SchedHrs=(SchedQty/ExpCycTm)/60+3
| eval OEE-A-ROTO=((TotTimeHrs)-(DownTimeHrs))/(TotTimeHrs)
| stats avg(SchedHrs) AS SchedHrsAvg, avg(OEE-A-ROTO) AS OEE-A-ROTO, sum(TotTimeHrs) AS TotTimeHrsSum BY JobID, Machine
| eval OEE-P-ROTO=SchedHrsAvg/TotTimeHrsSum
| eval OEE-ROTO=('OEE-A-ROTO'*'OEE-P-ROTO'*0.97)
| stats avg(OEE-ROTO) AS OEE by Machine
| eval OEE=round(OEE*100)."%"
Which provides this:
Machine OEE
Oven2 86%
Oven3 88%
Oven4 84%
Oven5 80%
Oven6 88%
What I would actually like this to be is:
Machine JAN FEB MAR APR MAY JUN JUL AUG
Oven2 86% 86% 86% 86% 86% 86% 86% 86%
Oven3 88% 84% 86% 86% 86% 86% 86% 86%
Oven4 84% 86% 86% 86% 86% 86% 86% 80%
Oven5 80% 86% 86% 86% 86% 86% 86% 80%
Oven6 88% 86% 86% 86% 86% 86% 86% 80%
Thanks for the help!
Cheers
John
Hi johnansett,
your search is probably very slow because in the main search you haven't _time field, it could be useful to create the _time field in the extraction query in DBX (obviously in epochtime format).
At first, you don't need the first where command: you can put the filters in the main search that is better!
index=dbx MachID=oven* StartTime>=1564617600 AND StopTime<1567296000 AND StopTime>0
Then, the following fields are in the main search results? TotTime, DownTime, SchedQty, ExpCycTm ; otherwise I don't understand how you calculate them.
Anyway, you can use bin and chart commands to have something near timechart, e.g.:
| bin StartTime span=1d
| chart sum(TotTimeHrs) AS TotTimeHrs OVER StartTime BY Machine
Adapting it to your search.
Bye.
Giuseppe
Hi johnansett,
your search is probably very slow because in the main search you haven't _time field, it could be useful to create the _time field in the extraction query in DBX (obviously in epochtime format).
At first, you don't need the first where command: you can put the filters in the main search that is better!
index=dbx MachID=oven* StartTime>=1564617600 AND StopTime<1567296000 AND StopTime>0
Then, the following fields are in the main search results? TotTime, DownTime, SchedQty, ExpCycTm ; otherwise I don't understand how you calculate them.
Anyway, you can use bin and chart commands to have something near timechart, e.g.:
| bin StartTime span=1d
| chart sum(TotTimeHrs) AS TotTimeHrs OVER StartTime BY Machine
Adapting it to your search.
Bye.
Giuseppe
Hi Giuseppe,
Thanks for the input. I will try that. I'm not sure it will work with the data but I will test and see.
good work!
Bye.
Giuseppe