Osquery is a great tool that helps cybersecurity responders to explore the operating system’s malicious behaviors such as running processes, network sockets, user anomalies, and more. It works with relational databases and it is completely free to install. Let us get started
Quick Installation:
Osquery can be used to explore various operating systems such as Windows, Linux, and Mac. Please download your favorite version here, Here I have downloaded & installed it for windows.
After installation, Start the Powershell as administrator > Navigate to the Osquery download path >Start the osquery with .\osqueryi.exe
To get started, Just Type .help which will provide you the list of functionality and use of this tool. Let us explore some of them and query them on the operating system to get possible results.
Explore the Tables on Windows
Exploring the table by typing .tables which will provide the list of operating systems functions and we will query some of them to retrieve possible data from the affected machine.
Above is the list of table names explored by osquery on this operating system.Let us pick , Some interesting table names with SQL queries.
OSquery – List of Active Users
To enumerate the list of users on the machine. Hunt for possible UID & GID for users rights, Check for users having administrator rights, and more. Use below query
OSquery Statements : Select * from users limit 8;
Below query , Provide the stats count of user accounts created on this machine.
OSquery Statements: Select Count(*) from users;
Below is the query to list users UID ( Unique Identifier )
OSquery Statements: Select uid from users;
Below is the query to list usernames with uid.
OSquery Statements:Select uid,username from users;
Above query is to perform an sting operation on the database with syntax, like ‘%Hunt your data Here%’
Osquery Statement: Select * from users WHERE description like ‘%windows%’;
OSquery – List of processes
List the number of processes running on the user machine and filter them accordingly to find the bad actor. Here we are exploring the list of processes up and running and using “limit” to pull the specific number of records on the console.
OSquery Statements for processes Retrieval : SELECT * from Processes limit 5;
Below query to merge the username and their respective running processes.
Osquery Statement : Select p.pid,p.name,u.username from processes p join users u on u.uid=p.uid limit 5;
Removing the “limit” will provide all the list of processes currently running for all users.
OSquery – Network connections of active processes
Check for suspicious network sockets and hunt for connections established from local to remote IP addresses.Before that explore the windows process tables by typing, .tables process
Build your SQL statements and check for possible outbound / inbound connections from the infected machine.
OSquery Statements : select * from process_open_sockets limit 3;
Above Screen Dump shows the connections state is listening, We are interested to look more on well-established connections for any relevant IP addresses.
Now , we have changed the SQL statements to filter out with <> ‘ ‘ ( Less than or Greater than null ) which returns some numbers , Also logical gates ( AND ) with != ( not-equal-to operator ) to return suspicious connections.
OSquery Statements :Select * from Process_open_sockets where remote_address <> ‘ ‘ and remote_address !=’0.0.0.0’ limit 3;
OSquery – Open Connections to Remote IP Address on Specific Port
Check for the open connections of external/internal with the table name process_open_sockets for a specific port, Here we are checking for NETBIOS. Build your SQL statements according to your incidents.
OSquery :select * from process_open_sockets where local_port=138 and remote_address !=’0.0.0.0′ and remote_address !=’::’;
To exclude the ipv6, we have used ‘::’; this will the exclude ipv6 IP address from the results console.As we are using != ( not-equal-to operator )
OSquery – Registry Analysis for suspicious behaviors
Check for suspicious registry key value modifications and find interesting artifacts. Preparing a query & save your incident response time and know the persistence of malware in the registry.
OSquery : Select data , path from registry where key = ‘HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders’;
OSquery – Malware Startup on Boot
Check the windows processes that are starting on boot as malware persistence behaviors. Detect such malicious activates and isolate the machine from the network and do possible forensics analysis for root-cause.
OSquery : Select * from services where start_type=’DEMAND_START’ or start_type=’AUTO_START’
OSquery – Suspicious Powershell Events
Rather than event logs, Check for PowerShell events to get more insights on ongoing attacks and harden your windows server with good security practices. Create policies to block Powershell access to normal users.
OSquery : Select * from powershell_events;
The administrator has to enable PowerShell event logging to hunt suspicious activates. A proactive approach is always good than a reactive one.
OSquery – Tracking suspicious windows processes
Track a suspicious behavior of Child and Parent process ID’s .
Osquery: select pid, name ,path ,parent from processes where name=’services.exe’;
The below query is to find the Parent process which is leveraging the services.exe , while checking we found it as wininit.exe
Osquery: Select pid, name ,path, parent from processes where pid=996;
Also Read : Top Windows Security Events Logs You Must Monitor
Conclusion
Osquery is a good tool for incident responders to hunt the windows, mac, and Linux environments of malicious behaviors.OSquery events can also be pushed to your SIEM for better incident handling and response. Use osquery software and build a proactive rule on your SIEM and compare the results with your EDR. Correlating OSquery events with the existing EDR will provide more insights into the bad actor.
Happy Hunting !!!