May 29 2020
05:54 AM
- last edited on
Apr 08 2022
10:27 AM
by
TechCommunityAP
May 29 2020
05:54 AM
- last edited on
Apr 08 2022
10:27 AM
by
TechCommunityAP
Hi All,
I'm trying to add a SparkLine column to my query to replicate the Trend line that's seen in some of the Sentinel Workbook templates:
The query I'm trying to add it to is simple, I'm looking at failed logons with the substatus for expired passwords:
union WindowsEvent, SecurityEvent
| where EventID == 4625
| where (Data.SubStatus) == 0xC0000071
or SubStatus == 0xC0000071
| extend UserInitiatingLogon_ = tostring(Data.SubjectUserName)
| extend TargetUserName_ = tostring(Data.TargetUserName)
| summarize NumberOfAttempts = count() by UserInitiatingLogon_, TargetUserName_, Computer
This works fine and returns the below:
When I add a 'make-series' statement to my query (TimeRange is set as a time picker earlier in the Workbook) as per the below I get an error:
union WindowsEvent, SecurityEvent
| where EventID == 4625
| where (Data.SubStatus) == 0xC0000071
or SubStatus == 0xC0000071
| extend UserInitiatingLogon_ = tostring(Data.SubjectUserName)
| extend TargetUserName_ = tostring(Data.TargetUserName)
| summarize NumberOfAttempts = count() by UserInitiatingLogon_, TargetUserName_, Computer
| make-series Trend = count() default = 0 on TimeGenerated in range({TimeRange:start}, {TimeRange:end}, {TimeRange:grain}) by UserInitiatingLogon, TargetUsername, Computer
Can anyone point me in the right direction? Thanks in advance!
Jun 01 2020 04:46 AM
Hi @Sam_SOC ,
The Summarize clause is the issue here - it keeps only the few fields that are explicitly mentioned (NumberOfAttempts, count, UserInitiatingLogon etc.) and TimeGenerated is not one of them. In fact, the summarize operation doesn't seem to be needed at all when you create a series, which is based on the TimeGenerated field. Without it, it works well.
To test, I've created a query very similar to yours:
let StartTime = ago(1d);
let EndTime = now();
union Event, SecurityEvent
| where EventID == 4625
| extend UserInitiatingLogon = tostring(SubjectUserName)
| extend TargetUserName = tostring(TargetUserName)
//| summarize NumberOfAttempts = count() by UserInitiatingLogon, TargetUserName, Computer
| make-series Trend = count() default = 0 on TimeGenerated in range(StartTime, EndTime, 1h) by UserInitiatingLogon, TargetUserName, Computer