Why is the right Log Analytics SQL to get these fields that are not under audit logs?

by

Summary

The issue at hand is the inability to access certain fields, such as protoPayload.serviceName, in Log Analytics using SQL queries, while these fields are accessible in the Log Explorer. This discrepancy leads to questions about the limitations of Log Analytics and potential errors in SQL syntax.

Root Cause

The root cause of this issue lies in the way Log Analytics and Log Explorer handle log data. Key points to consider:

  • Log Analytics is designed to work with structured log data, which may not include all fields available in Log Explorer.
  • Audit logs are a specific type of log data that contains detailed information about actions taken on resources, and Log Analytics may have limited access to non-audit log fields.
  • The protoPayload field is a part of the log data, but its accessibility varies between Log Explorer and Log Analytics.

Why This Happens in Real Systems

This issue occurs in real systems due to the following reasons:

  • Data filtering: Log Analytics might filter out certain log data, including non-audit logs, which contain fields like protoPayload.serviceName.
  • Data structuring: The way log data is structured and indexed in Log Analytics can affect the availability of certain fields in SQL queries.
  • Access control: Restrictions on access to certain log data, such as non-audit logs, can limit the fields that can be queried in Log Analytics.

Real-World Impact

The impact of this issue includes:

  • Limited visibility: Inability to access certain log fields in Log Analytics can limit the visibility into system activities and events.
  • Inaccurate analysis: Relying on audit logs alone may lead to inaccurate analysis, as they may not contain all relevant information.
  • Increased complexity: The need to use Log Explorer for certain queries can add complexity to the logging and analysis process.

Example or Code (if necessary and relevant)

SELECT 
  proto_payload.audit_log.service_name 
FROM 
  logs

This example shows how to access the service_name field via the audit_log path in Log Analytics.

How Senior Engineers Fix It

Senior engineers address this issue by:

  • Understanding log data structure: Familiarizing themselves with the structure and indexing of log data in Log Analytics.
  • Using correct syntax: Ensuring that the SQL syntax used is correct and takes into account the specific log data being queried.
  • Leveraging Log Explorer: Using Log Explorer to access log fields that are not available in Log Analytics.

Why Juniors Miss It

Junior engineers may miss this issue due to:

  • Lack of understanding: Limited knowledge of log data structure and indexing in Log Analytics.
  • Insufficient experience: Inadequate experience with Log Analytics and Log Explorer, leading to unfamiliarity with their respective capabilities and limitations.
  • Overreliance on documentation: Relying too heavily on documentation, which may not always be up-to-date or comprehensive, rather than exploring and experimenting with different approaches.