Pfthb

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.

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

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]