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

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

susmita_k — Wed, 08 Jan 2025 17:23:15 GMT

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

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

krzysztof_hoja — Mon, 13 Jan 2025 13:12:22 GMT

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

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

susmita_k — Mon, 13 Jan 2025 13:42:44 GMT

Thank you. Here is the DQL

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.

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

krzysztof_hoja — Mon, 13 Jan 2025 17:15:00 GMT

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?

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

susmita_k — Mon, 13 Jan 2025 17:45:26 GMT

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.