Is there a way to get row_number () or any function to generate sequential number in DQL ?

[susmita_k]

Is there a way to get row_number () or any function to generate sequential number in DQL ?

[krzysztof_hoja]

You can generate table with indexes if you have your data in form of timeseries using iIndex() function in iterative expression. 

Can you share your query end explain where/why you want to "number" you data?

Kris

[susmita_k]

Thank you. Here is the DQL 

fetch dt.entity.host
| fieldsAdd managementZones
| expand managementZones
| filter managementZones == "mz_prod" 
|expand tags
  |parse tags, """((LD:tag (!<<'\\' ':') LD:value)|LD:tag)"""
  |filter tag == "AppName" 
|fieldsAdd AppName = value
|filter AppName == "App1"
|fieldsAdd  pm=ceil(toDouble(physicalMemory)/1000000000)
|fields AppName, entity.name,   pm
|summarize d=collectArray(record(AppName, entity.name,   pm))
|fields a = record(value = d[], index = iIndex())
|expand a
|fields test =a[value], index = a[index]
|fields AppName=test[AppName],entity.name=test[entity.name],pm=test[pm],index

I need the index (sequence #) to compare with hosts of another Management zone for the same appName.

Can you please suggest, how can I create a timeseries out of this result ? Thank you.

[krzysztof_hoja]

I used word "timeseries", while I should use just "array". I see that you successfully used iIndex() function to get entity numbering.

I rewrote the query, so it can work on my test environment (adjusted filters) and did some simplification/compressions of syntax (use of expand can be avoided ) 

fetch dt.entity.host
| filter iAny(managementZones[]=="HipsterShopLogs")

| fieldsAdd ptags = parse(tags[], """('[' LD:context ']')?((LD:tag (!<<'\\' ':') LD:value)|LD:tag)""")
| fieldsAdd ptags = record(context=ptags[][context], tag=replaceString(ptags[][tag],"\\:",":"), 
  value=replaceString(ptags[][value],"\\:",":"), orig=tags[] )

| filter iAny(ptags[][tag]=="dt.cost.costcenter" and ptags[][value]=="business-intelligence/BI") 

| fieldsAdd  pm=ceil(toDouble(physicalMemory)/1000000000)
| summarize d=collectArray(record(AppName=arrayFirst(iCollectArray(if(ptags[][tag]=="dt.cost.costcenter",ptags[])))[value] , entity.name, pm))
| fields a = record(value = d[], index = iIndex())
| expand a
| fields index = a[index], AppName=a[value][AppName],entity.name=a[value][entity.name], pm=a[value][pm]

and my result of a query looks like this:

If you have similar query for a different MZ comparing result sets is possible of, but what is the desired criteria of comparison? And what is the role of the index in it?

[susmita_k]

I am able to compare the memories of each hosts from different mz ( same app running in model and prod and we would like to compare the memory/cpu configuration in both the environment). I am looking to create a timeseries so I can configure the alert in davis AI anomaly detector and the concerned team will be notified when there is a mismatch found.