topic Re: Adding field to time series based on separate record in DQL

Adding field to time series based on separate record

paulito — Tue, 18 Feb 2025 22:47:58 GMT

I have a time series with statuses from 0-12. I'd like to replace these status numbers with words as follows:

timeseries PR1_status = avg(PR1_status), by: { device } | join [ data record(Status = "Unknown", PR1_status= 0), record(Status = "Up", PR1_status= 1), record(Status = "Down", PR1_status= 2), record(Status = "Warning", PR1_status= 3), record(Status = "Shutdown", PR1_status= 4), record(Status = "Testing", PR1_status= 5), record(Status = "Dormant", PR1_status= 6), record(Status = "Not Present", PR1_status= 7), record(Status = "Lower Layer Down", PR1_status= 8), record(Status = "Unmanaged", PR1_status= 9), record(Status = "Unplugged", PR1_status= 10), record(Status = "External", PR1_status= 11), record(Status = "Unreachable", PR1_status= 12) ], kind: leftOuter, on: { PR1_status} | fieldsRemove right.PR1_status

I get the following error: The data types are incompatible. Please change the expressions and try again. Using the `to...` conversion functions could help.

I'm not sure if join is the best way to go about this but any help would be appreciated.

Re: Adding field to time series based on separate record

krzysztof_hoja — Wed, 19 Feb 2025 06:39:05 GMT

PR1_status is an array so it cannot be used in join condition directly.

I see that you are trying to translate numeric statues into text and the list of possibilities is rather short and static. This could be a query solving it:

timeseries PR1_status = avg(PR1_status), by: { device } | fieldsAdd Status=coalesce( if(PR1_status[]== 0,"Unknown"), if(PR1_status[]== 1,"Up"), if(PR1_status[]== 2,"Down"), if(PR1_status[]== 3,"Warning"), if(PR1_status[]== 4,"Shutdown"), if(PR1_status[]== 5,"Testins"), if(PR1_status[]== 6,"Dormant"), if(PR1_status[]== 7,"Not Present"), if(PR1_status[]== 8,"Lower Layer Down"), if(PR1_status[]== 9,"Unmanaged"), if(PR1_status[]== 10,"Unplugged"), if(PR1_status[]== 11,"External"), if(PR1_status[]== 12,"Unreachable") )

on simulated data it looks like this:

Re: Adding field to time series based on separate record

paulito — Wed, 19 Feb 2025 15:55:58 GMT

This does work but I'm trying to get the words to show up on a line graph instead of just "1" when a device is up or "2" when it's down. It'd be nice to also get the words on the y axis.

Not sure if this is possible as the data mapping asks for a numeric field.

I've attached an image showing what I'm trying to do.

Thanks again.

Re: Adding field to time series based on separate record

krzysztof_hoja — Wed, 19 Feb 2025 17:16:33 GMT

Unfortunately this visualization as well as other currently available does not allow it. Line/bar/area chart are designed to display metrics (continuous space of values) and what you are looking for is state tracking over time.

What is possible to do is e.g. see number of devices in each state for each interval. And in this case each of series would be labeled with word