Some IT shops have sysadmins logging into a Jumpbox or Bastion host (with their individual id’s) then logging into production servers as root and running commands. In such a case it can be difficult to trace command execution back to the sysadmin who ran it. One fix is to force sysadmins to login to servers with their individual id’s then use sudo. If this is not viable, then don't worry, as this article shows how Uptycs and osquery can solve the problem. We show how to trace command execution on a server back to specific user that initiated an ssh session from a client.

Take an example where a sysadmin (jwayte) logs into a bastion host (uptycs-centos1) then uses ssh to login to a PROD server (bad-ip-machine) and run a command (nc)...

Our first query we use to find a specific command from the process_events table on our desired server (command=nc and upt_hostname=bad-ip-machine in our case)...

Then we get the pid for the parent ssh session (4403 above) from the ancestor list column, we will use this to trace back to the user that run the ssh client to our server...

...enter the pid into our second query...

This query gets the remote_port for that pid from process_open_sockets on our server then finds the matching process_open_sockets.local_port records on our client machine, then join to processes then to users and now we know which user started the ssh client session (and thus ran our command -  nc)! 

The full SQL (and parameters) for the two queries is given below: 

   
   

    
    

     
     

      
      

       
       

        
        
Query parameters:

       
       
     
     
     
     

      
      

       
       

        
        
  :command (TEXT)
  :server_hostname (TEXT)
  :pid (NUMBER)
  :day (NUMBER)
       
       
     
     
    
    
   
   

   
   

    
    

     
     

      
      

       
       

        
        
--query1

       
       
     
     
     
     

      
      

       
       

        
        
select  ancestor_list 
from process_events 
where upt_day = 20200218
and upt_hostname = :server_hostname 
and  cmdline like :command
        
        


       
       
     
     
    
    
   
   

  
  

   
   

    
    

     
     

      
      

       
       
--query2

      
      
    
    
    
    

     
     

      
      

       
       
--get the client hostname from the server process_open_sockets.remote_address  
with client_hostname as (
  select distinct ia.upt_hostname as client_hostname
  from interface_addresses ia, process_open_sockets pos 
  where ia.address = pos.remote_address 
  and pos.remote_address <> ''
  and pos.upt_hostname = :server_hostname  
  and pos.upt_day = :day
  and pos.pid = :pid 
)  
--for the server pid, get the remote port and remote_address then join to client machine pos
--then join on client to processes then to users to get the username  
select distinct procs_client.upt_hostname, users_client.username client_username, pos_client.local_port port, pos_client.remote_address 
from process_open_sockets pos_client, client_hostname ch,
process_open_sockets pos_server, processes procs_client, users users_client 
where pos_client.upt_day = :day
and pos_server.upt_day = :day
and pos_server.pid = :pid
and pos_server.upt_hostname = :server_hostname
and pos_server.remote_port = pos_client.local_port 
and pos_server.remote_port <> 0 
and pos_client.upt_hostname = ch.client_hostname
and pos_client.pid = procs_client.pid 
and pos_client.upt_asset_id = procs_client.upt_asset_id 
and procs_client.upt_day = :day
and procs_client.upt_asset_id = users_client.upt_asset_id
and procs_client.uid = users_client.uid
and users_client.username <> 'root' 
      
      
    
    
   
   
  
  

Notes: 

 * Parsing the ssh pid out of the first query may soon be possibly done automatically with a Presto DB UDF

 * To handle rare midnight date crossover situations we could add additonal logic on upt_day to extend it out a day