Splunk Search
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Motivator

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
0 Karma
Highlighted

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

Explorer

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

Highlighted

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

Legend

Try this

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

View solution in original post

0 Karma
Highlighted

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

Explorer

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

0 Karma