Summarize with dynamic variable involved

Occasional Visitor

Hi All,

 

I have some data stored int he following format:

KeyTimestampDynField
12342019-07-24 23:59:33{"isA":false,"isRR":false,"isE":true,"isM":null,"isVS":0,"isBot":false}
39382019-07-24 12:04:32{"isA":true,"isRR":false,"isE":false,"isM":null,"isVS":1000,"isBot":false}
39822019-07-24 01:33:13{"isA":false,"isRR":true,"isE":false,"isM":null,"isVS":0,"isBot":true}

 

The combinations in the DynField can be true/false/null (except for isVS that is 0 or 1000).

 

My goal is to have the following output:

Datecount keycount key where isA = truecount key where isRR = true
2019-07-24311

 and so on... with a column for each of the variables with the dynamic field.

 

Can anyone help on how to best use Dynamic fields when summarizing?

 

Thanks,

g

1 Reply

consider the following option:

 

datatable(key:long, Timestamp:datetime, d:dynamic)
[
    1234, datetime(2019-07-24 23:59:33), dynamic({"isA":false,"isRR":false,"isE":true, "isM":null,"isVS":0,   "isBot":false}),
    3938, datetime(2019-07-24 12:04:32), dynamic({"isA":true, "isRR":false,"isE":false,"isM":null,"isVS":1000,"isBot":false}),
    3982, datetime(2019-07-24 01:33:13), dynamic({"isA":false,"isRR":true, "isE":false,"isM":null,"isVS":0,   "isBot":true })
]
| summarize count_key = dcount(key), 
            count_key_where_isA_is_true = dcountif(key, d.isA == true),
            count_key_where_isRR_is_true = dcountif(key, d.isRR == true)
         by Date = startofday(Timestamp)