How to keep null results from Stats Count by field to match the whole table

[angelcat]

I have 4 types of devices, a column for total number, and I need to count by type. But some of the result are null, then it will skip the types with null values. How can I keep the null value to make the results match the types? Below is the expected result:

Type Total Count
A 10 null
B 20 null
C 30 5
D 40 6

I can only get the result like this:

Type Total Count
A 10 5
B 20 6
C 30

D 40

I have tried fillnull before and after count , but it does not work.

[somesoni2 · Accepted solution]

Try like this

index="scan" 
|stats count as Total by Type 
| append [search index=vm |join type=outer ip [search
index="scan" |fields Type ip]
|stats count by Type]
| stats values(Total) as Total values(count) as count by Type

[richgalloway]

Please share your query.

[angelcat]

index="scan"
|stats count as Total by Type
|appendcols [search index=vm |join type=outer ip [search
index="scan" |fields Type ip]
|stats count by Type

]
|table Type Total count