SOLVED

Filtering log by date

%3CLINGO-SUB%20id%3D%22lingo-sub-146288%22%20slang%3D%22en-US%22%3EFiltering%20log%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-146288%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20can't%20seem%20to%20find%20the%20right%20syntax%20for%20this%20query%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%3EProtectionStatus%0A%7C%20summarize%20ThreatStatusRank%20%3D%20max(ThreatStatusRank)%20by%20Computer%2C%20Time%20%3D%20bin(todatetime(DateCollected)%2C%2010m)%0A%7C%20summarize(Time%2C%20ThreatStatusRank)%20%3D%20argmax(Time%2C%20ThreatStatusRank)%20by%20Computer%0A%7C%20where%20ThreatStatusRank%20!in%20(150%2C%20470)%0A%7C%20where%20TimeGenerated%20%20%26gt%3B%20ago(1d)%0A%7C%20project%20Computer%2C%20Rank%20%3D%20ThreatStatusRank%3C%2FPRE%3E%0A%3CDIV%3E%3CSPAN%3ENeither%20timestamp%20nor%20TimeGenerated%20seem%20to%20work.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EAny%20help%20is%20appreciated.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EThanks%2C%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EMatthew%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-146288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-146873%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20log%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-146873%22%20slang%3D%22en-US%22%3EYep%2C%20the%20time%20filter%20always%20have%20to%20come%20before%20summarize%20and%20it%20is%20recommended%20to%20come%20as%20the%20first%20condition.%3CBR%20%2F%3E%3CBR%20%2F%3EMeir%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-146297%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20log%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-146297%22%20slang%3D%22en-US%22%3E%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EHi%2C%20managed%20to%20get%20this%20working%20using%20the%20following%3A%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%3EProtectionStatus%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20summarize%20ThreatStatusRank%20%3D%20max(ThreatStatusRank)%20by%20Computer%2C%20Time%20%3D%20bin(todatetime(DateCollected)%2C%2010m)%0A%7C%20summarize(Time%2C%20ThreatStatusRank)%20%3D%20argmax(Time%2C%20ThreatStatusRank)%20by%20Computer%0A%7C%20where%20ThreatStatusRank%20!in%20(150%2C%20470)%0A%7C%20project%20Computer%2C%20Rank%20%3D%20ThreatStatusRank%3C%2FPRE%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I can't seem to find the right syntax for this query:

 
ProtectionStatus
| summarize ThreatStatusRank = max(ThreatStatusRank) by Computer, Time = bin(todatetime(DateCollected), 10m)
| summarize(Time, ThreatStatusRank) = argmax(Time, ThreatStatusRank) by Computer
| where ThreatStatusRank !in (150, 470)
| where TimeGenerated  > ago(1d)
| project Computer, Rank = ThreatStatusRank
Neither timestamp nor TimeGenerated seem to work.
Any help is appreciated.
 
Thanks,
Matthew
2 Replies
best response confirmed by Matthew Maguire (New Contributor)
Solution
Hi, managed to get this working using the following:
 
ProtectionStatus
| where TimeGenerated > ago(1d)
| summarize ThreatStatusRank = max(ThreatStatusRank) by Computer, Time = bin(todatetime(DateCollected), 10m)
| summarize(Time, ThreatStatusRank) = argmax(Time, ThreatStatusRank) by Computer
| where ThreatStatusRank !in (150, 470)
| project Computer, Rank = ThreatStatusRank
 
Yep, the time filter always have to come before summarize and it is recommended to come as the first condition.

Meir