topic Re: How to write a search to only return results where multiple values exist? in Splunk Search

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

drinkingjimmy — Tue, 22 Nov 2016 21:17:20 GMT

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.

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

gokadroid — Tue, 22 Nov 2016 21:45:08 GMT

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

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

sundareshr — Tue, 22 Nov 2016 21:51:21 GMT

Try this

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

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

drinkingjimmy — Tue, 22 Nov 2016 22:11:20 GMT

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

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

drinkingjimmy — Tue, 22 Nov 2016 22:11:48 GMT

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