Splunk Search

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

johnansett
Communicator

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

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

johnansett
Communicator

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

good work!
Bye.
Giuseppe

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