Gotcha's when building queries

Unions and Joins

• When using isfuzzy=true in a Union, be sure to test each individual section.
  • isfuzzy=true will not show any error conditions for individual queries inside the Union
• Be sure to bring thru pertinent information when doing a Join, don't leave valuable context out.
  • Use makeset, dcount and other aggregation functions to keep the context available from columns you may otherwise drop when attempting to reduce the result set to a manageable size.
• Watch your usage of Where versus Extend.
  • If wrapped in a fuzzy union, this will not fail, so again test your individual query parts.
• When attempting to use a Join in a custom function, where column names cannot be defined after the ON, try using column_ifexists in the custom function.
  • An example of usage is here.

Summarize

• For all queries, whenever possible bring through TimeGenerated, AccountName, HostName and IP.
• When doing a summarize with counts, to bring thru time without impacting counts use:

| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), count() by

Mapping Entities

• For all queries, map to the Alert required custom entities, when an entity type is available

| extend timestamp = TimeGenerated, IPCustomEntity = DestinationIP, AccountCustomEntity = TargetUserName, HostCustomEntity = Computer

Syntax

Syntax support may change and causes failures that did not occur originally.

• When trying to access nested values, I recommend you click the ellipse (…) on the value in the result pane in Log Analytics and choose Extend to see what Log Analytics automatically builds for you
  • For example – this used to work and now sometimes does not - tostring(InitiatedBy.user.userPrincipalName)
  • This should work in all situations now - tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
  • But, as I said…best way is to use the Extend feature in the results

Let statements

When using let statements in a query ensure that the name assigned with let does not clash with a saved function in your workspace. Best practice is to ensure you use camel case for variable names defined with let to avoid clashes with build in names that conform to pascal case.