CODEWITHSUNDEEP
azure-data-explorer
Min Mun
Min Mun

Reputation: 81

How to get distinct count for each table by kusto (Azure Data Exploer)

I have lots of table with different name but with the same schema in a database such like:

let _t1 = datatable(Guid:string, name:string)
[
'a00001','n1',
'a00001', 'n2',
'a00001', 'n1',
'a00002', 'n3',
'a00002', 'n2',
'a00003', 'n1',
];
let _t2 = datatable(Guid:string, Name:string)
[
'a00011','n2',
'a00023', 'n2',
'a00032', 'n1',
'a00045', 'n3',
'a00032', 'n7',
'a00083', 'n5',
];

...

I only know how to get the count distinct Guid with specified table name.

_t1
| summarize Count=count() by Guid;
| count;
_t2
| summarize Count=count() by Guid;
| count;
...

How can I get the distinct count of Guid for each table by one kusto command such like:

table_name| Guid_count
-------------------
t1|3
t2|6
...

Upvotes: 0

Views: 2201

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could use the union operator, while specifying the withsource option.

for example:

let _t1 = view() {datatable(Guid:string, name:string)
[
'a00001','n1',
'a00001', 'n2',
'a00001', 'n1',
'a00002', 'n3',
'a00002', 'n2',
'a00003', 'n1',
]}
;
let _t2 = view() { datatable(Guid:string, Name:string)
[
'a00011','n2',
'a00023', 'n2',
'a00032', 'n1',
'a00045', 'n3',
'a00032', 'n7',
'a00083', 'n5',
]}
;
union withsource=source _t*
| summarize dcount(Guid) by source

-->

| source | dcount_Guid |
|--------|-------------|
| _t2    | 5           |
| _t1    | 3           |

Upvotes: 2

Related Questions