Make-Series SparkLine

%3CLINGO-SUB%20id%3D%22lingo-sub-1426503%22%20slang%3D%22en-US%22%3EMake-Series%20SparkLine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426503%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20add%20a%20SparkLine%20column%20to%20my%20query%20to%20replicate%20the%20Trend%20line%20that's%20seen%20in%20some%20of%20the%20Sentinel%20Workbook%20templates%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Trend%20line%20conditional%20policy.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195334i1170B1B7EA2453AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Trend%20line%20conditional%20policy.PNG%22%20alt%3D%22Trend%20line%20conditional%20policy.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20query%20I'm%20trying%20to%20add%20it%20to%20is%20simple%2C%20I'm%20looking%20at%20failed%20logons%20with%20the%20substatus%20for%20expired%20passwords%3A%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EW%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3Edows%3C%2FSPAN%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3CSPAN%3EID%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E4625%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ESubStatus%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0xC0000071%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESubStatus%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0xC0000071%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EUserInitiat%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3EgLog%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E_%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ESubjectUserName%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETargetUserName_%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ETargetUserName%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ENumberOfAttempts%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EUserInitiat%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3EgLog%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E_%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETargetUserName_%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3EThis%20works%20fine%20and%20returns%20the%20below%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Query%20Without%20MakeSeries.png%22%20style%3D%22width%3A%20967px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195335iC191728CDB4BADAA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Query%20Without%20MakeSeries.png%22%20alt%3D%22Query%20Without%20MakeSeries.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20I%20add%20a%20'make-series'%20statement%20to%20my%20query%20(TimeRange%20is%20set%20as%20a%20time%20picker%20earlier%20in%20the%20Workbook)%20as%20per%20the%20below%20I%20get%20an%20error%3A%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EW%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3Edows%3C%2FSPAN%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3CSPAN%3EID%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E4625%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ESubStatus%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0xC0000071%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESubStatus%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0xC0000071%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EUserInitiat%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3EgLog%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E_%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ESubjectUserName%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETargetUserName_%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EData%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3ETargetUserName%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ENumberOfAttempts%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EUserInitiat%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3EgLog%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E_%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETargetUserName_%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x_MsoNormal%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Emake%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Eseries%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETrend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Edefault%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTimeGenerated%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Erange%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%7BTimeRange%3Astart%7D%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%7BTimeRange%3Aend%7D%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%7BTimeRange%3Agra%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EUserInitiat%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3EgLog%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ETargetUsername%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EComputer%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Query%20WITH%20makeseries%20error.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195345iA3645D6D5A6B6BBC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Query%20WITH%20makeseries%20error.PNG%22%20alt%3D%22Query%20WITH%20makeseries%20error.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20point%20me%20in%20the%20right%20direction%3F%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1426503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430744%22%20slang%3D%22en-US%22%3ERe%3A%20Make-Series%20SparkLine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430744%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F556556%22%20target%3D%22_blank%22%3E%40Sam_SOC%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EThe%20Summarize%20clause%20is%20the%20issue%20here%20-%20it%20keeps%20only%20the%20few%20fields%20that%20are%20explicitly%20mentioned%20(NumberOfAttempts%2C%20count%2C%20UserInitiatingLogon%20etc.)%20and%20TimeGenerated%20is%20not%20one%20of%20them.%20In%20fact%2C%20the%20summarize%20operation%20doesn't%20seem%20to%20be%20needed%20at%20all%20when%20you%20create%20a%20series%2C%20which%20is%20based%20on%20the%20TimeGenerated%20field.%20Without%20it%2C%20it%20works%20well.%3C%2FP%3E%0A%3CP%3ETo%20test%2C%20I've%20created%20a%20query%20very%20similar%20to%20yours%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20StartTime%20%3D%20ago(1d)%3B%0Alet%20EndTime%20%3D%20now()%3B%0Aunion%20Event%2C%20SecurityEvent%0A%7C%20where%20EventID%20%3D%3D%204625%0A%7C%20extend%20UserInitiatingLogon%20%3D%20tostring(SubjectUserName)%0A%7C%20extend%20TargetUserName%20%3D%20tostring(TargetUserName)%0A%2F%2F%7C%20summarize%20NumberOfAttempts%20%3D%20count()%20by%20UserInitiatingLogon%2C%20TargetUserName%2C%20Computer%0A%7C%20make-series%20Trend%20%3D%20count()%20default%20%3D%200%20on%20TimeGenerated%20in%20range(StartTime%2C%20EndTime%2C%201h)%20by%20UserInitiatingLogon%2C%20TargetUserName%2C%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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:

Trend line conditional policy.PNG

The query I'm trying to add it to is simple, I'm looking at failed logons with the substatus for expired passwords:

union WindowsEventSecurityEvent

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:Query Without MakeSeries.png

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 WindowsEventSecurityEvent

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 UserInitiatingLogonTargetUsernameComputer

 

Query WITH makeseries error.PNG

 

Can anyone point me in the right direction? Thanks in advance!

1 Reply

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