Question 267062 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – KQL Split an aggregrator Column if size exceeds threshold

Given Kusto query

Table
 | where type == "TYPE"
 | summarize lastEventTime = arg_max(lastEvent, *), vmCount = count(),  onboardedCount = countif(onboarded), 
 failVM = make_list_if(entity, onboarded == false and isnotempty(entity)), allVM = make_set(entity) by  environmentType, osType, id , type

Now I want to split allVM count into 2 list first one containing first 100 elements and second one containing rest .

allVM1 = [... first 100 elements... ] (0-99)
allVM2 = [.. remaining ... ] (100- size of allVm)

if there are less than 100 elements than allVM2 should be empty.

How can i achieve the same ?

Assume onboarded contains value true / false and entity is a string

Tags:

2

Answers


  1. 0

    You just use extend after summarize and create a substring of the allVM field, I think I have your KQL here:

    Table
| where type == "TYPE"
| summarize
    lastEventTime = arg_max(lastEvent, *),
    vmCount = count(),
    onboardedCount = countif(onboarded), 
    failVM = make_list_if(entity, onboarded == false and isnotempty(entity)),
    allVM = make_set(entity)
    by environmentType, osType, id, type
| extend allVM1 = substring(allVM, 0, 100)
| extend allVM2 = substring(allVM, 100, 9999)
    Login or Signup to reply.
  2. 0

    array_slice could be just the thing you’re looking for.

    I’ve edited your example code a little, hopefully though it’s transferable enough to your use case. I’ve removed lastEventTime, and id.

    let T = materialize(range Position from 1 to 500 step 1 //Generate a random-ish entity table
| extend entity = rand(1000)
| where  entity < 500 //Generate random number of results to output
| extend type = 'TYPE'
| extend onboarded = iif(rand(1000) < 500, true, false)
| extend osType = case(
  rand(1000) < 500, 'Windows 11',
  rand(1000) < 500, 'Windows 10',
  rand(1000) < 500, 'Server 2019',
  rand(1000) < 500, 'Server 2022',
  'Microsoft Bob'
  )
);
T
| where type == 'TYPE'
| summarize vmCount = count(), onboardedCount = countif(onboarded), 
  failVM = make_list_if(entity, onboarded == false and isnotempty(entity)),
  allVM = make_set(entity)
  by osType, type
| extend allVM1 = array_slice(allVM, 0, 99) //Take the beginning (0) and slide to 100 (99)
| extend allVM2 = array_slice(allVM, 100, -1) //Take from 101 to the end
    osType type vmCount onboardedCount failVM allVM allVM1 allVM2
    Windows 11 TYPE 127 54 [182.0,63.0,326.0,364.0,131.0,469.0,19.0,351.0,434.0,39.0,414.0,361.0,130.0,199.0,155.0,60.0,34.0,256.0,75.0,419.0,344.0,277.0,294.0,61.0,179.0,296.0,267.0,411.0,324.0,196.0,177.0,3.0,140.0,453.0,369.0,353.0,141.0,282.0,353.0,395.0,422.0,398.0,49.0,37.0,47.0,477.0,137.0,305.0,190.0,492.0,204.0,189.0,190.0,447.0,491.0,169.0,281.0,328.0,431.0,87.0,172.0,78.0,266.0,363.0,303.0,195.0,153.0,115.0,351.0,130.0,132.0,167.0,477.0] [232.0,182.0,297.0,269.0,63.0,180.0,326.0,289.0,486.0,375.0,364.0,131.0,469.0,448.0,27.0,19.0,4.0,480.0,452.0,351.0,434.0,39.0,414.0,82.0,361.0,440.0,130.0,199.0,155.0,60.0,312.0,34.0,223.0,422.0,336.0,112.0,69.0,256.0,75.0,419.0,344.0,277.0,294.0,61.0,204.0,179.0,296.0,267.0,411.0,324.0,24.0,335.0,237.0,196.0,238.0,177.0,3.0,140.0,32.0,118.0,363.0,453.0,273.0,369.0,415.0,467.0,353.0,141.0,328.0,282.0,28.0,395.0,287.0,398.0,49.0,37.0,47.0,477.0,322.0,137.0,251.0,305.0,383.0,59.0,104.0,190.0,492.0,218.0,189.0,99.0,447.0,491.0,169.0,281.0,431.0,445.0,87.0,21.0,172.0,58.0,78.0,266.0,303.0,195.0,153.0,181.0,115.0,132.0,62.0,167.0,466.0,193.0] [232.0,182.0,297.0,269.0,63.0,180.0,326.0,289.0,486.0,375.0,364.0,131.0,469.0,448.0,27.0,19.0,4.0,480.0,452.0,351.0,434.0,39.0,414.0,82.0,361.0,440.0,130.0,199.0,155.0,60.0,312.0,34.0,223.0,422.0,336.0,112.0,69.0,256.0,75.0,419.0,344.0,277.0,294.0,61.0,204.0,179.0,296.0,267.0,411.0,324.0,24.0,335.0,237.0,196.0,238.0,177.0,3.0,140.0,32.0,118.0,363.0,453.0,273.0,369.0,415.0,467.0,353.0,141.0,328.0,282.0,28.0,395.0,287.0,398.0,49.0,37.0,47.0,477.0,322.0,137.0,251.0,305.0,383.0,59.0,104.0,190.0,492.0,218.0,189.0,99.0,447.0,491.0,169.0,281.0,431.0,445.0,87.0,21.0,172.0,58.0] [78.0,266.0,303.0,195.0,153.0,181.0,115.0,132.0,62.0,167.0,466.0,193.0]
    Server 2022 TYPE 14 6 [444.0,14.0,48.0,204.0,28.0,188.0,201.0,366.0] [444.0,14.0,48.0,403.0,465.0,135.0,204.0,28.0,188.0,201.0,402.0,456.0,367.0,366.0] [444.0,14.0,48.0,403.0,465.0,135.0,204.0,28.0,188.0,201.0,402.0,456.0,367.0,366.0] []
    Server 2019 TYPE 39 22 [179.0,442.0,161.0,197.0,390.0,287.0,106.0,19.0,255.0,369.0,96.0,250.0,165.0,7.0,298.0,243.0,312.0] [111.0,179.0,202.0,442.0,18.0,287.0,161.0,197.0,273.0,307.0,390.0,295.0,106.0,19.0,486.0,136.0,154.0,99.0,255.0,342.0,353.0,497.0,369.0,66.0,452.0,96.0,250.0,165.0,122.0,262.0,232.0,7.0,298.0,124.0,243.0,185.0,312.0,294.0] [111.0,179.0,202.0,442.0,18.0,287.0,161.0,197.0,273.0,307.0,390.0,295.0,106.0,19.0,486.0,136.0,154.0,99.0,255.0,342.0,353.0,497.0,369.0,66.0,452.0,96.0,250.0,165.0,122.0,262.0,232.0,7.0,298.0,124.0,243.0,185.0,312.0,294.0] []
    Windows 10 TYPE 64 36 [165.0,377.0,99.0,199.0,325.0,281.0,276.0,473.0,169.0,272.0,339.0,469.0,13.0,24.0,240.0,200.0,237.0,106.0,282.0,466.0,252.0,196.0,55.0,237.0,406.0,84.0,85.0,46.0] [165.0,377.0,15.0,48.0,275.0,100.0,99.0,380.0,199.0,91.0,117.0,55.0,325.0,348.0,57.0,281.0,211.0,439.0,276.0,383.0,473.0,169.0,272.0,221.0,339.0,58.0,469.0,131.0,13.0,5.0,24.0,486.0,240.0,185.0,200.0,237.0,110.0,409.0,148.0,433.0,106.0,282.0,85.0,466.0,252.0,224.0,328.0,196.0,400.0,406.0,84.0,233.0,300.0,443.0,499.0,352.0,46.0,17.0,331.0,481.0,102.0] [165.0,377.0,15.0,48.0,275.0,100.0,99.0,380.0,199.0,91.0,117.0,55.0,325.0,348.0,57.0,281.0,211.0,439.0,276.0,383.0,473.0,169.0,272.0,221.0,339.0,58.0,469.0,131.0,13.0,5.0,24.0,486.0,240.0,185.0,200.0,237.0,110.0,409.0,148.0,433.0,106.0,282.0,85.0,466.0,252.0,224.0,328.0,196.0,400.0,406.0,84.0,233.0,300.0,443.0,499.0,352.0,46.0,17.0,331.0,481.0,102.0] []
    Microsoft Bob TYPE 15 5 [253.0,403.0,131.0,163.0,479.0,304.0,437.0,446.0,362.0,465.0] [130.0,318.0,253.0,403.0,131.0,163.0,479.0,304.0,437.0,446.0,373.0,319.0,362.0,465.0,22.0] [130.0,318.0,253.0,403.0,131.0,163.0,479.0,304.0,437.0,446.0,373.0,319.0,362.0,465.0,22.0] []
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search