Compare Columns Across Multiple Query Executions Using DQL

[rseibert1]

I have a workflow that has the below DQL task, I want to run and compare executions of this and create a list (of new unhealthy workers).

fetch logs
| filter contains(log.tag, "octopus_workers")
| parse content, "JSON:json"
| fieldsFlatten json
| expand json.content
| fieldsFlatten json.content
| expand json.content.alertText
| fieldsFlatten json.content.alertText
| fields timestamp, Id=json.content.alertText.Id

The table looks like:

The timestamps are only currently ever 12hrs, (via the task that ingests the logs) but I will eventually ingest logs  every 5min from workflow.

So the ability to compare Id's from previous 5min and capture Ids that are recently added.

[rseibert1]

Think maybe more than one way to do this, looking for the simplest, think if it ingests logs every 5min, I could compare the Id column from one execution to another and grab any unique ones. OR could make one DQL query that compares logs from the different times (now and -5min) but not having luck finding any examples of what I want to do.

Thanks 

[IzabelaRokita]

Hey @rseibert1 ,
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.

[t_pawlak]

Hi,
You can do it in one DQL query by splitting the last 10 minutes into two windows (current 5m vs previous 5m) and returning only IDs that appear in the current window but not in the previous one.

fetch logs
| filter contains(log.tag, "octopus_workers")
| filter timestamp >= now() - 10m
| parse content, "JSON:json"
| expand json.content
| expand json.content.alertText
| fields ts = timestamp, Id = json.content.alertText.Id
| fieldsAdd window = if(ts >= now() - 5m, "curr", else: "prev")
| summarize
    in_curr    = sum(if(window == "curr", 1, else: 0)),
    in_prev    = sum(if(window == "prev", 1, else: 0)),
    first_seen = min(ts),
    last_seen  = max(ts),
    by:{Id}
| filter in_curr > 0 and in_prev == 0
| fields Id, first_seen, last_seen
| sort last_seen desc