Analytics search - ADQL Query to manipulate values from a header using mathematic experessions

[CommunityUser]

Hi,

we have a Analytics search query for a header  that returns the following payload.

{"QuoteID":"0409657629","Annual premium":"10115","Line of business":"bop","State":"ND","AgencyCode":"0004045","method":"getPPC"}

my query is

SELECT segments.httpData.headers.`application-data` AS "application-data", count(*) FROM transactions WHERE segments.httpData.headers.`application-data` = "{\"QuoteID\":\"*\",\"Annual premium\":\"*\",\"Line of business\":\"bop\",\"State\":\"UT\",\"AgencyCode\":\"*\",\"method\":\"getPPC\"}" 

We are using the above query so that i can get data for each state by changing the value for state with the relevant abrevation. In the example above UT can be changed to MN etc.

We are am looking for a way that I can get the "Total Premium" by addidng all the Annual Premium ammounts.

please check attached image which shows the search result. 

Is there a way we can use mathematical expression on part of the payload data.

[CommunityUser]

Have not heard back from the Client.

will check and get back to you.

[CommunityUser · Accepted solution]

Hi Ajay,

From the screenshot attached, I can see that Annual Premium ammounts is part of an array of values which I believe is returned from your Data Collector.

So in this case, first you need to tune your Data Collctor config in a way that you only get the return of Annual Premium ammounts value and then you can use ADQL SUM function to sum the required values based on matching criteria.

https://docs.appdynamics.com/display/PRO44/Analytics+Functions

Let me know if this helps.

Thanks,

Deepanshu