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