Filter out logs with ID that contains "Running" as old information when they are "Finished" as the latest information

[AlexisGND]

Hello,
I have logs on the "log and events".

Here's the log sample of an interface with the same id and received 2 times (one that indicates that it started and another that indicates that it finished well) :

[
    {
      "interface.name": "Seq_SAP_to_AGV",
      "host.environment": "PROD",
      "host.name": "Datastage",
      "content": "Seq_SAP_to_AGV Finished - OK",
      "execution.status": "Finished - OK",
      "execution.starttime": "2024-05-28 14:00:34",
      "execution.endtime": "2024-05-28 14:01:34",
      "event.type": "LOG",
      "loglevel": "NOTICE",
      "interface.target.domain": "MFG",
      "ID": "Seq_SAP_to_AGV#28052024.140034",
      "timestamp": "2024-05-28T14:00:34.000000000Z",
      "status": "INFO"
    },
    {
      "interface.name": "Seq_SAP_to_AGV",
      "host.environment": "PROD",
      "host.name": "Datastage",
      "content": "Seq_SAP_to_AGV Running - no warnings",
      "execution.status": "Running - no warnings",
      "execution.starttime": "2024-05-28 14:00:34",
      "event.type": "LOG",
      "loglevel": "NOTICE",
      "interface.target.domain": "MFG",
      "ID": "Seq_SAP_to_AGV#28052024.140034",
      "timestamp": "2024-05-28T14:00:34.000000000Z",
      "status": "INFO"
    }
  ]

As you can see, the "ID" is the same between these 2 logs because i received a "Running" status first and a "Finished" status after.

Now my need is to filter out all of the other informations who contains "Running with..." when there is a "Finished" status with this ID because it is an outdated information since I received the same one with the "Finished with..." and the "endtime".

If I want to speak with other words : I want to keep only the latest information received from the timestamp for every ID.

How is it possible ?
Thanks in advance 🙂
Alexis

[JoseRomero]

Hello Alexis,

I hope this message finds you well.

Maybe this code snippet can help you. In DQL, you can filterby ID and after by execution.status and retrieve only the latest one if this finished. Another option would be to fetch by an ID and sort by execution.status because Finished in alphabet is before that Running.

data record(
      interface.name = "Seq_SAP_to_AGV",
      host.environment = "PROD",
      host.name = "Datastage",
      content = "Seq_SAP_to_AGV Finished - OK",
      execution.status = "Finished - OK",
      execution.starttime = "2024-05-28 14:00:34",
      execution.endtime = "2024-05-28 14:01:34",
      event.type = "LOG",
      loglevel = "NOTICE",
      interface.target.domain = "MFG",
      ID = "Seq_SAP_to_AGV#28052024.140034",
      timestamp = "2024-05-28T14:00:34.000000000Z",
      status = "INFO"
),
record(
      interface.name = "Seq_SAP_to_AGV",
      host.environment = "PROD",
      host.name = "Datastage",
      content = "Seq_SAP_to_AGV Running - no warnings",
      execution.status = "Running - no warnings",
      execution.starttime = "2024-05-28 14:00:34",
      event.type = "LOG",
      loglevel = "NOTICE",
      interface.target.domain = "MFG",
      ID = "Seq_SAP_to_AGV#28052024.140034",
      timestamp = "2024-05-28T14:00:34.000000000Z",
      status = "INFO")
//option 1
// | filter ID == "Seq_SAP_to_AGV#28052024.140034"
// | filter contains(execution.status,"Finished")
// | sort timestamp desc
//option 2 works because Finished in alphabet is before that Running
// | filter ID == "Seq_SAP_to_AGV#28052024.140034"
// | sort execution.status
// | limit 1

[AlexisGND]

Hello @JoseRomero thanks for your reply !
The "contains" is a good use case because there might be "Running - with warnings, Running - with no warnings" etc...
I can see that you used a "limit" in order to not display the "running" record in the second option.

It's a good suggestion but now if we go further, with others Interfaces Names and IDs, how can we keep the latest status received and filter out old status for each ID ?

Regards,
Alexis

[AlexisGND]

PS : the id attribute is a concat between the interface name + "#" + starttime

[JoseRomero]

@AlexisGND