Filter Appended Time-Series by Tag Value Using DQL

[markmc]

I've been exploring DQL and trying to get a timeseries query working. It would be easier to query based on hard names but that's too easy. The general idea is that I'm filtering for a message queue with a key value tag such as MQ_RSP. This returns a handful of message queue names with an associated queue count. From there, I then extract the value from the key value tag to identify the name of the queue on the other end. Effectively the tag is building a relationship between the two queues and then I'll run a small calculation off the pair.

The part I'm stuck at is that my appended timeseries query to get the other end is returning with nulls possibly because I'm filtering out the entity but I'm not sure how to get around that. What would be the best approach here?

timeseries { 
Incoming = sum(ibmmq.queue.enqueue_cnt), by:{queue_name}, filter: {  in(
        `dt.entity.ibmmq:local_queue`,
        classicEntitySelector("type(ibmmq:local_queue),tag(\"MQ_RSP\")")
    )}}  

| lookup [
  fetch `dt.entity.ibmmq:local_queue` 
  | expand tag_string=tags
  | filter startsWith(tag_string,"MQ_RSP")
  | parse tag_string, """((LD:tag_key (!<<'\\' ':') LD:tag_value)|LD:tag_key)"""
], lookupField:queue_name, sourceField:queue_name

 | fieldsRemove lookup.tag_key, lookup.tag_string, lookup.id, lookup.entity.name

| append [
  timeseries { 
  Outgoing = sum(ibmmq.queue.enqueue_cnt), by:{queue_name}, filter: { queue_name == lookup.tag_value }} 
]

[IzabelaRokita]

Hey @markmc ,
I just wanted to check in and see if you still need help with this. If so, I’d be happy to look into it for you! 😊
Please let me know what works best for you.

[markmc · Accepted solution]

Hello @IzabelaRokita 

Yeah I came back to it a couple days later and figured out the rest. I intentionally overcomplicated the scenario to explore what DQL could do. The uhh final query I came up with looked like this for anyone that might come across something similar. I wasn't expecting the query to be quite so long but it works! Probably room for improvement somewhere too but I'm good with this.

// Pull the timeseries of the request queue by tag starting with a key value tag where the key is MQ_RSP and the value is the response queue name. 
timeseries { 
  reqQueueCnt = sum(ibmmq.queue.dequeue_cnt),
    by:{ queue_name }, interval: 1m, 
    filter:{queue_name in [fetch `dt.entity.ibmmq:local_queue`
      | fieldsAdd tags, queue_name
      | expand tags
      | filter startsWith(tags,"MQ_RSP") AND endsWith(queue_name, "REQ") | fields queue_name]
    }
}

| fieldsrename reqQueueNameTimeseriesOrigin = queue_name

// Pull the request queue's tags and store into reqQueueKeyValueTag to join later on.
| lookup [
  fetch `dt.entity.ibmmq:local_queue` 
  | expand tag_string=tags
  | filter startsWith(tag_string,"MQ_RSP")
  | parse tag_string, """((LD:tag_key (!<<'\\' ':') LD:tag_value)|LD:tag_key)"""
  | fieldsRemove entity.name,id,tag_string,tag_key
], lookupField:queue_name, sourceField:reqQueueNameTimeseriesOrigin
| fieldsAdd reqQueueKeyValueTag = lookup.tag_value


// Perform a join with a timeseries on the RSP queue using the MQ_RSP provided tag value
| join [
  timeseries { 
    rspQueueCnt = sum(ibmmq.queue.enqueue_cnt), 
    by:{ queue_name }, interval: 1m, 
    filter: {queue_name in[ fetch `dt.entity.ibmmq:local_queue` 
      | expand tag_string=tags
      | filter startsWith(tag_string,"MQ_RSP")
      | parse tag_string, """((LD:tag_key (!<<'\\' ':') LD:tag_value)|LD:tag_key)"""
      | fields queue_name_rsp = tag_value]
    }
  }  
], on: { left[reqQueueKeyValueTag] == right[queue_name], timeframe }, fields: { rspQueueCnt,rspQueueNameTimeseriesOrigin = queue_name }


//Calcuate the difference of the two queues.
| fieldsAdd Difference = abs((`rspQueueCnt`[]/`reqQueueCnt`[])*100-100)
| fieldsRemove reqQueueCnt,rspQueueCnt,lookup.tag_value,reqQueueKeyValueTag

[IzabelaRokita]

That's awesome @markmc , so glad you found the right solution, and thank you for sharing it with our Community. Much appreciated 😊