Osquery is an open-source, cross-platform tool that allows you to query your operating system as if it were a relational database. Using SQL-based queries, Osquery provides insights into your system’s configuration, status, and activities, making it an invaluable tool for system monitoring, security auditing, and incident response.
In this tutorial, we’ll cover how to install, configure, and use Osquery to monitor the security of an Ubuntu 22.04 server.
Step 1 – Update the System
Keeping your system updated is crucial for security. Regular updates provide the latest security patches and software enhancements.
Ensure your system is up to date:
apt update -y
apt upgrade -yStep 2 – Install Osquery
Osquery is not available in the default Ubuntu repositories, so you need to add its repository.
Download and add the repository GPG key:
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys $OSQUERY_KEYThis command ensures the authenticity of the packages from the Osquery repository. GPG keys help verify that the software you’re installing is legitimate and hasn’t been tampered with.
add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'This command adds the Osquery repository to your system. Adding the repository ensures you get the latest version of Osquery directly from the source.
Now, install Osquery using the apt command:
apt install osquery -yStep 3 – Configure Osquery
Creating a configuration file is the first step in customizing Osquery’s behavior. Let’s create a new configuration file:
mkdir -p /etc/osquery
nano /etc/osquery/osquery.confAdd the following content:
{
"options": {
"config_plugin": "filesystem",
"logger_plugin": "filesystem",
"logger_path": "/var/log/osquery",
"disable_logging": "false",
"log_result_events": "true",
"schedule_splay_percent": "10",
"worker_threads": "2",
"enable_monitor": "true"
},
"schedule": {
"system_info": {
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"interval": 3600
},
"processes": {
"query": "SELECT * FROM processes WHERE on_disk = 0;",
"interval": 300
}
},
"packs": {
"osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf"
}
}This configuration sets Osquery to log its results and run scheduled queries. The options section configures logging and performance, while the schedule section defines periodic queries.
Create log directory and set permissions:
mkdir -p /var/log/osquery
chown -R syslog:adm /var/log/osqueryStep 4 – Start and Enable Osquery
Enable and start the Osquery daemon:
systemctl enable osqueryd
systemctl start osquerydCheck the service status:
systemctl status osquerydThe status command provides information about the service’s current state.
● osqueryd.service - The osquery Daemon
Loaded: loaded (/lib/systemd/system/osqueryd.service; disabled; vendor preset: enabled)
Active: active (running) since Fri 2024-06-21 03:33:42 UTC; 4s ago
Process: 4492 ExecStartPre=/bin/sh -c if [ ! -f $FLAG_FILE ]; then touch $FLAG_FILE; fi (code=exited, status=0/SUCCESS)
Process: 4494 ExecStartPre=/bin/sh -c if [ -f $LOCAL_PIDFILE ]; then mv $LOCAL_PIDFILE $PIDFILE; fi (code=exited, status=0/SUCCESS)
Main PID: 4495 (osqueryd)
Tasks: 14 (limit: 4579)
Memory: 14.0M
CPU: 110ms
CGroup: /system.slice/osqueryd.service
├─4495 /opt/osquery/bin/osqueryd --flagfile /etc/osquery/osquery.flags --config_path /etc/osquery/osquery.conf
└─4497 /opt/osquery/bin/osqueryd "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">Step 5 – Querying with Osquery
You can use Osquery’s interactive shell to run queries and explore your system.
Let’s launch the Osquery interactive shell:
osqueryiThe interactive shell allows you to run ad-hoc queries and inspect your system in real-time. It’s a powerful tool for on-the-fly analysis.
osquery> Run a query to list all users:
SELECT username, description FROM users;This query lists all users on the system. It’s useful for auditing user accounts and their descriptions.
+------------------+------------------------------------+
| username | description |
+------------------+------------------------------------+
| root | root |
| daemon | daemon |
| bin | bin |
| sys | sys |
| sync | sync |
| games | games |
| man | man |
| lp | lp |
| mail | mail |
| news | news |
| uucp | uucp |
| proxy | proxy |
| www-data | www-data |
| backup | backup |
| list | Mailing List Manager |
| irc | ircd |
| gnats | Gnats Bug-Reporting System (admin) |
| nobody | nobody |
| _apt | |
| systemd-network | systemd Network Management,,, |
| systemd-resolve | systemd Resolver,,, |
| messagebus | |
| systemd-timesync | systemd Time Synchronization,,, |
| pollinate | |
| sshd | |
| syslog | |
| uuidd | |
| tcpdump | |
| tss | TPM software stack,,, |
| landscape | |
| usbmux | usbmux daemon,,, |
| lxd | |
| ntp | |
+------------------+------------------------------------+Run a query to check for listening ports:
SELECT * FROM listening_ports;This query shows all open network ports.
+------+------+----------+--------+-------------------------+-----+--------+--------------------------------------------+---------------+
| pid | port | protocol | family | address | fd | socket | path | net_namespace |
+------+------+----------+--------+-------------------------+-----+--------+--------------------------------------------+---------------+
| 584 | 53 | 6 | 2 | 127.0.0.53 | 14 | 19806 | | 4026531992 |
| 1101 | 22 | 6 | 2 | 0.0.0.0 | 3 | 22415 | | 4026531992 |
| 1101 | 22 | 6 | 10 | :: | 4 | 22417 | | 4026531992 |
| 584 | 53 | 17 | 2 | 127.0.0.53 | 13 | 19805 | | 4026531992 |
| 745 | 123 | 17 | 2 | 209.23.8.109 | 19 | 20237 | | 4026531992 |
| 745 | 123 | 17 | 2 | 127.0.0.1 | 18 | 20235 | | 4026531992 |
| 745 | 123 | 17 | 2 | 0.0.0.0 | 17 | 20231 | | 4026531992 |Exit the Osquery shell:
.exitStep 6 – Automating Security Monitoring
To automate and extend your security monitoring, consider using additional query packs and integrating Osquery with centralized logging and alerting systems like ELK Stack or Splunk.
Download and install additional query packs:
wget -P /usr/share/osquery/packs/ https://raw.githubusercontent.com/osquery/osquery/master/packs/osquery-monitoring.confQuery packs contain predefined queries for specific monitoring tasks. They simplify setting up comprehensive security checks.
Update the packs section in /etc/osquery/osquery.conf to include the new packs:
"packs": {
"osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
"incident-response": "/usr/share/osquery/packs/incident-response.conf"
}Conclusion
You now have Osquery installed and configured on your Ubuntu 22.04 server. By using Osquery, you can monitor various aspects of your system in real-time, aiding in security and compliance efforts. Customize your queries and schedules to fit your specific needs, and integrate Osquery with other tools for enhanced monitoring and alerting. You can now use Osquery for security auditing on dedicated server hosting from Atlantic.Net!
