Siem Query Languages

Introduction

Early in my SOC and threat hunting career, I was thrown into the deep end with IBM QRadar. As I recall, the handover was abrupt: “This is our SIEM, QRadar. It’s yours now – good luck!” Faced with an unfamiliar system, I quickly realized the immense value of cheat sheets, a concept I’d come to rely on throughout my journey.

Cheat sheets are indispensable tools for navigating complex systems like SIEMs, particularly when you’re working with a platform you’re unfamiliar with. While they won’t magically transform you into an expert, they offer a practical approach to rapidly acquire operational knowledge and build confidence. Think of them as valuable aids that help you dissect intricate SIEMs and accelerate your learning curve.

What is a Cheat Sheet?

A cheat sheet is a concise and readily accessible reference guide that distills essential information about a specific subject, skill, or tool into an easily digestible format. It provides quick access to key facts, formulas, commands, or best practices, enabling users to efficiently recall and apply knowledge without wading through extensive documentation or relying solely on memory.

In essence, a cheat sheet serves as both a memory aid and a productivity booster. It’s an invaluable asset for anyone seeking to quickly grasp the essentials of a topic, overcome learning hurdles, or streamline their workflow. Whether it’s a condensed summary of programming syntax, a quick reference for keyboard shortcuts, or a guide to troubleshooting common issues, a cheat sheet empowers users to perform tasks more effectively and with greater confidence.

Can I Survive on Cheat Sheets Alone?

Absolutely not! And that’s the key intention behind this article. Cheat sheets are a fantastic way to gain initial exposure to various technologies, offering a glimpse into their core functionalities. Beyond simply getting you up and running, I believe cheat sheets provide essential reference points when you need to delve into and truly understand the official documentation – which, let’s face it, can often be uninspiring to pick up and read.

Overview of SIEM Query Languages

Cheat sheets are undeniably important. What I wish I had access to when I was starting out was an overview of multiple SIEM query languages, coupled with syntax examples and readily available, useful references. So, here’s my attempt to provide just that:

1. Kusto Query Language (KQL) - Microsoft Sentinel & Defender

KQL is optimized for efficiently querying structured log data and bears a resemblance to PowerShell in its syntax and logic flow.

Features

• Read-only query language with powerful aggregation functions
• Supports time-series analysis and anomaly detection
• Integrated with Microsoft Defender, Sentinel, and other security tools

Example Query

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625  // Failed logon attempts
| summarize count() by Account, bin(TimeGenerated, 1h)
| order by count_ desc

Resources

• Official Documentation
• Kusto Detective Agency
• KC7 Training
• KQL Cheat Sheet

It’s worth noting that Microsoft Sentinel is built on top of Azure Monitor and uses Log Analytics workspaces to store data. Furthermore, Microsoft Defender XDR data can be queried within Microsoft Sentinel when configured correctly, providing a unified view of your security landscape.

2. Splunk Processing Language (SPL) - Splunk

SPL is a pipeline-based language specifically designed for flexible log searching and powerful data visualization.

Features

• Uses pipelines (|) to chain search commands together.
• Supports advanced statistical functions and visualization tools for in-depth analysis.
• Handles both structured and unstructured data with ease.

Example Query

index=security sourcetype=windows_security
| search EventCode=4625
| stats count by user, _time
| sort -count

Resources

• Official Documentation
• Splunk Beginner Cheatsheet
• Splunk SPL cheatsheet
• Splunk Cheat Sheet: Search and Query Commands
• Splunk Cheat Sheet

3. Elastic Query DSL - OpenSearch Query DSL

OpenSearch and Elastic uses a powerful, JSON-based query language called Query DSL (Domain Specific Language) to search and analyze data. Query DSL allows you to build complex queries with a structured syntax, making it ideal for programmatic use and automation. In all essence, it is an JSON representation of a query. One nifty feature is that you can easily store DSL’s into files and share them. The receiver can then just simply copy and paste the DSL query into either Elastic or OpenSearch DSL search feature!

Features

• Supports full-text search, aggregations, and filtering for comprehensive analysis.
• Designed for structured, programmatic searches using JSON format.
• Enables complex queries with boolean logic, range filters, and more.

Example Query (Query DSL)

{
  "query": {
    "bool": {
      "must": [
        { "match": { "event_id": 4625 } }
      ],
      "filter": {
        "range": { "@timestamp": { "gte": "now-1d/d" } }
      }
    }
  }
}

Resources

• OpenSearch Query DSL Documentation
• Lucene Query Syntax
• OpenSearch Labs & Training

4. Kibana Query Language (KQL) - Elastic Stack / OpenSearch

KQL is natively used within Kibana for Elasticsearch and also seamlessly operates within OpenSearch.

Features

• Employs a human-readable syntax with intelligent auto-completion features.
• Significantly simplifies filtering within dashboards, enhancing the user experience.

Example Query

event.category: "authentication" and event.outcome: "failure"

Resources

• KQL Documentation
• Elastic Stack Training
• Kibana Cheat Sheet

5. QRadar Query Language (AQL) - IBM QRadar

AQL is IBM QRadar’s Ariel Query Language, which adopts a SQL-like structure optimized for searching through security event data.

Features

• Employs a familiar SQL-like syntax, making it accessible to many analysts.
• Supports real-time event analysis, enabling immediate threat detection.
• Provides deep filtering capabilities for precise investigation.

Example Query

SELECT LOGSOURCENAME(logsourceid) AS LogSource, username, COUNT(*)
FROM events
WHERE QIDNAME(qid)='Failed Login'
AND starttime >= CURRENT_TIMESTAMP - 86400
GROUP BY LogSource, username
ORDER BY COUNT(*) DESC

Resources

• Ariel Query Language Guide
• QRadar Labs
• AQL Cheat Sheet
• Qradar Cheat Sheet

6. Sigma Rules - Vendor-Agnostic Detection Format

Sigma is not a query language itself, but rather a versatile rule format designed to describe security detections in a standardized manner, making them convertible into SIEM-specific queries.

Features

• Utilizes a human-readable YAML-based rule format.
• Enables conversion to KQL, SPL, OpenSearch DSL, and numerous other query languages.
• Remains community-driven and extensible, adapting to the ever-changing threat landscape.

Example Sigma Rule:

title: Failed Logon Attempts
logsource:
  product: windows
  service: security
  category: authentication
selection:
  EventID: 4625
condition: selection

Resources

• Sigma Project Repository
• Sigma Documentation
• Sigma Converter Tools

Conclusion

This article isn’t designed to make you an overnight expert in any specific SIEM query language. Instead, the intent is to share an approach to quickly and simply gain familiarity with essential languages that you might encounter as a threat hunter. My personal experience with cheat sheets is that they serve as a great way to familiarize myself with essential technologies and quickly understand the fundamentals.

The key takeaway is that while cheat sheets provide a fantastic starting point and ongoing reference, they are a stepping stone to deeper understanding. By using them to grasp the core concepts and then diving into the official documentation, you can effectively build a strong foundation in any SIEM query language and enhance your capabilities as a threat hunter. Embrace cheat sheets as a tool to accelerate your learning and navigate the complex world of cybersecurity with greater confidence and efficiency.