KQL (Kusto Query Lenguage) query to get all clients that have requests from different cities

Question

I have this query and it almost gives what i want, but i'd like to get rid of requests that have only 1 city

requests
| where url contains "GetContactInfo/BR"
| distinct client_City, url
| summarize requests = count() by client_City
| join kind=innerunique requests on client_City
| where url contains "GetContactInfo/BR"
| project url, client_City, resultCode
| sort by url desc 

Answer 1

@Vasiliy Grinko thank you for posting this question on Microsoft Q&A. The query contains the line - | distinct client_City, url which is not required. Instead, you could do something like below (I created a table variable to mimic the source table that you have shared). Note the where requests >1 line added in the query below after summarize

let testRequests = datatable(url:string, client_City:string, resultCode:int)
[
   "https://testurl1.com", "Greeley", 200,
   "https://testurl2.com", "Houston", 200,
   "https://testurl2.com", "Houston", 200,
   "https://testurl2.com", "Dallas", 200,
   "https://testurl2.com", "Dallas", 200,
   "https://testurl2.com", "Houston", 200,
   "https://testurl2.com", "Houston", 200,
   "https://testurl2.com", "Dallas", 200,
   "https://testurl3.com", "Peoria", 200,
   ];
testRequests  // -----------The table variable, defined above
| where url contains "testurl" // ------- sample URL pattern as available in the table above
//| distinct client_City, url  // ------- not required. The distinct combination will not help filter based on count of city
| summarize requests = count() by client_City
| where requests > 1          //---------------- include only cities where request > 1
| join kind=inner testRequests on client_City
| where url contains "testurl"
| project url, client_City, resultCode
| sort by url desc

This gives the result as below, (Peoria and Greeley removed from the output)

Hope this helps.

If the answer did not help, please add more context/follow-up question for it. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.