Apr 16 2018
09:49 AM
- last edited on
Apr 07 2022
04:57 PM
by
TechCommunityAP
Apr 16 2018
09:49 AM
- last edited on
Apr 07 2022
04:57 PM
by
TechCommunityAP
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.
Apr 16 2018 10:37 PM
SolutionHi,
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.
Apr 22 2018 09:21 AM
Thanks Stan for answering the question. Here is another way of doing it that avoids joins and multiple table scans by using countif function
Apr 16 2018 10:37 PM
SolutionHi,
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.