kusto query to show the third column after using distinct for two other columns

0
votes

Hi I m trying to display ingestedtime in my below Kusto query, can you pls provide suggestion 

find withsource=source in (cluster(X).database('y*').['TextFileLogs'])
where AttemptedIngestTime > ago(7d)
and FileLineContent contains "<li>Build Number:" 
| distinct source , FileLineContent //, AttemptedIngestTime
| extend databaseName = extract(@"""(oci-[^""]*)""", 1, source)
| extend BuildNumber = extract(@"([A-Z]\w*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
| extend StampVersion = extract(@"([0-9]\d*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
| extend cluster = X
//| extend IngestedTime = AttemptedIngestTime
|  summarize NumberOfRuns=count() by BuildNumber , StampVersion
1
azure-data-explorerkql

1 Answers

2
votes

you could replace distinct source, FileLineContent with summarize min(AttemptedIngestTime) by source, FileLineContent

  • or replace min with max, depending on the semantics you want)

then, you'll still need to decide how you aggregate it in your final summarize (either as min(AttemptedIngestTime), or as a group by key, e.g. startofday(AttemptedIngestTime))

regardless, you should consider following query best practices, and:

  1. replace usage of contains with has.
  2. replace usage of extract with parse.