Analyze Query Results Using KQL

By Ashwin Venugopal -

 



Use the Summarize Operator

The count operator can create a new column with the help of its variations and with the calculated result for the specified fields. The By clause, makes the result set in three columns- Process, Computer, Count.
The arg_max() and arg_min() functions can filter out top and bottom rows respectively while the make_functions return a dynamic (JSON) array based on the specific function's purpose.

Use the Render operator to create visualizations

The render operator generates a visualization of the query results. The supported visualizations are:
  • areachart
  • bacchant
  • columnchart
  • piechart
  • scatterchart
  • timechart 

Build Multi-Table Statements Using KQL

Use the Union operator

The union operators take two or more tables and returns the rows of all of them and it is essential to understand how results are passed as well as impacted with the pipe character. 

Use Join operator

The join operator merges the rows of two tables to form a new table by matching the specified columns' values from each table. While joining tables, you can use flavors to determine the joining behavior. The graphic below shows which records will be kept if there is or isn't a matching record in the other dataset. The inner join will only show records from the left side if there is a matching record on the right side which also requires a left side record.

Join Flavor

Output Records

Kind=leftanti, kind=leftantisemi

Returns all the records from the left side that don’t have matches from the right.

Kind=rightant, kind=rightantisemi

Returns all the records from the right side that don’t have matches from the left.

Kind-unspecified, kind=innerunique

Only one row from the left side is matched for each value of the on key. The output contains a row for each match of this row which rows from the right.

Kind=leftsemi

Returns all the records from the left side that have matches from the right.

Kind=rightsemi

Returns all the records from the right side that have matches from the left.

Kind=inner

Contains a row in the output for every combination of matching rows from left and right.

Kind=leftouter (or kind=rightouter or kind=fullouter)

Contains a row for every row on the left and right, even if it has no match. The unmatched output cells contain nulls.

 









Comments

Post a Comment

Popular posts from this blog

Deployment (Part 3)

By Ashwin Venugopal -
Image
  To read part 1, please click  here To read part 2, please click  here Microsoft Monitoring Agent (MMA) MMA is used across multiple Microsoft solutions and has undergone a few name changes as its features and functionality have evolved. If it is being considered to be used, then following areas should be covered during a Microsoft Sentinel deployment project: Consider the timelines and the strategy for a migration to AMA (Azure Monitor Agent) before the MMA end-of-life.  Consider the central deployment and management methodology for MMA. Determine which endpoints are in scope for deployment as these affect the log ingestion volume significantly.  Azure Monitor Agent (AMA) Organizations with MMA as the main Microsoft Sentinel agent should start planning the migration to AMA as soon as possible to avoid a rushed migration. One of the main advantages of AMA is the ability to control the log collection policy centrally and apply different policies against different...
Read more

Deployment (Part 1)

By Ashwin Venugopal -
Image
  To read part 2, please click  here To read part 3, please click  here Azure Resources Microsoft Sentinel needs following resources to be created: Subscription (if a dedicated subscription(s) will be used) Resource group(s) Log Analytics workspace(s) Automation rules/playbook Alert rules Workbooks Microsoft Sentinel offers hundreds of alert rules, workbooks, and automation playbook templates along with hunting scripts. The templates can be used to activate/deploy schedule alerts, create customized dashboards, create automation playbooks and perform threat-hunting activities. Generally, once deployed, the resources created have to be adjusted to match the existing environment, configure local credentials, etc. Methods of deployment: Manual- Administrator can manually configures the Microsoft Sentinel resources with the help of Azure portal. Any manual process has the inherent risks of human operator error, lack of compliance with potential change control procedures, and u...
Read more

Project Resourcing (Part 2)

By Ashwin Venugopal -
Image
  To read part 1, please click  here Engineering - SIEM The SIEM engineers configures Microsoft Sentinel, including Log Analytics, Logic Apps, workbooks, and playbooks. They are also responsible for the following high-level tasks: Initial configuration of Azure tenant, including provisioning required resources, assigning access roles, and configuring workspace parameters like log retention, resource tagging, and blueprints.  Deployment and configuration of syslog/CEF log collection agents in appropriate locations to collect logs from on-premises devices. Generally, it will be joint effort of both system engineer and SIEM engineer, involving configuration and potential troubleshooting.  Working with system owners to enable log forwarding and configuring any required parsing of log data in Log Analytics.  Working with security operations to create and deploy KQL analytic rules to offer detections for SOC/Computer Security Incident Response Team (CSRIT) use. Tuning...
Read more