How to write a search to only return results where multiple values exist?

[drinkingjimmy]

I have a log output which provides many fields, but the two I'm most concerned with are user and device.

I'm trying to output a list of users and devices which corresponds with the user from the log data, only in the event that the user has more than one device associated: I.e.:

JohnSmith DeviceA
JohnSmith DeviceB
SteveSmith DeviceB
SteveSmith DeviceC
TedSmith DeviceX
TedSmith DeviceY
TedSmith DeviceZ

I don't care about users who have only one device assigned, and want to focus on users where the distinct count of devices >1.

[sundareshr · Accepted solution]

Try this

base search | stats values(device) as devices by user | where mvcount(devices)>1

[drinkingjimmy]

This seems so obvious now that I see it. Thank you!

[gokadroid]

Can you try this if you have fields called user and device to return all the users who have more than once device:

your base query to return you the user and device field
| table user, device
| stats count by user
| where count > 1

OR alternatively:

your base query to return you the user and device field
| stats values(device) as Devices, dc(device) as DevicesHeld by user
| where DevicesHeld > 1

[drinkingjimmy]

This was a good solution, but the other was a little simpler. Thanks for your time!