[Sigma]

Overview

This page is set out to establish some guidelines for writing SQL. There are specific sections for details about writing Canned Queries

General
These are general rules to follow when writing any SQL. Unless stated otherwise you should attempt to follow these guidelines.

SQL Keywords should be FULL CAPS

• This will be for all reserved keywords within SQL.
  


• A full list can be found at: https://www.w3schools.com/sql/sql_ref_keywords.asp
  

Indentation should be 4 spaces

• You can set VS Code to a custom tab size per filetype by:
  • Ctrl + Shift + P, and search for "Configure Language Specific Settings"
    
  
  
  • Find SQL, and click it - You should be taken to a settings.json
    
  
  
  • Add the following JSON to the SQL node
    • "editor.tabSize": 4
      
    
    
    • Share settings.json with tabSize
      
    
    
    
    
  
  
  
  

Always select specific columns

• When querying, you should always explicitly declare which fields you wish to return
  


• "SELECT * ..." shouldn't be used, as it's not explicit what you're selecting
  

Datatypes should be FULL CAPS

• A list of endpoint specific datatypes is available at: https://www.sqlite.org/datatype3.html
  


• A list of datalake specific datatypes is available at: https://trino.io/docs/current/language/types.html?highlight=data%20types
  

Non-SQL keywords and function calls should be FULL CAPS

• SQLite keywords - https://www.sqlite.org/keyword_index.html
  • Scalar functions - https://sqlite.org/lang_corefunc.html
    
  
  
  • Datetime functions - https://sqlite.org/lang_datefunc.html
    
  
  
  • Aggregate functions - https://sqlite.org/lang_aggfunc.html
    
  
  
  • JSON functions - https://sqlite.org/json1.html
    
  
  
  
  


• osquery keywords - https://osquery.readthedocs.io/en/stable...-additions
  


• Trino keywords (Datalake) - https://trino.io/docs/current/functions/list.html
  

New clauses should begin on new lines


Columns should be separated by new lines

• This is specifically for the SELECT actually returning data, however where possible please keep to this.
  

Conditions should be separated by new lines

• This applies to WHERE, CASE, and JOIN statements most significantly
  


• No more than 2 conditions per line
  


• Longer example with more points
  


• Logical operators should be at the start of a new line, for the line they apply to
  

Use indentation to clarify structure

• Nesting should be used to indicate when a CLAUSE is a member of a different CLAUSE
  

Blocks of SQL should be separated by new lines

• When UNIONing, a blank line should be between the end of the first table, the UNION statement, and the start of the second table
  


• When constructing a virtual table using WITH, and blank line should be between the end of the WITH clause and beginning of the next clause
  

Column names and variables should be snake_case


Semi-colons should not be used at the end of a query

• Semi-colons cause issues when querying the Data Lake, and are unnecessary for querying endpoints
  

Need for comments should be reduced as much as possible

• Anything important should be pushed into description/variable descriptions where they'll be localized
  


• Comments should be for explaining something that may not be immediately obvious (regex/specific IDs etc)
  

Opening brackets should be on the same line as the start of the statement

• Closing brackets should be on a new line and should be aligned to the same indentation as the opening bracket
  

Punctuation spacing should reflect generic C++ punctuation standards

• No space before a comma, 1 space after a comma
  


• Operators should be padded with 1 space on either side
  


• Brackets should be treated with the same spacing that would be used for C++
  

We should remove nested WITH's

• When needing to create multiple CTE's, they should be created under the same WITH statement
  

Scalar subqueries can be in-line, but multi-line returning queries should be part of a WITH

• This can be changed as needed for performance
  


• A scalar sub query only returns one row consisting of one column
  

General Canned Queries

These rules specifically apply to writing SQL for any form of canned queries.

Time fields should be in the following format

• YYYY-MM-DDTHH:MM:SSZ
  


• E.g. 2021-03-12T15:06:45Z
  


• When querying endpoints you can use the following to convert from epoch to the format:
  • strftime('%Y-%m-%dT%H:%M:%SZ', datetime(<timefield>,'unixepoch')
    
  
  
  
  


• When querying the datalake you can use the following to convert from epoch to the format:
  • date_format(from_unixtime(<timefield>), '%Y-%m-%dT%H:%i:%SZ')
    
  
  
  
  


• Variables should not be used for toggles
  

Wildcarding within a query is dependant on the context of the query

• We should try to limit the number of times we use wildcards to avoid unexpected matching for the user
  


• Add it where it's needed, but if there's a case where the user should have the ability to be explicit, allow them to add wildcards themselves.
  


• If the wildcarding may be confusing to a user, add details in the variables descriptions
  


• Use our best judgment.
  

When joining two tables on the equality of columns with the same name, you should use the USING keyword


When more than 1 table is being selected from, column names should be fully qualified


ALIASES should be meaningful

• Although aliases are used to shorten names, they should be meaningful and be clearly understandable without knowing context
  

Endpoint Canned Queries

These rules specifically apply to writing canned queries for endpoints.

Any column referring to the time of the event should be named "date_time"

• This should apply to the "time" column
  


• This isn't necessary if you're calling the column something more specific
  

Data Lake Canned Queries

These rules specifically apply to writing canned queries for the Data Lake

We should use LOWER for case insensitive matching

• As LIKE is not case insensitive when querying against the Data Lake, we should consistently use LOWER to perform a case insensitive match
  

Where possible, specify which query_name you're wanting to query

• This improves query speed and efficiency
  

When joining the xdr_data table on itself, use RIGHT JOIN

Scheduled queries

These rules specifically apply to writing scheduled queries

Be aware of the types of your columns

• Any column should have the same type as all other columns of the same name
  


• This applies across scheduled queries
  

[Tyr]

Fantastic thread with very detailed information. Thanks for posting this.