DQL - Best Practice for Multiple Variable Assignment

[Moonrox]

I am working on a DQL query to gather and store 2 variables and then create a trending variable.

Please advise if this is the best way to solve this problem.

// Fetch Active problems from the last 7 days

fetch dt.davis.problems, from: now()-7d

| fieldsAdd entity_tags

| expand entity_tags

| filter event.status == "ACTIVE"

| summarize count(), alias:last7DayCount

// Fetch Active problems from the 8-14 days ago

| append [fetch dt.davis.problems, from: now()-14d, to: now()-8d

| fieldsAdd entity_tags

| expand entity_tags

| filter event.status == "ACTIVE"

| summarize count(), alias:last14DayCount]

// Combine results into a single output

| summarize sum(last7DayCount), alias:last7DayCount, sum(last14DayCount), alias:last14DayCount

| fields last7DayCount, last14DayCount

// Calculate trend using conditional logic with named parameters

| fieldsAdd trend = if(condition: last7DayCount > last14DayCount, then: "Up", else: if(condition: last7DayCount < last14DayCount, then: "Down", else: "Stable"))

// Display results

| fields last14DayCount, last7DayCount, trend

[marco_irmer]

I would suggest running a single fetch going back the full 14 days and then using summarize combined with countIf. This can be used to create two separate count, one for the last 7 days, and one for the prior 7 days (based on the start time perhaps). You can then use fieldsAdd with an if statement that compares the two counts to determine which way the trend is moving. I would also urge caution around the use of the expand command, as well as filtering on active==true in your query, as both of these might generate undesirable results depending on what you are trying to accomplish here.