topic Re: How do you group Start and End times from a set of log events? in Getting Data In

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

OnderSentira — Tue, 12 Oct 2021 13:31:07 GMT

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

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

gcusello — Tue, 12 Oct 2021 13:43:49 GMT

Hi @OnderSentira,

sorry, but what's the correlation rule?

in other words: why do you exclude some values and takes some other?

Ciao.

Giuseppe

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

OnderSentira — Tue, 12 Oct 2021 14:03:05 GMT

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.

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

gcusello — Tue, 12 Oct 2021 14:05:35 GMT

Hi @OnderSentira,

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

Ciao.

Giuseppe

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

OnderSentira — Tue, 12 Oct 2021 14:33:39 GMT

Hi Giuseppe,

See part of the events below. And I have used the following Script to extract the fields from the events:

my Search "MyApp<<<--MQ: 025;" | 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) | eval start = if(owcs_msg_status = 57,_time,"") | eval stop = if(owcs_msg_status = 59,_time,"") | eval start= strftime(start,"%Y-%m-%d %H:%M:%S.%3N") | eval stop= strftime(stop,"%Y-%m-%d %H:%M:%S.%3N")

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

OnderSentira — Tue, 12 Oct 2021 14:37:08 GMT

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;

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

gcusello — Tue, 12 Oct 2021 14:55:05 GMT

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

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

OnderSentira — Wed, 13 Oct 2021 06:44:53 GMT

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.

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

gcusello — Wed, 13 Oct 2021 07:41:36 GMT

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

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

OnderSentira — Wed, 13 Oct 2021 08:21:38 GMT

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.

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

gcusello — Wed, 13 Oct 2021 11:13:33 GMT

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

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

OnderSentira — Wed, 13 Oct 2021 11:58:28 GMT

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

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

gcusello — Wed, 13 Oct 2021 12:38:42 GMT

Hi @OnderSentira,

what's the problem?

im my test I have for each Station more transactions with start and end time.

please give me a sample of the result tha you have and the ones you want, e.g. for one Station.

Ciao.

Giuseppe

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

OnderSentira — Wed, 13 Oct 2021 13:07:39 GMT

Hi Giuseppe

The both column have same timestamp.

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

gcusello — Thu, 14 Oct 2021 06:19:10 GMT

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

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

OnderSentira — Thu, 14 Oct 2021 07:23:24 GMT

Hi,

after debugging the following script I get correct result:

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:

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

gcusello — Thu, 14 Oct 2021 07:40:56 GMT

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

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

OnderSentira — Thu, 14 Oct 2021 08:45:27 GMT

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.

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

gcusello — Thu, 14 Oct 2021 08:52:18 GMT

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

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

OnderSentira — Thu, 14 Oct 2021 09:08:18 GMT

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"