Query Exchange

 View Only
  • 1.  What users are logged into a specific Host

    Posted Jun 27, 2019 08:16 PM

    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);

     


    #IncidentResponse
    #Compliance
    #Community
    #Windows
    #HelpDeskOperations


  • 2.  RE: What users are logged into a specific Host

    Broadcom Employee
    Posted Jul 05, 2019 09:33 PM

     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);



  • 3.  RE: What users are logged into a specific Host

    Posted Jul 08, 2019 02:58 PM

    nice. good suggestion, thanks!



  • 4.  RE: What users are logged into a specific Host

    Broadcom Employee
    Posted Jul 09, 2019 04:18 PM


  • 5.  RE: What users are logged into a specific Host

    Posted Jul 09, 2019 04:18 PM

    I changed the query to reflect your suggestion



  • 6.  RE: What users are logged into a specific Host

    Broadcom Employee
    Posted Nov 27, 2020 06:45 PM

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



  • 7.  RE: What users are logged into a specific Host

    Posted Jan 31, 2022 09:27 AM

    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 



  • 8.  RE: What users are logged into a specific Host

    Broadcom Employee
    Posted Jan 31, 2022 05:13 PM

     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  ), 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';

     



  • 9.  RE: What users are logged into a specific Host

    Posted Feb 09, 2022 11:12 AM

    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



  • 10.  RE: What users are logged into a specific Host

    Broadcom Employee
    Posted Feb 09, 2022 04:55 PM