The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

What users are logged into a specific Host

Description:looking for what users are logged into a host, the user the type of auth, time and process

What The Data Shows: good for troubleshooting what user may have made changes that caused an issue, auditing purposes, or even incident response.

SQL: 

SELECT type,user,host,time,pid,p.name
FROM (`logged_in_users`) JOIN processes AS p USING(pid);

 

0 Votes
9 Comments
jnelson
Carbon Black Employee
Status changed to: Under Review

@coreymaygard would you consider using a JOIN to get the process name and no just the PID? Here is an example:

SELECT type,user,host,time,pid,p.name
FROM (`logged_in_users`) JOIN processes AS p USING(pid);

coreymaygard
New Contributor III

nice. good suggestion, thanks!

jnelson
Carbon Black Employee
Status changed to: Approved
 
coreymaygard
New Contributor III

I changed the query to reflect your suggestion

jnelson
Carbon Black Employee

Here is a similar query in case you are interested: https://community.carbonblack.com/t5/Query-Exchange/Process-by-user/idi-p/97414 

dale_a_brown
New Contributor II

Hi, 

Very handy thank you, what about if I want to search a large amount of computers and I only want hits if a specific user is present.

Thanks in advance 

jnelson
Carbon Black Employee

@dale_a_brown you could use something like:

SELECT type,user,host,time,pid,p.name

FROM (`logged_in_users`)
JOIN processes AS p USING(pid)
WHERE user = 'dale';

However, I have recently seen cases where the pid is "-1" which indicates that osquery did not understand the value. Since there is no such pid, the JOIN does not take place and you would get no results. Therefore, it is probably best to run it without the JOIN (sorry @coreymaygard ), but I would also convert the time to human-readable form:

SELECT type,user,host,datetime(time,'unixepoch','localtime') AS 'time',pid

FROM (`logged_in_users`)
WHERE user = 'dale';

 

dale_a_brown
New Contributor II

Hi,

SELECT type,user,host,datetime(time,'unixepoch','localtime') AS 'time',pid
FROM (`logged_in_users`)
WHERE user = 'dale';

The above will tell me if user 'dale' is currently logged in - is that correct?

What i'm searching for is if a user has ever logged on the machine i.e a user folder is present on the machine.

Thanks

jnelson
Carbon Black Employee