Why won't Sentinel Analytics Rule Wizard validate a valid query?

Question

I have a query that I am trying to implement as a Sentinel Analytics Rule. In Log Explorer, the query works fine and returns expected results but in the Analytics Rule Wizard, it will not validate the query because I am dynamically generating columns that don't natively exist. My issue seems similar to the issues that were fixed by this PR.

The query is to detect resource deployments in regions that have need been deployed to in the past 14 days. The error I get in the Analytics Rule Wizard is 'where' operator: Failed to resolve column or scalar expression named 'unpack_requestbody'. But no errors in Log Explorer.

I based this query off of a template query for "Rare User Actions".

let starttime = 14d;
let endtime = 1d;
let RareRegion = AzureActivity
    | where TimeGenerated between (ago(starttime) .. ago(endtime))
    | evaluate bag_unpack(Properties_d, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
    | where unpack_requestbody != ''
    | extend request_body = parse_json(unpack_requestbody)
    | evaluate bag_unpack(request_body, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
    | where unpack_location != ''
    | summarize count() by CallerIpAddress, Caller, OperationNameValue, unpack_location, bin(TimeGenerated, 1d)
    // Returns all the records from the right side that don't have matches from the left.
    | join kind=rightantisemi (
        AzureActivity
        | where TimeGenerated > ago(endtime)
        | evaluate bag_unpack(Properties_d, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
        | where unpack_requestbody != ''
        | extend request_body = parse_json(unpack_requestbody)
        | evaluate bag_unpack(request_body, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
        | where unpack_location != ''
        | summarize
            StartTimeUtc = min(TimeGenerated),
            EndTimeUtc = max(TimeGenerated),
            ActivityTimeStamp = make_set(TimeGenerated, 100),
            ActivityStatusValue = make_set(ActivityStatusValue, 100),
            CorrelationIds = make_set(CorrelationId, 100),
            ResourceGroups = make_set(ResourceGroup, 100),
            ResourceIds = make_set(_ResourceId, 100),
            ActivityCountByCallerIPAddress = count()
            by CallerIpAddress, Caller, OperationNameValue, unpack_location)
        on CallerIpAddress, Caller, OperationNameValue, unpack_location;
RareRegion
| extend Name = iif(Caller has '@', tostring(split(Caller, '@', 0)[0]), "")
| extend UPNSuffix = iif(Caller has '@', tostring(split(Caller, '@', 1)[0]), "")
| extend AadUserId = iif(Caller !has '@', Caller, "")

Answer 1

I think you can create "fake" entries for the columns, like this?

let starttime = 14d;
let endtime = 1d;
let RareRegion = AzureActivity
    | where TimeGenerated between (ago(starttime) .. ago(endtime))
    | evaluate bag_unpack(Properties_d, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
    | extend unpack_requestbody = column_ifexists('unpack_requestbody','fake')
    | where isnotempty(unpack_requestbody)
    | extend request_body = parse_json(unpack_requestbody)
    | evaluate bag_unpack(request_body, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
    | extend unpack_location = column_ifexists('unpack_location','fake')
    | where isnotempty(unpack_location)
    | summarize count() by CallerIpAddress, Caller, OperationNameValue, unpack_location, bin(TimeGenerated, 1d)
    // Returns all the records from the right side that don't have matches from the left.
    | join kind=rightantisemi (
        AzureActivity
        | where TimeGenerated > ago(endtime)
        | evaluate bag_unpack(Properties_d, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
        | extend unpack_requestbody = column_ifexists('unpack_requestbody','fake')
        | where isnotempty(unpack_requestbody)
        | extend request_body = parse_json(unpack_requestbody)
        | evaluate bag_unpack(request_body, OutputColumnPrefix='unpack_', columnsConflict='replace_source')
         | extend unpack_location = column_ifexists('unpack_location','fake')
        | where isnotempty(unpack_location)
        | summarize
            StartTimeUtc = min(TimeGenerated),
            EndTimeUtc = max(TimeGenerated),
            ActivityTimeStamp = make_set(TimeGenerated, 100),
            ActivityStatusValue = make_set(ActivityStatusValue, 100),
            CorrelationIds = make_set(CorrelationId, 100),
            ResourceGroups = make_set(ResourceGroup, 100),
            ResourceIds = make_set(_ResourceId, 100),
            ActivityCountByCallerIPAddress = count()
            by CallerIpAddress, Caller, OperationNameValue, unpack_location)
        on CallerIpAddress, Caller, OperationNameValue, unpack_location;
RareRegion
| extend Name = iif(Caller has '@', tostring(split(Caller, '@', 0)[0]), "")
| extend UPNSuffix = iif(Caller has '@', tostring(split(Caller, '@', 1)[0]), "")
| extend AadUserId = iif(Caller !has '@', Caller, "")

Answer 2

Hi Trevor,

I suspect you've defined a function for "unpack_requestbody" which wouldn't be available to the kql in Sentinel.

You may need to set up a 'let' to replicate that function within your kql.

Answer 3

I had to add a line like this after each bag_unpack line so that the column exists and is either empty or contains the unpacked value, just as @Clive Watson mentioned.

extend unpack_location= column_ifexists("unpack_location", '')