topic Table of monthly (or weekly) averages using epoch time in field (not _time) in Splunk Search

Table of monthly (or weekly) averages using epoch time in field (not _time)

johnansett — Thu, 12 Sep 2019 16:07:57 GMT

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

Re: Table of monthly (or weekly) averages using epoch time in field (not _time)

gcusello — Thu, 12 Sep 2019 17:14:55 GMT

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

Re: Table of monthly (or weekly) averages using epoch time in field (not _time)

johnansett — Thu, 12 Sep 2019 17:46:42 GMT

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.

Re: Table of monthly (or weekly) averages using epoch time in field (not _time)

gcusello — Thu, 12 Sep 2019 17:51:38 GMT

good work!
Bye.
Giuseppe