Getting Data In

How do you group Start and End times from a set of log events?

OnderSentira
Path Finder

OnderSentira_0-1634045026217.png

How can I group the start and end time of an station like attachment shows? The startime with X I want to skip, 

Labels (2)
Tags (1)
0 Karma
1 Solution

OnderSentira
Path Finder

Hi @gcusello 

The following solution is working for me: 

| 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
| stats min(_time) AS NotAvailableTimeStamp max(_time) AS AvailableTimeStamp BY owcs_msg_station Row field1
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")
| eval ts1=substr(field1,0,30)
| eval _time1=strptime(ts1,"%Y-%m-%d %H:%M:%S.%3N %:z")
| eval owcs_msg_splited1 = split(field1, ";")
| eval owcs_msg_station1 = mvindex(owcs_msg_splited1,1)
| eval owcs_msg_status1 = mvindex(owcs_msg_splited1,2)
| stats min(_time1) AS NotAvailableTimeStamp max(_time1) AS AvailableTimeStamp  by owcs_msg_station1 Row
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")

 

View solution in original post

0 Karma

OnderSentira
Path Finder

Hi @gcusello ,

As long as the station is not available, the system will send status "NOT Available" (Start Time, Station sends the messages in succession). If station is available again, available status will be sent (End Time). I need to show the timestamp when the system became Unavailable first time and became available again. I also need show the duration. This must be done for all events for the same station.

0 Karma

gcusello
Legend

Hi @OnderSentira,

could you share the events before aggregation (I think you used a stats command)?

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

See below some of my events:

 

 

2021-10-11 23:49:42.165 +02:00 MyApp<<<--MQ: 025;2423;58;
2021-10-11 23:48:39.261 +02:00 MyApp<<<--MQ: 025;2423;59;
2021-10-11 23:45:21.577 +02:00 MyApp<<<--MQ: 025;2415;58;
2021-10-11 23:21:49.748 +02:00 MyApp<<<--MQ: 025;2425;59;
2021-10-11 23:20:57.161 +02:00 MyApp<<<--MQ: 025;2425;58;
2021-10-11 23:20:20.867 +02:00 MyApp<<<--MQ: 025;2423;58;
2021-10-11 23:19:47.623 +02:00 MyApp<<<--MQ: 025;2423;59;
2021-10-11 23:19:40.390 +02:00 MyApp<<<--MQ: 025;2422;59;
2021-10-11 23:19:01.883 +02:00 MyApp<<<--MQ: 025;2422;58;
2021-10-11 23:18:45.122 +02:00 MyApp<<<--MQ: 025;2420;59;
2021-10-11 23:17:15.864 +02:00 MyApp<<<--MQ: 025;2420;58;
2021-10-11 23:12:32.095 +02:00 MyApp<<<--MQ: 025;2423;58;
2021-10-11 23:09:26.318 +02:00 MyApp<<<--MQ: 025;2402;59;
2021-10-11 23:09:25.269 +02:00 MyApp<<<--MQ: 025;2402;57;
2021-10-11 23:07:47.638 +02:00 MyApp<<<--MQ: 025;2408;59;
2021-10-11 23:07:46.604 +02:00 MyApp<<<--MQ: 025;2408;57;
2021-10-11 23:03:54.637 +02:00 MyApp<<<--MQ: 025;2402;58;
2021-10-11 23:03:40.217 +02:00 MyApp<<<--MQ: 025;2408;58;
2021-10-11 23:00:54.335 +02:00 MyApp<<<--MQ: 025;2425;59;
2021-10-11 23:00:33.423 +02:00 MyApp<<<--MQ: 025;2425;58;
2021-10-11 22:53:39.917 +02:00 MyApp<<<--MQ: 025;2309;59;
2021-10-11 22:53:37.867 +02:00 MyApp<<<--MQ: 025;2309;57;
2021-10-11 22:53:34.752 +02:00 MyApp<<<--MQ: 025;2309;58;
2021-10-11 22:52:09.233 +02:00 MyApp<<<--MQ: 025;2309;57;
2021-10-11 22:50:03.853 +02:00 MyApp<<<--MQ: 025;2309;58;
2021-10-11 22:48:18.297 +02:00 MyApp<<<--MQ: 025;2429;59;
2021-10-11 22:45:59.798 +02:00 MyApp<<<--MQ: 025;2429;58;
2021-10-11 22:45:44.102 +02:00 MyApp<<<--MQ: 025;2429;59;
2021-10-11 22:44:49.645 +02:00 MyApp<<<--MQ: 025;2429;58;
2021-10-11 22:42:22.055 +02:00 MyApp<<<--MQ: 025;2422;59;
2021-10-11 22:42:07.408 +02:00 MyApp<<<--MQ: 025;2422;58;
2021-10-11 22:38:44.801 +02:00 MyApp<<<--MQ: 025;2428;59;
2021-10-11 22:34:35.329 +02:00 MyApp<<<--MQ: 025;2428;58;
2021-10-11 21:56:25.519 +02:00 MyApp<<<--MQ: 025;2424;59;
2021-10-11 21:56:15.376 +02:00 MyApp<<<--MQ: 025;2420;59;
2021-10-11 21:55:55.916 +02:00 MyApp<<<--MQ: 025;2420;58;
2021-10-11 21:55:48.675 +02:00 MyApp<<<--MQ: 025;2424;58;
2021-10-11 21:48:25.111 +02:00 MyApp<<<--MQ: 025;2425;59;
2021-10-11 21:47:55.588 +02:00 MyApp<<<--MQ: 025;2420;59;
2021-10-11 21:47:41.317 +02:00 MyApp<<<--MQ: 025;2425;58;
2021-10-11 21:47:29.898 +02:00 MyApp<<<--MQ: 025;2420;58;
2021-10-11 21:41:48.068 +02:00 MyApp<<<--MQ: 025;2418;58;
2021-10-11 21:41:40.931 +02:00 MyApp<<<--MQ: 025;2418;59;
2021-10-11 21:41:35.784 +02:00 MyApp<<<--MQ: 025;2418;58;
2021-10-11 21:41:28.621 +02:00 MyApp<<<--MQ: 025;2418;59;
2021-10-11 21:34:47.864 +02:00 MyApp<<<--MQ: 025;2312;59;
2021-10-11 21:34:46.845 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:34:42.766 +02:00 MyApp<<<--MQ: 025;2312;58;
2021-10-11 21:33:52.952 +02:00 MyApp<<<--MQ: 025;2418;58;
2021-10-11 21:32:15.925 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:32:13.885 +02:00 MyApp<<<--MQ: 025;2312;58;
2021-10-11 21:32:12.861 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:32:02.653 +02:00 MyApp<<<--MQ: 025;2312;59;
2021-10-11 21:32:00.604 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:31:57.491 +02:00 MyApp<<<--MQ: 025;2312;58;
2021-10-11 21:31:19.440 +02:00 MyApp<<<--MQ: 025;2420;59;
2021-10-11 21:31:11.245 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:31:09.180 +02:00 MyApp<<<--MQ: 025;2312;58;
2021-10-11 21:31:07.144 +02:00 MyApp<<<--MQ: 025;2312;57;
2021-10-11 21:30:49.801 +02:00 MyApp<<<--MQ: 025;2420;58;
2021-10-11 21:30:34.466 +02:00 MyApp<<<--MQ: 025;2408;59;
2021-10-11 21:30:33.439 +02:00 MyApp<<<--MQ: 025;2408;57;
2021-10-11 21:22:21.987 +02:00 MyApp<<<--MQ: 025;2408;58;
2021-10-11 21:16:10.230 +02:00 MyApp<<<--MQ: 025;2316;59;
2021-10-11 21:16:10.227 +02:00 MyApp<<<--MQ: 025;2316;57;
2021-10-11 21:16:08.154 +02:00 MyApp<<<--MQ: 025;2316;58;
2021-10-11 21:16:06.134 +02:00 MyApp<<<--MQ: 025;2316;57;
2021-10-11 21:16:01.998 +02:00 MyApp<<<--MQ: 025;2316;58;
2021-10-11 21:14:18.056 +02:00 MyApp<<<--MQ: 025;2316;57;
2021-10-11 21:14:15.995 +02:00 MyApp<<<--MQ: 025;2316;58;
2021-10-11 21:14:13.970 +02:00 MyApp<<<--MQ: 025;2316;57;
2021-10-11 21:12:50.601 +02:00 MyApp<<<--MQ: 025;2423;59;
2021-10-11 21:12:08.930 +02:00 MyApp<<<--MQ: 025;2420;59;
2021-10-11 21:11:33.163 +02:00 MyApp<<<--MQ: 025;2423;58;
2021-10-11 21:09:18.031 +02:00 MyApp<<<--MQ: 025;2310;59;
2021-10-11 21:09:17.015 +02:00 MyApp<<<--MQ: 025;2310;57;
2021-10-11 21:09:12.957 +02:00 MyApp<<<--MQ: 025;2310;58;
2021-10-11 21:07:53.288 +02:00 MyApp<<<--MQ: 025;2420;58;
2021-10-11 21:07:29.889 +02:00 MyApp<<<--MQ: 025;2310;57;
2021-10-11 21:07:26.839 +02:00 MyApp<<<--MQ: 025;2310;58;
2021-10-11 21:07:24.802 +02:00 MyApp<<<--MQ: 025;2310;57;
2021-10-11 21:03:37.973 +02:00 MyApp<<<--MQ: 025;2402;59;
2021-10-11 21:03:36.960 +02:00 MyApp<<<--MQ: 025;2402;57;
2021-10-11 20:54:19.071 +02:00 MyApp<<<--MQ: 025;2402;58;
2021-10-11 20:50:03.435 +02:00 MyApp<<<--MQ: 025;2317;59;
2021-10-11 20:50:02.426 +02:00 MyApp<<<--MQ: 025;2317;57;
2021-10-11 20:49:51.289 +02:00 MyApp<<<--MQ: 025;2317;58;
2021-10-11 20:49:50.277 +02:00 MyApp<<<--MQ: 025;2317;57;
2021-10-11 20:49:47.158 +02:00 MyApp<<<--MQ: 025;2317;58;
2021-10-11 20:47:17.382 +02:00 MyApp<<<--MQ: 025;2317;57;
2021-10-11 20:47:15.358 +02:00 MyApp<<<--MQ: 025;2317;58;

 

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

could you share also the search you used to group events?

let me understand:

  • the "station" field that you used to group events is the one that has value 2423, 2415, etc...
  • you want to group two events with the same station value,
  • each station value can have one or two events,
  • you want only the ones with two values;

is it correct?

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

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.

OnderSentira_0-1634107467684.png

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

please see my example and adapt it to your needs (the first part is only to have the data you sent, the second one is the hinted solution):

| makeresults 
| eval field="2021-10-11 23:49:42.165 +02:00 MyApp<<<--MQ: 025;2423;58;|
2021-10-11 23:48:39.261 +02:00 MyApp<<<--MQ: 025;2423;59;|
2021-10-11 23:45:21.577 +02:00 MyApp<<<--MQ: 025;2415;58;|
2021-10-11 23:21:49.748 +02:00 MyApp<<<--MQ: 025;2425;59;|
2021-10-11 23:20:57.161 +02:00 MyApp<<<--MQ: 025;2425;58;|
2021-10-11 23:20:20.867 +02:00 MyApp<<<--MQ: 025;2423;58;|
2021-10-11 23:19:47.623 +02:00 MyApp<<<--MQ: 025;2423;59;|
2021-10-11 23:19:40.390 +02:00 MyApp<<<--MQ: 025;2422;59;|
2021-10-11 23:19:01.883 +02:00 MyApp<<<--MQ: 025;2422;58;|
2021-10-11 23:18:45.122 +02:00 MyApp<<<--MQ: 025;2420;59;|
2021-10-11 23:17:15.864 +02:00 MyApp<<<--MQ: 025;2420;58;|
2021-10-11 23:12:32.095 +02:00 MyApp<<<--MQ: 025;2423;58;|
2021-10-11 23:09:26.318 +02:00 MyApp<<<--MQ: 025;2402;59;|
2021-10-11 23:09:25.269 +02:00 MyApp<<<--MQ: 025;2402;57;|
2021-10-11 23:07:47.638 +02:00 MyApp<<<--MQ: 025;2408;59;|
2021-10-11 23:07:46.604 +02:00 MyApp<<<--MQ: 025;2408;57;|
2021-10-11 23:03:54.637 +02:00 MyApp<<<--MQ: 025;2402;58;|
2021-10-11 23:03:40.217 +02:00 MyApp<<<--MQ: 025;2408;58;|
2021-10-11 23:00:54.335 +02:00 MyApp<<<--MQ: 025;2425;59;|
2021-10-11 23:00:33.423 +02:00 MyApp<<<--MQ: 025;2425;58;|
2021-10-11 22:53:39.917 +02:00 MyApp<<<--MQ: 025;2309;59;|
2021-10-11 22:53:37.867 +02:00 MyApp<<<--MQ: 025;2309;57;|
2021-10-11 22:53:34.752 +02:00 MyApp<<<--MQ: 025;2309;58;|
2021-10-11 22:52:09.233 +02:00 MyApp<<<--MQ: 025;2309;57;|
2021-10-11 22:50:03.853 +02:00 MyApp<<<--MQ: 025;2309;58;|
2021-10-11 22:48:18.297 +02:00 MyApp<<<--MQ: 025;2429;59;|
2021-10-11 22:45:59.798 +02:00 MyApp<<<--MQ: 025;2429;58;|
2021-10-11 22:45:44.102 +02:00 MyApp<<<--MQ: 025;2429;59;|
2021-10-11 22:44:49.645 +02:00 MyApp<<<--MQ: 025;2429;58;|
2021-10-11 22:42:22.055 +02:00 MyApp<<<--MQ: 025;2422;59;|
2021-10-11 22:42:07.408 +02:00 MyApp<<<--MQ: 025;2422;58;|
2021-10-11 22:38:44.801 +02:00 MyApp<<<--MQ: 025;2428;59;|
2021-10-11 22:34:35.329 +02:00 MyApp<<<--MQ: 025;2428;58;|
2021-10-11 21:56:25.519 +02:00 MyApp<<<--MQ: 025;2424;59;|
2021-10-11 21:56:15.376 +02:00 MyApp<<<--MQ: 025;2420;59;|
2021-10-11 21:55:55.916 +02:00 MyApp<<<--MQ: 025;2420;58;|
2021-10-11 21:55:48.675 +02:00 MyApp<<<--MQ: 025;2424;58;|
2021-10-11 21:48:25.111 +02:00 MyApp<<<--MQ: 025;2425;59;|
2021-10-11 21:47:55.588 +02:00 MyApp<<<--MQ: 025;2420;59;|
2021-10-11 21:47:41.317 +02:00 MyApp<<<--MQ: 025;2425;58;|
2021-10-11 21:47:29.898 +02:00 MyApp<<<--MQ: 025;2420;58;|
2021-10-11 21:41:48.068 +02:00 MyApp<<<--MQ: 025;2418;58;|
2021-10-11 21:41:40.931 +02:00 MyApp<<<--MQ: 025;2418;59;|
2021-10-11 21:41:35.784 +02:00 MyApp<<<--MQ: 025;2418;58;|
2021-10-11 21:41:28.621 +02:00 MyApp<<<--MQ: 025;2418;59;|
2021-10-11 21:34:47.864 +02:00 MyApp<<<--MQ: 025;2312;59;|
2021-10-11 21:34:46.845 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:34:42.766 +02:00 MyApp<<<--MQ: 025;2312;58;|
2021-10-11 21:33:52.952 +02:00 MyApp<<<--MQ: 025;2418;58;|
2021-10-11 21:32:15.925 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:32:13.885 +02:00 MyApp<<<--MQ: 025;2312;58;|
2021-10-11 21:32:12.861 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:32:02.653 +02:00 MyApp<<<--MQ: 025;2312;59;|
2021-10-11 21:32:00.604 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:31:57.491 +02:00 MyApp<<<--MQ: 025;2312;58;|
2021-10-11 21:31:19.440 +02:00 MyApp<<<--MQ: 025;2420;59;|
2021-10-11 21:31:11.245 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:31:09.180 +02:00 MyApp<<<--MQ: 025;2312;58;|
2021-10-11 21:31:07.144 +02:00 MyApp<<<--MQ: 025;2312;57;|
2021-10-11 21:30:49.801 +02:00 MyApp<<<--MQ: 025;2420;58;|
2021-10-11 21:30:34.466 +02:00 MyApp<<<--MQ: 025;2408;59;|
2021-10-11 21:30:33.439 +02:00 MyApp<<<--MQ: 025;2408;57;|
2021-10-11 21:22:21.987 +02:00 MyApp<<<--MQ: 025;2408;58;|
2021-10-11 21:16:10.230 +02:00 MyApp<<<--MQ: 025;2316;59;|
2021-10-11 21:16:10.227 +02:00 MyApp<<<--MQ: 025;2316;57;|
2021-10-11 21:16:08.154 +02:00 MyApp<<<--MQ: 025;2316;58;|
2021-10-11 21:16:06.134 +02:00 MyApp<<<--MQ: 025;2316;57;|
2021-10-11 21:16:01.998 +02:00 MyApp<<<--MQ: 025;2316;58;|
2021-10-11 21:14:18.056 +02:00 MyApp<<<--MQ: 025;2316;57;|
2021-10-11 21:14:15.995 +02:00 MyApp<<<--MQ: 025;2316;58;|
2021-10-11 21:14:13.970 +02:00 MyApp<<<--MQ: 025;2316;57;|
2021-10-11 21:12:50.601 +02:00 MyApp<<<--MQ: 025;2423;59;|
2021-10-11 21:12:08.930 +02:00 MyApp<<<--MQ: 025;2420;59;|
2021-10-11 21:11:33.163 +02:00 MyApp<<<--MQ: 025;2423;58;|
2021-10-11 21:09:18.031 +02:00 MyApp<<<--MQ: 025;2310;59;|
2021-10-11 21:09:17.015 +02:00 MyApp<<<--MQ: 025;2310;57;|
2021-10-11 21:09:12.957 +02:00 MyApp<<<--MQ: 025;2310;58;|
2021-10-11 21:07:53.288 +02:00 MyApp<<<--MQ: 025;2420;58;|
2021-10-11 21:07:29.889 +02:00 MyApp<<<--MQ: 025;2310;57;|
2021-10-11 21:07:26.839 +02:00 MyApp<<<--MQ: 025;2310;58;|
2021-10-11 21:07:24.802 +02:00 MyApp<<<--MQ: 025;2310;57;|
2021-10-11 21:03:37.973 +02:00 MyApp<<<--MQ: 025;2402;59;|
2021-10-11 21:03:36.960 +02:00 MyApp<<<--MQ: 025;2402;57;|
2021-10-11 20:54:19.071 +02:00 MyApp<<<--MQ: 025;2402;58;|
2021-10-11 20:50:03.435 +02:00 MyApp<<<--MQ: 025;2317;59;|
2021-10-11 20:50:02.426 +02:00 MyApp<<<--MQ: 025;2317;57;|
2021-10-11 20:49:51.289 +02:00 MyApp<<<--MQ: 025;2317;58;|
2021-10-11 20:49:50.277 +02:00 MyApp<<<--MQ: 025;2317;57;|
2021-10-11 20:49:47.158 +02:00 MyApp<<<--MQ: 025;2317;58;|
2021-10-11 20:47:17.382 +02:00 MyApp<<<--MQ: 025;2317;57;|
2021-10-11 20:47:15.358 +02:00 MyApp<<<--MQ: 025;2317;58;"
| makemv field delim="|"
| mvexpand field
| rename field AS _raw


| rex "(?<TimeStamp>\d+-\d+-\d+ \d+:\d+:\d+\.\d+ \+02:00)\s+[^ ]+\s+025;(?<Station>\d+);(?<StatusCode>\d+)"
| sort TimeStamp
| transaction Station startswith=";57;" endswith=";59;"
| table TimeStamp Station
| mvexpand TimeStamp
| stats first(TimeStamp) AS "NotAvailableTimeStamp" last(TimeStamp) AS "AvailableTimeStamp" BY Station

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

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.

OnderSentira_0-1634113107836.png

 

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

please try this:

| makeresults 
| eval field="2021-10-11 23:49:42.165 +02:00 MyApp<<<--MQ: 025;2423;58;|2021-10-11 23:48:39.261 +02:00 MyApp<<<--MQ: 025;2423;59;|2021-10-11 23:45:21.577 +02:00 MyApp<<<--MQ: 025;2415;58;|2021-10-11 23:21:49.748 +02:00 MyApp<<<--MQ: 025;2425;59;|2021-10-11 23:20:57.161 +02:00 MyApp<<<--MQ: 025;2425;58;|2021-10-11 23:20:20.867 +02:00 MyApp<<<--MQ: 025;2423;58;|2021-10-11 23:19:47.623 +02:00 MyApp<<<--MQ: 025;2423;59;|2021-10-11 23:19:40.390 +02:00 MyApp<<<--MQ: 025;2422;59;|2021-10-11 23:19:01.883 +02:00 MyApp<<<--MQ: 025;2422;58;|2021-10-11 23:18:45.122 +02:00 MyApp<<<--MQ: 025;2420;59;|2021-10-11 23:17:15.864 +02:00 MyApp<<<--MQ: 025;2420;58;|2021-10-11 23:12:32.095 +02:00 MyApp<<<--MQ: 025;2423;58;|2021-10-11 23:09:26.318 +02:00 MyApp<<<--MQ: 025;2402;59;|2021-10-11 23:09:25.269 +02:00 MyApp<<<--MQ: 025;2402;57;|2021-10-11 23:07:47.638 +02:00 MyApp<<<--MQ: 025;2408;59;|2021-10-11 23:07:46.604 +02:00 MyApp<<<--MQ: 025;2408;57;|2021-10-11 23:03:54.637 +02:00 MyApp<<<--MQ: 025;2402;58;|2021-10-11 23:03:40.217 +02:00 MyApp<<<--MQ: 025;2408;58;|2021-10-11 23:00:54.335 +02:00 MyApp<<<--MQ: 025;2425;59;|2021-10-11 23:00:33.423 +02:00 MyApp<<<--MQ: 025;2425;58;|2021-10-11 22:53:39.917 +02:00 MyApp<<<--MQ: 025;2309;59;|2021-10-11 22:53:37.867 +02:00 MyApp<<<--MQ: 025;2309;57;|2021-10-11 22:53:34.752 +02:00 MyApp<<<--MQ: 025;2309;58;|2021-10-11 22:52:09.233 +02:00 MyApp<<<--MQ: 025;2309;57;|2021-10-11 22:50:03.853 +02:00 MyApp<<<--MQ: 025;2309;58;|2021-10-11 22:48:18.297 +02:00 MyApp<<<--MQ: 025;2429;59;|2021-10-11 22:45:59.798 +02:00 MyApp<<<--MQ: 025;2429;58;|2021-10-11 22:45:44.102 +02:00 MyApp<<<--MQ: 025;2429;59;|2021-10-11 22:44:49.645 +02:00 MyApp<<<--MQ: 025;2429;58;|2021-10-11 22:42:22.055 +02:00 MyApp<<<--MQ: 025;2422;59;|2021-10-11 22:42:07.408 +02:00 MyApp<<<--MQ: 025;2422;58;|2021-10-11 22:38:44.801 +02:00 MyApp<<<--MQ: 025;2428;59;|2021-10-11 22:34:35.329 +02:00 MyApp<<<--MQ: 025;2428;58;|2021-10-11 21:56:25.519 +02:00 MyApp<<<--MQ: 025;2424;59;|2021-10-11 21:56:15.376 +02:00 MyApp<<<--MQ: 025;2420;59;|2021-10-11 21:55:55.916 +02:00 MyApp<<<--MQ: 025;2420;58;|2021-10-11 21:55:48.675 +02:00 MyApp<<<--MQ: 025;2424;58;|2021-10-11 21:48:25.111 +02:00 MyApp<<<--MQ: 025;2425;59;|2021-10-11 21:47:55.588 +02:00 MyApp<<<--MQ: 025;2420;59;|2021-10-11 21:47:41.317 +02:00 MyApp<<<--MQ: 025;2425;58;|2021-10-11 21:47:29.898 +02:00 MyApp<<<--MQ: 025;2420;58;|2021-10-11 21:41:48.068 +02:00 MyApp<<<--MQ: 025;2418;58;|2021-10-11 21:41:40.931 +02:00 MyApp<<<--MQ: 025;2418;59;|2021-10-11 21:41:35.784 +02:00 MyApp<<<--MQ: 025;2418;58;|2021-10-11 21:41:28.621 +02:00 MyApp<<<--MQ: 025;2418;59;|2021-10-11 21:34:47.864 +02:00 MyApp<<<--MQ: 025;2312;59;|2021-10-11 21:34:46.845 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:34:42.766 +02:00 MyApp<<<--MQ: 025;2312;58;|2021-10-11 21:33:52.952 +02:00 MyApp<<<--MQ: 025;2418;58;|2021-10-11 21:32:15.925 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:32:13.885 +02:00 MyApp<<<--MQ: 025;2312;58;|2021-10-11 21:32:12.861 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:32:02.653 +02:00 MyApp<<<--MQ: 025;2312;59;|2021-10-11 21:32:00.604 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:31:57.491 +02:00 MyApp<<<--MQ: 025;2312;58;|2021-10-11 21:31:19.440 +02:00 MyApp<<<--MQ: 025;2420;59;|2021-10-11 21:31:11.245 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:31:09.180 +02:00 MyApp<<<--MQ: 025;2312;58;|2021-10-11 21:31:07.144 +02:00 MyApp<<<--MQ: 025;2312;57;|2021-10-11 21:30:49.801 +02:00 MyApp<<<--MQ: 025;2420;58;|2021-10-11 21:30:34.466 +02:00 MyApp<<<--MQ: 025;2408;59;|2021-10-11 21:30:33.439 +02:00 MyApp<<<--MQ: 025;2408;57;|2021-10-11 21:22:21.987 +02:00 MyApp<<<--MQ: 025;2408;58;|2021-10-11 21:16:10.230 +02:00 MyApp<<<--MQ: 025;2316;59;|2021-10-11 21:16:10.227 +02:00 MyApp<<<--MQ: 025;2316;57;|2021-10-11 21:16:08.154 +02:00 MyApp<<<--MQ: 025;2316;58;|2021-10-11 21:16:06.134 +02:00 MyApp<<<--MQ: 025;2316;57;|2021-10-11 21:16:01.998 +02:00 MyApp<<<--MQ: 025;2316;58;|2021-10-11 21:14:18.056 +02:00 MyApp<<<--MQ: 025;2316;57;|2021-10-11 21:14:15.995 +02:00 MyApp<<<--MQ: 025;2316;58;|2021-10-11 21:14:13.970 +02:00 MyApp<<<--MQ: 025;2316;57;|2021-10-11 21:12:50.601 +02:00 MyApp<<<--MQ: 025;2423;59;|2021-10-11 21:12:08.930 +02:00 MyApp<<<--MQ: 025;2420;59;|2021-10-11 21:11:33.163 +02:00 MyApp<<<--MQ: 025;2423;58;|2021-10-11 21:09:18.031 +02:00 MyApp<<<--MQ: 025;2310;59;|2021-10-11 21:09:17.015 +02:00 MyApp<<<--MQ: 025;2310;57;|2021-10-11 21:09:12.957 +02:00 MyApp<<<--MQ: 025;2310;58;|2021-10-11 21:07:53.288 +02:00 MyApp<<<--MQ: 025;2420;58;|2021-10-11 21:07:29.889 +02:00 MyApp<<<--MQ: 025;2310;57;|2021-10-11 21:07:26.839 +02:00 MyApp<<<--MQ: 025;2310;58;|2021-10-11 21:07:24.802 +02:00 MyApp<<<--MQ: 025;2310;57;|2021-10-11 21:03:37.973 +02:00 MyApp<<<--MQ: 025;2402;59;|2021-10-11 21:03:36.960 +02:00 MyApp<<<--MQ: 025;2402;57;|2021-10-11 20:54:19.071 +02:00 MyApp<<<--MQ: 025;2402;58;|2021-10-11 20:50:03.435 +02:00 MyApp<<<--MQ: 025;2317;59;|2021-10-11 20:50:02.426 +02:00 MyApp<<<--MQ: 025;2317;57;|2021-10-11 20:49:51.289 +02:00 MyApp<<<--MQ: 025;2317;58;|2021-10-11 20:49:50.277 +02:00 MyApp<<<--MQ: 025;2317;57;|2021-10-11 20:49:47.158 +02:00 MyApp<<<--MQ: 025;2317;58;|2021-10-11 20:47:17.382 +02:00 MyApp<<<--MQ: 025;2317;57;|2021-10-11 20:47:15.358 +02:00 MyApp<<<--MQ: 025;2317;58;"
| makemv field delim="|"
| mvexpand field
| rename field AS _raw


| rex "(?<TimeStamp>\d+-\d+-\d+ \d+:\d+:\d+\.\d+ \+02:00)\s+[^ ]+\s+025;(?<Station>\d+);(?<StatusCode>\d+)"
| fields - _time
| eval _time=strptime(TimeStamp,"%Y-%m-%d %H:%M:%S.%3N %:z")
| sort -_time
| transaction Station endswith=";59" keepevicted=true
| eval counter=1
| accum counter as Row
| mvexpand TimeStamp
| stats earliest(_time) AS NotAvailableTimeStamp latest(_time) AS AvailableTimeStamp BY Station Row
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")
| fields - Row
| eval counter=1
| accum counter as Row
| table Row Station NotAvailableTimeStamp AvailableTimeStamp

Ciao.

Giuseppe

Tags (1)
0 Karma

OnderSentira
Path Finder

Hi Giuseppe,

Nope, it is not working. I get same TS for NotAvailableTimeStamp and AvailableTimeStamp. 
 I also have added status 57 to the Transaction, but still not working. same 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 startswith=owcs_msg_status="57" endswith=owcs_msg_status="59" keepevicted=true
| eval counter=1
| accum counter as Row
| 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")
| fields - Row
| eval counter=1
| accum counter as Row
| table Row owcs_msg_station NotAvailableTimeStamp AvailableTimeStamp
0 Karma

gcusello
Legend

Hi @OnderSentira,

please at first take this test: replace the transaction row with this row

| transaction owcs_msg_station endswith=owcs_msg_status="59" keepevicted=true

if you don't solve the problem, please debug your search running it until the row before transaction, so you can see if you have a row for each event.

Bye.

Giuseppe

0 Karma

OnderSentira
Path Finder

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.

OnderSentira_0-1634195641083.png

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:

OnderSentira_1-1634196118427.png

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

please share the values of all fields after the mvexpand TimeStamp command, not only event, in particular, I'm interested on _time and Row.

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

Hi,

so far I can see the first row of the event group is token:

OnderSentira_0-1634200933635.png

The TimeStamp and _time is always the time of the first event of the group above screen shot.

OnderSentira_1-1634201096524.png

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

please, run your search until "| mvexpand TimeStamp" and add the following command:

| table Row TimeStamp _time owcs_msg_station owcs_msg_status event

and share results

Ciao.

Giuseppe

 

0 Karma

OnderSentira
Path Finder

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"

OnderSentira_0-1634202276059.png

 

And below the results with 
"| mvexpand TimeStamp
| table Row TimeStamp _time owcs_msg_station owcs_msg_status event"

OnderSentira_1-1634202471914.png

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

at first simplify your results (only for debugging) filtering your results for one Station with many events.

Then put at the end  of your last search, before the table command:

| fields - _time
| eval _time=strptime(TimeStamp,"%Y-%m-%d %H:%M:%S.%3N %:z")
| sort -_time

 In this way you shuld have the correct list of events to run the stats command, and plese, show me the results, then replace the final table command with:

| 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")

You should have the correct results.

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

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.

OnderSentira_2-1634205979030.png

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.

OnderSentira_3-1634206834525.png

 

 

 

 

 

0 Karma

gcusello
Legend

Hi @OnderSentira,

you missed a part, please see this:

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

You missed the counter to identify transaction and the Row in the stats command.

Please try it.

Ciao.

Giuseppe

0 Karma

OnderSentira
Path Finder

Hi @gcusello 

The following solution is working for me: 

| 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
| stats min(_time) AS NotAvailableTimeStamp max(_time) AS AvailableTimeStamp BY owcs_msg_station Row field1
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")
| eval ts1=substr(field1,0,30)
| eval _time1=strptime(ts1,"%Y-%m-%d %H:%M:%S.%3N %:z")
| eval owcs_msg_splited1 = split(field1, ";")
| eval owcs_msg_station1 = mvindex(owcs_msg_splited1,1)
| eval owcs_msg_status1 = mvindex(owcs_msg_splited1,2)
| stats min(_time1) AS NotAvailableTimeStamp max(_time1) AS AvailableTimeStamp  by owcs_msg_station1 Row
| eval NotAvailableTimeStamp=strftime(NotAvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N"), AvailableTimeStamp=strftime(AvailableTimeStamp,"%Y-%m-%d %H:%M:%S.%3N")

 

0 Karma

gcusello
Legend

@OnderSentira,

ok good for you, tell me if I can still help you, or, please, accept the answer for the other paople of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!