Count rows until you get to the current owning team value... Kusto, countof()

0
votes

I have this Kusto code that I have been trying to develop and any help would be greatly appreciated.

The objective is to count to the first occurrence of the CurrentOwningTeamId in the OwningTeamId column.

I packed the Owning Team number and parsed the value into a column of its own. I need to count the owning teams until I get to the current owning team. Columns are (example):

Objective: Count to the first occurrence of the CurrentOwningTeam value in the OwningTeamId column using Kusto (Application Insights code):

[CODE]

   OwningTeamId,   CurrenOwningTeam,    CreateDate,   RequestType
       155523          **888888**        2017-07-02    PRIMARY
       256924          **888888**        2017-08-02    TRANSFER
     **888888**        **888888**        2017-09-02    TRANSFER
       954005          **888888**        2017-10-02    TRANSFER
     **888888**        **888888**        2017-11-02    TRANSFER
       155523          **888888**        2017-12-02    TRANSFER
       954005          **888888**        2017-13-02    TRANSFER
     **888888**        **888888**        2017-14-02    TRANSFER

[/CODE]

I think you can match the current owning team with the countof() function, but I don't know how to go about it using regex. Note: values are different with each owning team on every incident, is why I capture the owning team on the incident first and try to count the very first instance of the CurrentOwningTeam number in the OwningTeamId column. In other words I want to count the number of times it takes to get to the very first owning team. In this case, it would be three.

Note: OwningTeamId's and CurrentOwningTeam can change on every incident, I first capture the CurrentOwningTeam then try to match in the OwningTeamId column.

Note: This is just one incident, but I am trying to do multiple Incidents. Below is how I got the Current Owning Team Value. [/CODE]

  | extend CurrentOwningTeam=pack_array(OwningTeamId)
  | parse CurrentOwningTeam with * "[" CurrentOwningTeam:int "]" *
  | serialize CurrentOwningTeam

[/CODE]

I tried using row_number() but it will not work for multiple incidents, only per incident, so I have to use count or countof functions or another way of doing it.

2
countazure-data-explorer
Tagged kusto - smci
Did find another way... you can summarize, RowNumber=sum(1) or RowNUmber =count(). - Chris Singleton

2 Answers

1
votes

Thanks for clarification. Here is a suggestion for a query that counts ordered by-time rows until certain condition is reached (count is contextual using IncidentId key).

datatable(IncidentId:string, OwningTeamId:string, CurrentOwningTeam:string, CreateDate:datetime, RequestType:string)
[
'Id1','155523','888888',datetime(2017-02-07),'PRIMARY',
'Id1','256924','888888',datetime(2017-02-08),'TRANSFER',
'Id1','888888','888888',datetime(2017-02-09),'TRANSFER',
'Id1','954005','888888',datetime(2017-02-10),'TRANSFER',
'Id1','888888','888888',datetime(2017-02-11),'TRANSFER',
'Id1','155523','888888',datetime(2017-02-12),'TRANSFER',
'Id1','954005','888888',datetime(2017-02-13),'TRANSFER',
'Id1','888888','888888',datetime(2017-02-14),'TRANSFER',
// Id2
'Id2','155523','888888',datetime(2017-02-07),'PRIMARY',
'Id2','256924','888888',datetime(2017-02-08),'TRANSFER',
'Id2','999999','888888',datetime(2017-02-09),'TRANSFER',
'Id2','954005','888888',datetime(2017-02-10),'TRANSFER',
'Id2','888888','888888',datetime(2017-02-11),'TRANSFER',
'Id2','155523','888888',datetime(2017-02-12),'TRANSFER',
'Id2','954005','888888',datetime(2017-02-13),'TRANSFER',
'Id2','888888','888888',datetime(2017-02-14),'TRANSFER',
]
| order by IncidentId, CreateDate asc
| extend c= row_cumsum(1, IncidentId!=prev(IncidentId))
| where OwningTeamId == CurrentOwningTeam 
| summarize arg_min(CreateDate, c) by IncidentId

Result:

IncidentId  CreateDate  c
Id1  2017-02-09 00:00:00.0000000  3
Id2  2017-02-11 00:00:00.0000000  5

Here are the links to the docs that point how to find earliest record using arg_min() aggregation, and link to the row_cumsum() (cumulative sum) function.

https://docs.microsoft.com/en-us/azure/kusto/query/arg-min-aggfunction https://docs.microsoft.com/en-us/azure/kusto/query/rowcumsumfunction

0
votes

I figured it out by using the RowNumber directly into grouping inside the table, then finally summing to get my total count. 

      [CODE]

      | serialize Id  
      | extend RowNumber=row_number(1, (Id) ==Id)  
      | summarize TotalOwningTeamChanges=sum(RowNumber) by Id  

      [/CODE]

Then after that I got the Minimum Date to extract the entire data set to the first instance of the current OwningTeamName. 

      [CODE]
      //Outside the scope of the table.
      | extend ExtractFirstOwningTeamCreateDate=CreateDate2  
      | extend VeryFirstOwningTeamCreateDate=MinimumCreateDate  
      | where FirstOwningTeamRow == true or MinimumCreateDate <=
              ExtractFirstOwningTeamCreateDate  
      | serialize VeryFirstOwningTeamCreateDate  

      [/CODE]