Splunk Search

How to make a new row in a table based on one of the tables column value?

ichesla1111
Path Finder

Hello!!!

I am doing calculations for the time it takes when a machine is undergoing maintenance. Right now, I calculated the time in hours it takes for the maintenance, thus how long the machine was not in use, BUT I want the time to only show how much it was down for that specific day. My code will be based on the time column.

If machine maintenance starts on one day (i.e., 9/7) but ends the next day (i.e., 9/8), I want one row to show the machine downtime for 9/7 =24 hours- time in hours when machine was being worked on AND a new row to be created for 9/8=24 hours- time down during that day

CURRENTLY It gives me the time in hours in a row based on when the machine started to be working on ....current results

ichesla1111_2-1663341153387.png

BUT I want....

ichesla1111_0-1663347411595.png

 


Can I have help please!!!!

Code:
index=.......search.....
| eval open = strptime(Open_Date, "%m/%d/%Y %I:%M:%S %P")
| eval close = strptime(Close_Date, "%m/%d/%Y %I:%M:%S %P")
|eval Diff=round(((close-open)/3600),1)
|eval CloseTime=strftime(close,"%Y-%m-%d")
|eval OpenTime=strftime(open,"%Y-%m-%d")
|eval Time=strftime(_time,"%Y-%m-%d")
|table Time OpenTime CloseTime Close_Date Open_Date Diff

Labels (1)
Tags (2)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Here is an example that performs the logic you need on each row - assuming your search is producing the tables you show

| makeresults
``` Set up 3 date ranges, one spanning one day, the other a single day and the final over 3 days ```
| eval t=split("2022-09-07 20:45:36,2022-09-08 14:08:51;2022-09-12 13:27:53,2022-09-12 15:22:21;2022-09-07 20:45:36,2022-09-10 10:00:51",";")
| mvexpand t
| eval ranges=split(t, ",")
| eval open=strptime(mvindex(ranges, 0), "%F %T")
| eval close=strptime(mvindex(ranges, 1), "%F %T")
| eval _time=open
| fieldformat open=strftime(open, "%F %T")
| fieldformat close=strftime(close, "%F %T")
| fields - ranges t
``` Now do calculations ```
| eval Diff=round(((close-open)/3600),1)
``` Calculate the end of today and the nmumber of hours in today's close ```
| eval end_day=relative_time(open, "+1d@d")
| eval today_hours=round((min(close, end_day)-open)/3600, 1)
``` Work out how many days this covers and create the extra rows to accommodate ```
| eval day_span=if(today_hours < Diff, floor((Diff-today_hours)/24+1), 0)
| eval day=mvrange(0,day_span+1, 1)
| mvexpand day
``` Now adjust diff for each day ```
| eval Diff=case(day=0, today_hours, day<day_span, 24, 1==1, (Diff-today_hours) % 24 )
| table _time open close Diff *
| eval close=if(day<day_span, "-", close)
| fields - day_span day today_hours end_day

It handles multi-day ranges not just the overnight and uses mvrange/mvexpand to create the extra rows as needed.

Hopefully this gives you some pointers to work with

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @ichesla1111,

this should be one of the few cases where the best solution is the transaction command.

I suppose that you can recognize start and open maintenence for a field value (e.g. action="start maintenance" or action="end maintenance"), in this case you could try something like this:

<your_search>
| transaction startswith="Open maintenance" endswith="End maintenance" maxevents=2
| rename duration AS Diff
| eval open = strptime(Open_Date, "%m/%d/%Y %I:%M:%S %P")
| eval close = strptime(Close_Date, "%m/%d/%Y %I:%M:%S %P")
| eval CloseTime=strftime(close,"%Y-%m-%d")
| eval OpenTime=strftime(open,"%Y-%m-%d")
| eval Time=strftime(_time,"%Y-%m-%d") 
| table Time OpenTime CloseTime Close_Date Open_Date Diff

Ciao.

Giuseppe

0 Karma

HathMH
Path Finder

I had a somewhat similar question a bit ago. Taking a search, sort by index, then further sort by values within the index, etc...

Can I do all that in one search string, or do i ha... - Splunk Community

The solution I received has put me on a good path to the intended result.

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