How can I combine duplicates of 1 column, then have multiple results in the same row of another column?

Question

I am very new to kql, and i am stuck on this query. I am looking to have query display which users have had sign-ins from different states. I created this query, but i do not know how to count the results in the column "names".

SigninLogs
| project tostring(LocationDetails.state), UserDisplayName
| extend p =pack( 'Locations', LocationDetails_state)
| summarize names = make_set(p)  by UserDisplayName

This generates a column "names" with a row like so:

[{"Locations":"Arkansas"},{"Locations":"Iowa"},{"Locations":""}]

Here is a simple query that grabs all sign-ins from users and another column with the locations.

SigninLogs
| where ResultType == "0"
| summarize by UserDisplayName, tostring(LocationDetails.state)

Is there a way to combine the duplicates of users column, and then display each location in the second? If so, could i count each location in order to filter by where location is > 1?

Yoni L. · Accepted Answer · 2020-12-17 02:13:21Z

0

I am looking to have query display which users have had sign-ins from different states

Assuming I understood your question correctly, this could work (using array_length()):

SigninLogs
| project State = tostring(LocationDetails.state), UserDisplayName
| summarize States = make_set(State) by UserDisplayName
| where array_length(States) > 1 // filter users who had sign-ins from *more than 1 state*

answered Dec 17, 2020 at 2:13

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ven Over a year ago

Thank you! this works, i felt like i tried this but i guess not

Collectives™ on Stack Overflow

How can I combine duplicates of 1 column, then have multiple results in the same row of another column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related