Why is my stats count by command missing number of result?

[phamxuantung]

Hello,

I have a csv file that have 209,946 rows of event as show

After some query to apply some condition, as

|inputlookup VCCS_VIB.csv
|eval TIME = strptime(Time,"%H:%M %d/%m/%Y")
|where TIME>=1656090000 AND TIME<=1659286800
|stats count by TYPE NAME CMND CARDNUM

The meaning is I want to find events that between 25/6 and 31/7 and filter out duplicate row that match NAME, CMND and CARDNUM.

The query above show 207,460 events (note that all events are between the time constrain), when I order the count column, it show

So there are only two duplicate row -> the final number of row should have been 209,946 - 2 = 209,944, not 207,460. There are over two thousand events missing somewhere.

Could anyone show me?

[ITWhisperer · Accepted solution]

What do you get if you try this?

|inputlookup VCCS_VIB.csv
|eval TIME = strptime(Time,"%H:%M %d/%m/%Y")
|where TIME>=1656090000 AND TIME<=1659286800
|fillnull value="NA"
|stats count by TYPE NAME CMND CARDNUM

[phamxuantung]

Ah yes, I forgot stats count doesn't count null value.

Thank you.