If you have a sample search such as the below
sourcetype=HOSTS | stats values(user) as USERS_OF_COMPUTER dc(user) as TOTAL_USERS_OF_COMPUTER by HOSTNAME | where TOTAL_USERS_OF_COMPUTER > 1
You will get a table containing a computer, a list of users associated with this device, and a count of how many users, providing the count is greater than 1.
I want to be able to search for user John.Smith, and come up with all the computers that contain John.Smith as a user, as well as all the other users associated with this computer.
Currently if I just add "user=john.smith" to the search I get no results in my table, I would get results if I removed the greater than 1 requirement, but only for that specific user, I want to see all the other users displayed in the same table.
Any suggestions would be helpful. Thanks!
sourcetype=HOSTS
[ search sourcetype=HOSTS user=john.smith | table HOSTNAME | dedup HOSTNAME]
| stats values(user) as USERS_OF_COMPUTER
dc(user) as TOTAL_USERS_OF_COMPUTER
by HOSTNAME
| where TOTAL_USERS_OF_COMPUTER > 1
The subsearch produces a table of HOSTNAME
values where user=john.smith
is in the records. The implicit "format
" command as a result of the square brackets [ ]
returns that list of values in formatted query language such as...
(HOSTNAME="host1" OR HOSTNAME="host2" OR ....)
If you want to see what exactly the resulting query clause looks like, then just run an explicit format like this
sourcetype=HOSTS user=john.smith | table HOSTNAME | dedup HOSTNAME | format