topic Re: Carry over the value of a field to another result row in DQL

Carry over the value of a field to another result row

Red — Fri, 24 Jan 2025 09:01:31 GMT

Hello community,

I have set up a bucket to retrieve events from the "Splunk OnCall" tool in order to create monitoring dashboards. No problem on this side, I receive my data.

I am preparing a dashboard with the listing of OnCall alerts. In order to provide maximum detail, I want to calculate the lifetime of an OnCall alert. And this is where I get stuck because I receive an event for each alert status. I am looking to use the "timestamp" field to be able to do this calculation, so I started with the idea of having a field with the timestamp value for each state in order to do a basic calculation:

However, I have a problem, I am looking to report the values of these fields on each line of the result of an alert, otherwise, I cannot do the calculation:

And the additional difficulty is that I must only display the last current state (in the previous screens, I show the 3 possible states to illustrate my problem but at the end, there is a dedup to keep only the last state).

I searched everywhere in the doc but I did not find a solution. Do you have any idea how we can go about it, if it is feasible?

Here is the DQL that I am currently using, it is not yet optimized:

fetch events | filter source == "OnCall" | filterOut in(routingKey, array("dynatrace_event", "mpb-dynatrace_event")) | sort incidentNumber asc, timestamp desc //| dedup incidentNumber | fieldsAdd parse(event, "STRING:priority ' - 'LD") | fieldsAdd parse(event, """(('P'LD' - 'LD:title)|LD:title)""") | fieldsAdd priority = if(isNull(priority), "Px", else:priority) | fieldsAdd routingKey = if(routingKey == "dynatrace_2", "Dynatrace", else:if(routingKey == "servicepilot_prod", "ServicePilot", else:if(routingKey == "control-m", "Control-M", else:if(routingKey == "dynatrace_mfe", "Dynatrace MFE", else:if(routingKey == "control-m_mfe", "Control-M MFE", else:if(routingKey == "dynatrace_amm", "Dynatrace AMM", else:if(routingKey == "control-m_z-os", "Control-M Z/OS", else:if(routingKey == "mpb-control-m", "Control-M MPB", else:if(routingKey == "mpb-dynatrace_problem", "Dynatrace MPB", else:if(routingKey == "dynatrace_f02", "Dynatrace F02", else:routingKey)))))))))) | fieldsAdd startAlert = if(alertType == "CRITICAL", timestamp, else:"") | fieldsAdd ackAlert = if(alertType == "ACKNOWLEDGEMENT", timestamp, else:"") | fieldsAdd endAlert = if(alertType == "RECOVERY", timestamp, else:"") | fieldsRename `Priorité` = priority, `Dernier état` = alertType, `Titre` = title, `N° OnCall` = incidentNumber, `Propriétaire` = owner, `Source` = routingKey, `N° ticket EV` = ticket_EV | fields timestamp, startAlert, ackAlert, endAlert, `N° OnCall`, `Dernier état`, `Priorité`, Titre, Source, `Propriétaire`, `N° ticket EV`

Thanks in advance,

Sincerely,

Red

Re: Carry over the value of a field to another result row

Tiit_Hallas — Tue, 28 Jan 2025 07:32:37 GMT

Would it make sense to summarize the results? If I got your question right, then one of the solutions would be to use takeLast aggregation function for the times and just summarize based on them, e.g.

| summarize { start_time = takeLast(start), ack_time = takeLast(ack), end_time = takeLast(end), latest_status = takeLast(status) }, by: { id, title }

A solution with the demo data that resembles your use-case can be seen in a shared case on our Playground: https://wkf10640.apps.dynatrace.com/ui/apps/dynatrace.security.investigator/share/2a99bca9-eeb7-4671-804f-1261eb52cbd6

Is this something you had in mind?

Re: Carry over the value of a field to another result row

Red — Tue, 28 Jan 2025 10:41:59 GMT

Hello @Tiit_Hallas,

Thanks for your help. I'm not yet comfortable with DQL, I'm still discovering some things (like the "summarize" command). I adapted it slightly to add some necessary fields to my dashboard, and I get everything back as it should:

So, after having validated on a particular alert (this makes the verification more readable), I deactivated the filter so that it applies to all the fields but there, I no longer have the fields filled in:

I have the impression that it only takes the last parameter (in our case, the end_time). I tinkered a little and I saw that by changing the order of the "by" parameter and specifying to sort by the incidentNumber, it worked as well for a targeted element as for the entire dashboard:

Thanks again for your help. Have a nice day,

Sincerely,

Red