KQL Date between range not working

%3CLINGO-SUB%20id%3D%22lingo-sub-2368665%22%20slang%3D%22en-US%22%3EKQL%20Date%20between%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368665%22%20slang%3D%22en-US%22%3E%3CP%3EDue%20to%20the%2010%2C000%20row%20limit%20within%20KQL%2C%20we%20are%20working%20with%20running%20scan%20for%20just%20specific%20time%20ranges.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuery%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIdentityLogonEvents%3C%2FP%3E%3CP%3E%7C%20where%20LogonType%20%3D%3D%20%22Failed%20logon%22%20and%20isnotempty(AccountName)%3C%2FP%3E%3CP%3E%7C%20project%20LogonTime%20%3D%20Timestamp%2C%20LogonType%2C%20Application%2C%20FailureReason%2C%20AccountName%2C%20AccountUpn%2C%20DeviceName%2C%20DestinationDeviceName%3C%2FP%3E%3CP%3E%7C%20where%20Timestamp%20between%20(datetime(2021-5-02)..datetime(2021-5-03))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20the%20datetime%20is%20not%20working%20correctly%2C%20we%20still%20get%20what%20ever%20the%20option%20is%20selected%20in%20the%20gui.%26nbsp%3B%20When%20I%20test%20this%20in%20the%20lademo%20area%2C%20I%20get%20%22Set%20in%20query%22.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%2Fadvice%20on%20how%20to%20get%20the%20date%20range%20to%20work%20in%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20it's%20odd%20how%20the%2010%2C000%20limit%20is%20not%20in%20a%20row.%26nbsp%3B%20For%20example%2C%20if%20we%20did%205%2F5%20-%205%2F8%20and%20we%20limited%20out%2C%20we%20will%20get%20results%20for%20all%20dates%20but%20not%20all%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2368665%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekql%20query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391336%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Date%20between%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391336%22%20slang%3D%22en-US%22%3EI%20have%20not%20tested%20your%20KQL%20but%20have%20you%20tried%20moving%20your%20Timestap%20to%20the%20top%20of%20you%20query%3F%3CBR%20%2F%3ESo%20your%20data%20set%20is%20first%20created%20within%20the%20wanted%20time%20range%20and%20narrowed%20down%20based%20on%20the%20logonType.%3CBR%20%2F%3E%3CBR%20%2F%3EIdentityLogonEvents%3CBR%20%2F%3E%7C%20where%20Timestamp%20between%20(datetime(2021-5-02)..datetime(2021-5-03))%3CBR%20%2F%3E%7C%20where%20LogonType%20%3D%3D%20%22Failed%20logon%22%20and%20isnotempty(AccountName)%3CBR%20%2F%3E%7C%20project%20LogonTime%20%3D%20Timestamp%2C%20LogonType%2C%20Application%2C%20FailureReason%2C%20AccountName%2C%20AccountUpn%2C%20DeviceName%2C%20DestinationDeviceName%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2423906%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Date%20between%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2423906%22%20slang%3D%22en-US%22%3EHey%20Louis%2C%20Looks%20like%20they%20are%20going%20to%20decide%20to%20run%20the%20query%20everyday%20until%20we%20can%20get%20PowerBI%20desktop%20setup%20on%20the%20VM.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%2C%3C%2FLINGO-BODY%3E
Occasional Contributor

Due to the 10,000 row limit within KQL, we are working with running scan for just specific time ranges.  

 

Query: 

IdentityLogonEvents

| where LogonType == "Failed logon" and isnotempty(AccountName)

| project LogonTime = Timestamp, LogonType, Application, FailureReason, AccountName, AccountUpn, DeviceName, DestinationDeviceName

| where Timestamp between (datetime(2021-5-02)..datetime(2021-5-03))

 

However the datetime is not working correctly, we still get what ever the option is selected in the gui.  When I test this in the lademo area, I get "Set in query".  

 

Any help/advice on how to get the date range to work in query?

 

Also, it's odd how the 10,000 limit is not in a row.  For example, if we did 5/5 - 5/8 and we limited out, we will get results for all dates but not all the data.

 

Cheers,

2 Replies
I have not tested your KQL but have you tried moving your Timestap to the top of you query?
So your data set is first created within the wanted time range and narrowed down based on the logonType.

IdentityLogonEvents
| where Timestamp between (datetime(2021-5-02)..datetime(2021-5-03))
| where LogonType == "Failed logon" and isnotempty(AccountName)
| project LogonTime = Timestamp, LogonType, Application, FailureReason, AccountName, AccountUpn, DeviceName, DestinationDeviceName


Hey Louis, Looks like they are going to decide to run the query everyday until we can get PowerBI desktop setup on the VM.

Cheers,