Search code examples
azure-data-explorerkql

How to Add Default Values for My Query Using Make-Series?

To whom it may concern,

I have a query where I am looking for the number of active action items past SLA, the total active action items, and the percentage of active action items 30 days past their SLA. The issue that I would like to alleviate is that if the month/year does not have an active action item, then there is no row. I would like my query to have a default value of 0 for months with active action items, but I am unable to figure out how to do this. Below is the query:

ActiveActionItems
| summarize by ActionItemId, Created_Date, Due_Date
| extend Thirty_Days_Past_Due_Date = datetime_diff('day', now(), Due_Date) > 30
| extend yy = datetime_part("Year", Created_Date)
| extend mm = datetime_part("Month", Created_Date)
| summarize ['Action Items Past SLA'] = countif(Thirty_Days_Past_Due_Date == true), Total=count() by month=bin(datetime_part("Month", Created_Date), 1), tostring(yy), mm
| extend Month = case(month==1,strcat('Jan/',yy),month==2,strcat('Feb/',yy),month==3, strcat('Mar/',yy),month==4,strcat('Apr/',yy),month==5,strcat('May/',yy),month==6,strcat('Jun/',yy),month==7, strcat('Jul/',yy),month==8,strcat('Aug/',yy),month==9,strcat('Sep/',yy),month==10,strcat('Oct/',yy),month==11,strcat('Nov/',yy),month==12,strcat('Dec/',yy),"error")
| order by yy asc, mm asc
| extend ['Percentage 30 Days Past SLA'] = ['Action Items Past SLA'] * 100 / Total
| project Month, ['Action Items Past SLA'], ['Total Active Action Items'] = Total, ['Percentage 30 Days Past SLA']

Input: ActionItemId (Integer) Created_Date (DateTime) Due_Date (DateTime)

Current Output: enter image description here Note how Dec/2018 is missing as there were no active action items for that month.

Any help here would be greatly appreciated!!

Solution

  • Figured it out, needed to switch out the summary line for these two:

    | make-series ['Action Items Past SLA'] = countif(Thirty_Days_Past_Due_Date == true), Total = count() default = 0 on Created_Date from datetime(ago(24m)) to now() step 30d
| project ['Percentage 30 Days Past SLA'] = series_divide(['Action Items Past SLA'], Total), Created_Date

    Realized it would be better to keep the original idea though, as the time-series chart wasn't as helpful, and having blank rows actually allowed us to discern information faster.