Timeseries Using Field Values From Ingested Logs

[victor_correa]

Hi all,

My organization used to be pretty Splunk heavy in the past, but we're slow shifting our Log Aggregation/Interrogation initiatives to Dynatrace.

I am far more versed in SPL that I am in DQL, so I was hoping to get some assistance with building a query for a specific scenario.

We have MQ Metrics being ingested via Logs into Dynatrace and I want to create a query that will show a timeseries of the actual values for Queue Depth over a period of time. In Splunk I could use the stats commands with the "values" option and it wouldn't aggregate the values for that particular field - it would simply show them in a timeseries.

With DQL, I can only do AVG or SUM, or any of the other aggregations that are available, but I don't want the Queue Depth to be aggregated, I just want a timeseries of the values as they were ingested.
I managed to create a query that shows Queue Name, Timestamp for the Event and the Queue Depth in a table view, but it'll show me all occurrences for all the queues across the selected timeframe.

fetch logs
| filter dt.host_group.id == "hostgroupname"
| filter contains(log.source, "qstatus")
| parse content, """DATA ld:nasTimestamp "," ld:groupName "," ld:nodeName "," ld:queueManager "," ld:queueName "," ld:queueDepth "," ld:ipProcs "," ld:opProcs ","ld:messageAge "," ld:uncommittedMessages "," ld:putDate ","
ld:putTime "," ld:getDate "," ld:getTime ",""""
| filter startsWith(queueName, "APPLICATIONCODE")
| fields timestamp, queueName, queueDepth

I have searched the community forum and came across a solution to only show the last value for the queueDepth field, that did half of what I was hoping to achieve:

| summarize latestDepth = takeLast(queueDepth), by:{queueName}

But I still can't figure out a way to have the queueDepth (or last queueDepth) values displayed in a timeseries.

Not sure I made myself abundantly clear, but I'm happy to try and further clarify the ask. 🙂

Thanks in advance.

[dannemca]

Take a look at the maketimeseries command https://docs.dynatrace.com/docs/discover-dynatrace/references/dynatrace-query-language/commands/aggr... 

This will allow you to create the timeseries graph with the queueDepth values.

[victor_correa]

Hi @dannemca ,

Thanks for chiming in.

The "maketimeseries" command also requires that I select an aggregation, which is the issue I'm running into.

Despite not achieving the outcome I need, I tried using the command to generate a timeseries of the average Queue Depth overtime, but I get an error.

Command line:

• | maketimeseries avg(queueDepth), by:{queueName}

Error: 

• "The data types are incompatible. Please change the expressions and try again. Using the `to...` conversion functions could help."

[dannemca]

The queueDepth you are getting may be as string, make it as a double first.

toDouble(queueDepth)

https://docs.dynatrace.com/docs/discover-dynatrace/references/dynatrace-query-language/functions/con... 

[victor_correa]

Sure, that would likely work, but it would still give me the average of the values overtime.

I want the actual values for the queueDepth field as they were ingested in the logs, not the average. This is what I'm struggling with at the moment.

[dannemca · Accepted solution]

The values you will get as average will depends on the interval of your timeseries...
If you get the data every 1 min, and you set the interval of your timeseries as 1 min, you will end up with the real value for each datapoint.

The average will transform the data if you set up an interval higher than the data feed. For instance, if you get 5 datapoins in last 5 min as value of [300, 350, 340, 500, 800], you will get the value of 458 in that datapoint of timeseries if your interval is set to 5 min...

but if you set your interval of 1 min, you will see the exact values of 300, then, 350, then 340, ... in the graph. Since those will be the only values to be calculated at that specific interval.

Try and let us know.

[victor_correa]

Thanks, @dannemca .

That actually did the trick. It didn't occur to me that the interval I define on the timeseries will actually determine whether or not the data will be normalized.

I was able to create a timeseries showing queue depth over time, just like I wanted.

I have already marked your response a solution, but just thought I'd squeeze another question in: if I were to setup a single value tile, showing the last value for current depth and the sparkline showing the timeseries overtime, would this same query work?

[dannemca]

It will not, since the result field will be an array, and we can not handle the array in the single value widget. And if you get the last value from the array, you will not be able to draw the sparkline.

So one thing I usualy do, when need to show the current value and its history trend, I use two widgets, one for the maketimeseries as line and other for last value as single value.

Maybe there is another solution for that, but I am not aware... lets see if anyone can enlight us.