How can I group the start and end time of an station like attachment shows? The startime with X I want to skip,
Hi Giuseppe,
- each station sends its status (Available / Unavailable)
- when a station becomes Unavailable it will continue to send this status until it is Available again.
- for each station I want to have the first timestamp when it was unavailable (Stop Column) and in other column I want to have the timestamp when it is available (Start Column) again.
- please see image below for more explanation.
Thank you Giuseppe. But this overview I have already. But I want an overview like "Table 3". Please see my screenshot.
- each station sends its status (Available / Unavailable)
- when a station becomes Unavailable it will continue to send this status until it is Available again.
- for each station I want to have the first timestamp when it was unavailable (Stop Column) and in other column I want to have the timestamp when it is available (Start Column) again for same Station.
- Next time when the same station become unavailable then it will be shown in next row.
- please see image below for more explanation.
Hi,
after debugging the following script I get correct result:
| rex "(?<TimeStamp>\d+-\d+-\d+ \d+:\d+:\d+\.\d+ \+02:00)\s+[^ ]"
| fields - _time
| eval _time=strptime(TimeStamp,"%Y-%m-%d %H:%M:%S.%3N %:z")
| sort -_time
| eval ret_event = split(_raw, ":")
| eval owcs_msg = mvindex(ret_event,4)
| eval owcs_msg_splited = split(owcs_msg, ";")
| eval owcs_msg_id = mvindex(owcs_msg_splited,0)
| eval owcs_msg_station = mvindex(owcs_msg_splited,1)
| eval owcs_msg_status = mvindex(owcs_msg_splited,2)
| where owcs_msg_status = 57 OR owcs_msg_status = 59
| transaction owcs_msg_station endswith=owcs_msg_status="59" keepevicted=true
| eval counter=1
| accum counter as Row
| mvexpand TimeStamp
Result: As you can see, the are events with status 57 but I only need to show the TimeStamp of first event from the list of each group of events (See highlighted events, the rest of events (not highlighted ) can be skipped) in UnAvailable column and the Timestamp of event of status 59 under column Available.
But, When I run stats command then I get the same TS for both columns.
....
| mvexpand TimeStamp
| stats earliest(_time) AS NotAvailableTimeStamp latest(_time) AS AvailableTimeStamp BY owcs_msg_station Row
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")
....Result:
Hi,
so far I can see the first row of the event group is token:
The TimeStamp and _time is always the time of the first event of the group above screen shot.
Hi Giuseppe,
Please find the results below without "| mvexpand TimeStamp" and the following is added:
| table Row TimeStamp _time owcs_msg_station owcs_msg_status event"
And below the results with
"| mvexpand TimeStamp
| table Row TimeStamp _time owcs_msg_station owcs_msg_status event"
Hi @gcusello ,
When I perform the following script then I get the list below:
"MyApp<<<--MQ: 025;2310"
| rex "(?<TimeStamp>\d+-\d+-\d+ \d+:\d+:\d+\.\d+ \+02:00)\s+[^ ]"
| fields - _time
| eval _time=strptime(TimeStamp,"%Y-%m-%d %H:%M:%S.%3N %:z")
| sort -_time
| eval ret_event = split(_raw, ":")
| eval owcs_msg = mvindex(ret_event,4)
| eval owcs_msg_splited = split(owcs_msg, ";")
| eval owcs_msg_id = mvindex(owcs_msg_splited,0)
| eval owcs_msg_station = mvindex(owcs_msg_splited,1)
| eval owcs_msg_status = mvindex(owcs_msg_splited,2)
| where owcs_msg_status = 57 OR owcs_msg_status = 59
| table TimeStamp _time owcs_msg_station owcs_msg_status
Please see my comment.
I need to bring the Unavailable event and available event together. all Unavailable events between first unavailable and available event can be skipped. Then the next row will be the Unavailable event and available event for same station.
When I replace Table command by:
| stats earliest(_time) AS NotAvailableTimeStamp latest(_time) AS AvailableTimeStamp BY owcs_msg_station
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")Then I get result for whole day and not for each time that station was not available and available.
