Audit Endpoints Using osquery: SQL-Powered Host Visibility in 2025
Why Audit Endpoints Matter in 2025 (And Why You Should Care)
Let’s be real: if you’re running anything online—cloud VMs, Docker containers, VPS, or even a beefy dedicated server—you’re basically painting a target on your back. Attackers, compliance auditors, and even your own devs (oops) can all introduce risk. And in 2025, with the explosion of remote work, microservices, and “everything as code,” knowing what’s happening on your endpoints isn’t just nice-to-have—it’s essential.
But here’s the kicker: most people still treat endpoint auditing like a chore. “Install an agent, hope for the best, and pray the logs make sense.” That’s where osquery comes in—a tool that lets you ask questions about your systems using good old SQL. Imagine running SELECT * FROM processes WHERE name='nginx'; on every server, container, or laptop you manage, and getting instant, structured answers. That’s the power of osquery.
This post is your practical, no-BS guide to using osquery for endpoint auditing in 2025. Whether you’re spinning up a new VPS (try this), managing a Docker swarm, or wrangling a fleet of dedicated servers (these), you’ll learn how to get visibility, catch problems, and automate your way to sanity.
What’s the Problem? Why Is Endpoint Auditing So Hard?
- Too many moving parts: Modern infrastructure is a mix of cloud VMs, containers, bare metal, and laptops. Each has its own quirks.
- Traditional tools are clunky: Old-school log shippers and agents are slow, hard to configure, and don’t speak a common language.
- Compliance is a pain: PCI, HIPAA, GDPR, SOC2… they all want proof that you’re watching your endpoints.
- Attackers are sneaky: Malware, rootkits, and insider threats can hide in plain sight if you’re not looking closely.
So, how do you get real visibility, without drowning in logs or spending a fortune?
Enter osquery: SQL for Your Infrastructure
osquery is an open-source tool from Facebook (now Meta), designed to turn your endpoints into databases you can query with SQL. It runs on Linux, Windows, and macOS, and works just as well in a Docker container as on a physical server.
Official site: https://osquery.io/
How Does osquery Work?
- Agent-based: You install osquery on your endpoint (VM, container, server, laptop).
- Virtual tables: It exposes system data (processes, users, network, file integrity, etc.) as SQL tables.
- Query engine: You run SQL queries to get real-time or scheduled data.
- Flexible output: Results can be logged locally, shipped to a central server, or integrated with SIEMs.
Think of it as “SELECT * FROM your_server WHERE bad_stuff=true;”
Three Big Questions (And Answers!)
1. How Does osquery Actually Audit Endpoints?
- It collects data from the OS (processes, users, network connections, file hashes, etc.).
- It can monitor changes in real time (e.g., new processes, modified files).
- You can schedule queries to run every X minutes/hours, or trigger them on events.
- It outputs results as structured logs, which you can parse, alert on, or send to your SIEM.
2. How Do I Set Up osquery Quickly (Without Losing My Mind)?
- Install the package (apt, yum, brew, or Docker).
- Write a config file with the queries you care about.
- Start the daemon (osqueryd) or run interactive queries (osqueryi).
- Send logs wherever you want (filesystem, syslog, remote server).
3. What’s the Catch? Any Gotchas or Limitations?
- osquery is powerful, but you need to know what to ask (SQL skills help).
- Some tables are OS-specific (not all queries work everywhere).
- It’s not a full EDR (Endpoint Detection & Response) suite, but it’s a killer foundation.
How osquery Works: Under the Hood
osquery is basically a lightweight daemon that exposes your system as a database. Here’s the basic structure:
- osqueryd: The background service for scheduled queries and logging.
- osqueryi: The interactive shell for ad-hoc queries (great for debugging).
- Config file: Defines what to collect, how often, and where to send results.
- Plugins: For logging, remote management, and more.
Example: Want to see all users with sudo access?
SELECT * FROM users WHERE uid = 0;
Or, check for suspicious processes:
SELECT pid, name, path FROM processes WHERE name LIKE '%crypto%';
Or, monitor file changes in /etc:
SELECT * FROM file_events WHERE action = 'UPDATED' AND path LIKE '/etc/%';
Quick Setup: osquery in 5 Minutes
1. Install osquery
- Ubuntu/Debian:
sudo apt-get update
sudo apt-get install osquery
- CentOS/RHEL:
sudo yum install osquery
- macOS:
brew install osquery
- Docker:
docker run --rm -it --privileged --pid=host \
-v /:/host:ro \
--workdir=/host \
osquery/osquery:latest osqueryi
2. Try Interactive Queries
osqueryi
SELECT * FROM processes LIMIT 5;
3. Set Up Scheduled Queries
Create a config file (e.g., /etc/osquery/osquery.conf):
{
"schedule": {
"processes": {
"query": "SELECT pid, name, path FROM processes;",
"interval": 600
},
"users": {
"query": "SELECT * FROM users;",
"interval": 3600
}
}
}
4. Start the Daemon
sudo systemctl start osqueryd
5. Check the Logs
tail -f /var/log/osquery/osqueryd.results.log
That’s it! You’re now auditing your endpoint with SQL.
Practical Examples and Use Cases
| Use Case | osquery Example | Advice |
|---|---|---|
| Detect new users | SELECT * FROM users WHERE uid > 1000; |
Alert on new accounts, especially with sudo rights. |
| Monitor running Docker containers | SELECT * FROM docker_containers; |
Spot rogue or unexpected containers. |
| File integrity monitoring | SELECT * FROM file_events WHERE action='UPDATED'; |
Track changes in sensitive directories. |
| Check for listening ports | SELECT * FROM listening_ports; |
Find unexpected open services. |
| Audit installed packages | SELECT * FROM packages; |
Spot unauthorized software installs. |
Positive and Negative Cases (With Tips!)
Positive Example: Catching a Rogue Process
You schedule a query for processes with suspicious names (SELECT * FROM processes WHERE name LIKE '%miner%';). One day, you spot a crypto miner running. You kill it, investigate, and patch the vulnerability. osquery saves the day!
Negative Example: Too Many Logs, Not Enough Context
You schedule every possible query, and your log files explode. You can’t find the signal in the noise. Tip: Start small—pick 3-5 high-value queries, and expand as you learn what matters for your environment.
Comparison Table: osquery vs. Traditional Auditing Tools
| Feature | osquery | Auditd (Linux) | Sysmon (Windows) |
|---|---|---|---|
| Cross-platform | Yes | No | No |
| SQL interface | Yes | No | No |
| Real-time & scheduled | Yes | Real-time only | Real-time only |
| Extensible | Yes (plugins, custom tables) | Limited | Limited |
| Learning curve | Medium (SQL) | High | Medium |
Beginner Mistakes and Common Myths
- Myth: “osquery is only for big companies.”
Reality: It’s perfect for small teams and solo admins too. - Myth: “It’s hard to use.”
Reality: If you know basic SQL, you’re 90% there. - Mistake: Collecting too much data.
Tip: Start with a handful of focused queries. - Mistake: Not securing the config/logs.
Tip: Treat osquery configs and logs as sensitive data. - Mistake: Forgetting to update osquery.
Tip: Stay current—new tables and bug fixes drop regularly.
Similar Solutions and Alternatives
- Auditd (Linux): Kernel-level auditing, but config is complex and output is raw.
- Sysmon (Windows): Great for Windows, but no SQL and not cross-platform.
- Wazuh: Adds a management layer over osquery and other tools. More features, more complexity.
- Falco: Real-time container security, but focused on runtime events, not SQL queries.
osquery stands out for its SQL interface, cross-platform support, and flexibility.
Interesting Facts and Non-Standard Usage
- osquery in CI/CD: Run security checks on build servers or containers before deployment.
- osquery for Inventory: Use it to build a live inventory of hardware, software, and users across your fleet.
- osquery + Grafana: Visualize query results in dashboards for real-time monitoring.
- osquery as a “canary”: Plant fake files or users, and alert if they’re touched.
Automation and Scripting: New Opportunities
- Automate compliance checks (e.g., “Are all servers patched?”).
- Integrate with Slack or email for instant alerts (“New user created!”).
- Trigger remediation scripts based on query results (e.g., kill rogue processes).
- Use osquery’s remote management features (Fleet, Kolide) to manage large environments.
With osquery, you can turn your infrastructure into a living, queryable database—perfect for automation, self-healing scripts, and proactive monitoring.
Conclusion: Why osquery Should Be Your Go-To in 2025
If you care about security, compliance, or just knowing what the heck is happening on your servers, osquery is a must-have. It’s free, open-source, cross-platform, and speaks the universal language of SQL. Whether you’re running a single VPS (get one here) or a global fleet of dedicated servers (order here), osquery gives you the visibility and control you need—without the pain of legacy tools.
- Start with a few high-value queries (users, processes, network, files).
- Automate alerts and integrate with your favorite tools.
- Expand as you learn—osquery grows with you.
In 2025, endpoint visibility isn’t optional. With osquery, it’s not just possible—it’s easy, powerful, and even a little bit fun. Happy querying!
Official osquery docs: https://osquery.io/docs/
This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.
This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.
