Splunk Search

How to use existing csv date-time fields for earliest and latest in new subsearch?

apietersen
Contributor

Hi I am struggling with an issue for days now but keep running in circles, any help is much appreciated.

Below you find some content of a csv file with fields: rtime(date) , start(time) and stop(time)

I am trying to use the start and stop time for a appended subsearch on other data/subsearch (by append or appendcols or whatever...)

 

 

rtime,start,stop
"2023-07-02",0,0
"2023-07-03","2023-07-03 06:54","2023-07-03 22:42"
"2023-07-04","2023-07-04 06:54","2023-07-04 22:24"
"2023-07-05","2023-07-05 06:48","2023-07-05 21:00"
"2023-07-06","2023-07-06 06:54","2023-07-06 22:36"
"2023-07-07","2023-07-07 07:18","2023-07-07 22:48"
"2023-07-08",0,0
"2023-07-09",0,0
"2023-07-10","2023-07-10 07:06","2023-07-10 22:06"
"2023-07-11","2023-07-11 07:00","2023-07-11 22:36"
"2023-07-12","2023-07-12 06:48","2023-07-12 18:48"

 

 

The subsearch looks in basic as shown below, so quit simple and straight forward :

I want to count here the state of a machine as it is in a state (5 or 6). ,From there I can calcultae it to a time in hrs per day (but never mind these detail) My problem here is that I am not able to reuse the start and stop date-time fields from csv file above for the this subsearch ...

I have tried many things eg: trying to use the start and stop fields form csv fiel for 'earliest' and 'latest' field in the new subsearch (append, appencols etc) ; Reformated all time in epoch or any other time-format, no succes unfortunetaly sofar. 

 

 

index=<namex> AND MachineID=YY AND Tag="*Application.GVL_Hmi.Unit.PackML.Sts.State" AND (Value=6 OR Value=5 ) 
| timechart span=1h count(Value) as Exec 
| timechart span=1d sum(Exec) as Execute 
| eval Execute=round(Execute,1) 

 

 

As said, any help, tip or direction is welcome, Thanks
AshleyP

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=<namex> AND MachineID=YY AND Tag="*Application.GVL_Hmi.Unit.PackML.Sts.State" AND (Value=6 OR Value=5) [| inputlookup your.csv | where start > 0 AND stop > 0 | eval earliest=strptime(start, "%F %H:%M") | eval latest=strptime(stop, "%F %H:%M") | fields earliest latest] 
| timechart span=1h count(Value) as Exec 
| timechart span=1d sum(Exec) as Execute 
| eval Execute=round(Execute,1) 

View solution in original post

apietersen
Contributor

Update,  finally I succeed by using the subsearch as was mentioned before + creating 3x6=18 daily scheduled reports. (6 machines)

 apietersen_2-1691323897200.png

 

 

Dahboard to split in 2 tables panel...

Nb. Unfortunately I was not able to make a historical search all-in-one!

Thanks

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=<namex> AND MachineID=YY AND Tag="*Application.GVL_Hmi.Unit.PackML.Sts.State" AND (Value=6 OR Value=5) [| inputlookup your.csv | where start > 0 AND stop > 0 | eval earliest=strptime(start, "%F %H:%M") | eval latest=strptime(stop, "%F %H:%M") | fields earliest latest] 
| timechart span=1h count(Value) as Exec 
| timechart span=1d sum(Exec) as Execute 
| eval Execute=round(Execute,1) 

apietersen
Contributor

Thanks!! That was very helpful. 👍👍

My conclusion is that I totally ignored the concepts of piping here.  

This is what I was trying to achieve:

apietersen_0-1689324049722.png

👍

0 Karma

apietersen
Contributor

My next step must be to sync start-stop time with rtime 🙂

0 Karma

apietersen
Contributor

Hi ITWhisperer 

After struggling further today I think I need an other approach for this issue.

I intend now to create 2 different csv files based on the two different searches and try to connect them with a join command. (based on a date field)

As you may have noticed in the image before, the sync of days (rtime) and the relevant start and stop time per that day does not match,  it drives me crazy but I keep looking for a solution. When I found an solution I will post it here.

Nonetheless, ITWhisperer thanks for you input sofar 🙂

AshleyP

0 Karma

apietersen
Contributor

Hi ITWhisperer 

Thank you, I will give it a try (tomorrow) and post back he results here.
AshleyP

Tags (1)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...