SOLVED

Union operator return results out of sequence in Azure Log Analytics Query

%3CLINGO-SUB%20id%3D%22lingo-sub-206870%22%20slang%3D%22en-US%22%3EUnion%20operator%20return%20results%20out%20of%20sequence%20in%20Azure%20Log%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206870%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI'm%20working%20on%20the%20following%20query.%20The%20union%20will%20give%20me%20count%20of%20the%20message%20in%20out%20of%20sequence%20fashion%20always%20for%20e.g%20The%20expected%20behavior%26nbsp%3Bshould%20be%20that%20it%20should%20return%20results%20as%20count%20of%20xyz%20message%20first%2C%20then%20abc%20and%20then%20pqr%2C%20but%20the%20results%20are%20always%20out%20of%20sequence.%20Please%20help%20me%20out%20on%20this%20just%20wan%20to%20know%20why%20is%20this%20happening%20and%20how%20to%20obtain%20result%20in%20the%20same%20order%20as%20the%20statements%20are%20executed.%20Thanks%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Esearch%3C%2FSPAN%3E%3CSPAN%3Ein%20(traces)%20message%20%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22xyz%22%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3Etimestamp%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E())%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Esearch%3C%2FSPAN%3E%3CSPAN%3Ein%20(traces)%20message%20%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22abc%22%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3Etimestamp%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E())%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Esearch%3C%2FSPAN%3E%3CSPAN%3Ein%20(traces)%20message%20%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22pqr%22%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3Etimestamp%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E())%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThis%20should%20output(expected%20result)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ecnt_%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E13%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E2%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ebut%20the%20result%20is%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E13%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E2%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThis%20sequence%20will%20always%20change%20whenever%20I%20execute%20the%20query.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-206870%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217369%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20operator%20return%20results%20out%20of%20sequence%20in%20Azure%20Log%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217369%22%20slang%3D%22en-US%22%3E%3CP%3EBTW%20it%20is%20good%20not%20to%20use%20search%20as%20this%20slows%20down%20queries.%20Query%20like%20this%20is%20better%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Etraces%20%7C%20where%20message%20has%20%22xyz%22%20and%20%20timestamp%20%26gt%3B%20ago(1d)%20%7C%20summarize%20count()%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217366%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20operator%20return%20results%20out%20of%20sequence%20in%20Azure%20Log%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217366%22%20slang%3D%22en-US%22%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217365%22%20slang%3D%22en-US%22%3ERe%3A%20Union%20operator%20return%20results%20out%20of%20sequence%20in%20Azure%20Log%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217365%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20probably%20the%20easier%20solution%20for%20this%20is%20%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20result1%20%3D%20search%20in%20(traces)%20message%20%3A%20%22New%20Request%20Received%22%20and%20timestamp%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20count()%20%7C%20extend%20rank%20%3D%201%3B%0Alet%20result2%20%3D%20search%20in%20(traces)%20message%20%3A%20%22Listing%20Customers%22%20and%20timestamp%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20count()%7C%20extend%20rank%20%3D%202%3B%0Alet%20result3%20%3D%20search%20in%20(traces)%20message%20%3A%20%22pqr%22%20and%20timestamp%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20count()%7C%20extend%20rank%20%3D%203%3B%0A%20result1%20%7C%20union%20%20result2%2C%20result3%20%7C%20sort%20by%20rank%20asc%3C%2FPRE%3E%0A%3CP%3ESeems%20like%20union%20does%20not%20care%20on%20the%20order%26nbsp%3Bof%20how%20you%20stitch%20results.%20Probably%20this%20is%20done%20due%20to%20performance%20reasons.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi,
 
     I'm working on the following query. The union will give me count of the message in out of sequence fashion always for e.g The expected behavior should be that it should return results as count of xyz message first, then abc and then pqr, but the results are always out of sequence. Please help me out on this just wan to know why is this happening and how to obtain result in the same order as the statements are executed. Thanks
 
union
(search in (traces) message : "xyz" and timestamp > ago(1d)
| summarize count()),
(search in (traces) message : "abc" and timestamp > ago(1d)
| summarize count()),
(search in (traces) message : "pqr" and timestamp > ago(1d)
| summarize count())
 
This should output(expected result)
cnt_
13
2
4
 
but the result is
4
13
2
 
This sequence will always change whenever I execute the query.
3 Replies
best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi 

I think probably the easier solution for this is :

 

let result1 = search in (traces) message : "New Request Received" and timestamp > ago(1d)
| summarize count() | extend rank = 1;
let result2 = search in (traces) message : "Listing Customers" and timestamp > ago(1d)
| summarize count()| extend rank = 2;
let result3 = search in (traces) message : "pqr" and timestamp > ago(1d)
| summarize count()| extend rank = 3;
 result1 | union  result2, result3 | sort by rank asc

Seems like union does not care on the order of how you stitch results. Probably this is done due to performance reasons.

BTW it is good not to use search as this slows down queries. Query like this is better:

 

traces | where message has "xyz" and  timestamp > ago(1d) | summarize count()