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

[apietersen]

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

[apietersen]

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

 

Dahboard to split in 2 tables panel...

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

Thanks

[ITWhisperer · Accepted solution]

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]

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]

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

[apietersen]

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

[apietersen]

Hi ITWhisperer 

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