Osquery for Cyber Threat Detection & Incident Response

0

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

Above screen dump illustrates , Osquery is installed on windows operating system
Osquery Help Menu

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.

Interesting values to hunt on windows operating system
Extended list of table values

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;

Above screen dump illustrates ,List of active user accounts,User’s UID & GID

Below query , Provide the stats count of user accounts created on this machine.

OSquery Statements: Select Count(*) from users;

Above screen dump illustrates, Counting the list of user accounts

Below is the query to list users UID ( Unique Identifier )

OSquery Statements: Select uid from users;

Above Figure shows, Retrieval of list of UID

Below is the query to list usernames with uid.

OSquery Statements:Select uid,username from users;

List of user names with their uid levels

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;

Checking the list of running processes

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;

Processes currently used by user or system

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

Windows process tables explorer

Build your SQL statements and check for possible outbound / inbound connections from the infected machine.

OSquery Statements : select * from process_open_sockets limit 3;

Detected Listen connections for some of the process ( PID )

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.

Powershell Events

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

Child Process Parent ID Lookup

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;

Tracking Parent process ID
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 !!!


Previous articleDynamic Malware Analysis – Procmon to Extract Indicators of Compromise
Next articleLatest IOCs – Threat Actor URLs , IP’s & Malware Hashes
Balaganesh is a Incident Responder. Certified Ethical Hacker, Penetration Tester, Security blogger, Founder & Author of Soc Investigation.

LEAVE A REPLY

Please enter your comment!
Please enter your name here