SOLVED

Query Help (minus - operator)

%3CLINGO-SUB%20id%3D%22lingo-sub-183271%22%20slang%3D%22en-US%22%3EQuery%20Help%20(minus%20-%20operator)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183271%22%20slang%3D%22en-US%22%3E%3CP%3ELooking%20for%20some%20query%20assistance%2C%20is%20this%20the%20correct%20forum%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20still%20learning%20the%20query%20language%2C%20so%20bare%20with%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20the%20difference%20of%20the%20resulting%20count%20from%20two%20queries.%20I%20have%20tried%20several%20combinations%20and%20I%20can't%20seem%20to%20figure%20this%20out.%20Any%20assistance%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EWaaSDeploymentStatus%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20(UpdateCategory%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%22Feature%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TargetOSVersion%20!%3D%3C%2FSPAN%3E%3CSPAN%3E%221709%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20DetailedStatus%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Update%20successful%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E%20)-(UpdateCategory%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%22Feature%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TargetOSVersion%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%221709%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20DetailedStatus%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Update%20successful%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E%20)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-183271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-185728%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Query%20Help%20(minus%20-%20operator)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-185728%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Stan%20for%20answering%20the%20question.%26nbsp%3B%20Here%20is%20another%20way%20of%20doing%20it%20that%20avoids%20joins%20and%20multiple%20table%20scans%20by%20using%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FAggregation-functions%2Fcountif()%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ecountif%20%3C%2FA%3Efunction%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA3WOPQvCMBCGd6H%252F4cykoKCTOGRqce1QP%252BaQXGqkTUpyQSr%252BeBOL4uJwcHDP%252Bz53EaKpcOjc2KOlhgTFUMyecL%252BiRzgNShCWaVrnR87ZARPgkQEIq6BCEqZDNcWAc2BTAkKUEkPQsWO5LcS%252BF948EKSLlra7zZ6%252FN6MXR%252BFbpLo5ow%252FG2STJZ7ZcTWxN6ZPwj55%252F6GwZvLuhJFBG6zLz%252FKdh%252FTUXsxehRQXC9QAAAA%253D%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ELink%20to%20query%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EWaaSDeploymentStatus%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20UpdateCategory%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%22Feature%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20DetailedStatus%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Update%20successful%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20count1709%3Dcountif(TargetOSVersion%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%221709%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%20countOthers%3Dcountif(TargetOSVersion!%3D%3C%2FSPAN%3E%3CSPAN%3E%221709%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20diffCount%3DcountOthers-count1709%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183928%22%20slang%3D%22en-US%22%3ERE%3A%20Query%20Help%20(minus%20-%20operator)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183928%22%20slang%3D%22en-US%22%3EWorks%20perfectly%2C%20thank%20you%20Stanislav%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183488%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20Help%20(minus%20-%20operator)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183488%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20the%20following%20query%3A%3C%2FP%3E%0A%3CPRE%3Elet%20Table1%20%3D%20WaaSDeploymentStatus%20%7C%20where%20UpdateCategory%3D%3D%22Feature%22%20and%20TargetOSVersion%20!%3D%221709%22%20and%20DetailedStatus%20%3D%3D%20%22Update%20successful%22%20%20%20%7C%20count%20as%20Count1%20%7C%20extend%20dummy%3D1%20%3B%0Alet%20Table2%20%3D%20WaaSDeploymentStatus%20%7C%20where%20UpdateCategory%3D%3D%22Feature%22%20and%20TargetOSVersion%3D%3D%221709%22%20and%20DetailedStatus%20%3D%3D%20%22Update%20successful%22%20%20%7C%20count%20as%20Count2%20%7C%20extend%20dummy%3D1%3B%0ATable1%20%7C%20join%20kind%3D%20inner%20(%0A%20%20%20%20Table2%0A)%20on%20dummy%20%7C%20extend%20FinalCount%20%3D%20Count1%20-%20Count2%20%7C%20project%20FinalCount%3C%2FPRE%3E%0A%3CP%3EFirst%20it%20is%20best%20to%20split%20the%20two%20queries%20into%20sperate%20ones%20by%20using%20let%20statement%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FQuery-statements%2FLet-statement%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FQuery-statements%2FLet-statement%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EAlso%20notice%20that%20you%20will%20always%20provide%20the%20Table%20name%20in%20both%20of%20them.%20Also%20you%20will%20always%20have%20where%20clause%20when%20you%20filter.%20Additionally%20to%20the%20results%20of%20both%20queries%20you%20will%20add%20dummy%20column%20with%20the%20same%20value%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FTabular-operators%2Fextend-operator%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FTabular-operators%2Fextend-operator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThat%20way%20you%20can%20use%20join%20operator%20to%20join%20the%20results%20into%20single%20row%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FTabular-operators%2Fjoin-operator%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FTabular-operators%2Fjoin-operator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ENot%20that%20the%20different%20counts%20have%20different%20names%20so%20you%20can%20distinguish%20them%20and%20substract%20them.%20We%20are%20doing%20that%20subtraction%20in%20a%20separate%20column%20and%20at%20the%20end%20we%20only%20show%20that%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Looking for some query assistance, is this the correct forum?

 

I am still learning the query language, so bare with me.

 

I am trying to get the difference of the resulting count from two queries. I have tried several combinations and I can't seem to figure this out. Any assistance would be appreciated.

 

WaaSDeploymentStatus | where (UpdateCategory=="Feature" and TargetOSVersion !="1709" and DetailedStatus == "Update successful" | count )-(UpdateCategory=="Feature" and TargetOSVersion=="1709" and DetailedStatus == "Update successful" | count )
3 Replies
Best Response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi,

You can use the following query:

let Table1 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion !="1709" and DetailedStatus == "Update successful"   | count as Count1 | extend dummy=1 ;
let Table2 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion=="1709" and DetailedStatus == "Update successful"  | count as Count2 | extend dummy=1;
Table1 | join kind= inner (
    Table2
) on dummy | extend FinalCount = Count1 - Count2 | project FinalCount

First it is best to split the two queries into sperate ones by using let statement - https://docs.loganalytics.io/docs/Language-Reference/Query-statements/Let-statement

Also notice that you will always provide the Table name in both of them. Also you will always have where clause when you filter. Additionally to the results of both queries you will add dummy column with the same value: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/extend-operator

That way you can use join operator to join the results into single row: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/join-operator

Not that the different counts have different names so you can distinguish them and substract them. We are doing that subtraction in a separate column and at the end we only show that column.

Works perfectly, thank you Stanislav

Thanks Stan for answering the question.  Here is another way of doing it that avoids joins and multiple table scans by using countif function

 

Link to query

WaaSDeploymentStatus
| where UpdateCategory=="Feature" and DetailedStatus == "Update successful"
| summarize count1709=countif(TargetOSVersion=="1709"), countOthers=countif(TargetOSVersion!="1709")
| project diffCount=countOthers-count1709