Creating custom metrics for ADQL query for monitoring Intune connectors

[Uma.Boppana]

1] Tried using Until since to pull the no of days between the expirationDateTime and system date, based on token name as we have many token names

expirationDateTime	eventTimestamp	pickupTimestamp
2025-07-26T23:00:03+05:30	2024-11-21T17:06:33+05:30	2024-11-21T17:06:33+05:30

Token name

AppD

can you suggest the query to be used such that we get value in no of days the certificate gets expired

[Ryan.Paredez]

Hi @Uma.Boppana,

Thank you for asking your question on the community. Did you happen to find a solution to your question or any new information you can share here?

If not, and you are still looking for help, you can contact AppDynamics Support: How to contact AppDynamics Support and manage existing cases with Cisco Support Case Manager (SCM) 

[Mario.Morelli]

Hi Uma

You just have to create a metric per Token and use a query like this

SELECT toInt(expirationDateTime- eventTimestamp) AS "Seconds" which will give you the difference in seconds between the dates, you can then further multiply the seconds to get minutes/hours or days if you want to rather use that.

This will give you the metric to tell you how much seconds/minutes/hours/days to expiry and you can then alert on it

Ciao

[Uma.Boppana]

Hi Ryan,

Support provided query for which we are unable to create metrics .

we have tweeked s below

SELECT (toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)) AS daysleft FROM intune_dep WHERE tokenName = "Wipro-EY-Intune" AND (toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)) >= 30

and when created metrics we are getting error

Following fields (toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)) AS daysleft in the select clause are not supported.

could you help on the query

[Ryan.Paredez]

@Mario.Morelli can you provide any further insight for @Uma.Boppana on this?

[Mario.Morelli]

There are some limitations with what functions you can use with the analytics metrics with only aggregation queries supported min,max avg etc..

try and use this , the max value essentially will do nothing and not change the value, but it allows the metric to be saved as you use an aggregation function

SELECT max(toInt((toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)))) FROM intune_dep WHERE tokenName = "Wipro-EY-Intune" AND (toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)) >= 30

SELECT max(toInt((tokenExpirationDateTime - now()) / (24*60*60*1000)))) FROM intune_dep WHERE tokenName = "Wipro-EY-Intune" AND (toInt(tokenExpirationDateTime - now()) / (24*60*60*1000)) >= 30

Let me know if this works

[Uma.Boppana]

Hi Mario,

Thanks, query worked as we input max

can we do to alias the result filed name (toInt((tokenExpirationDateTime - now()) / (24*60*60*1000))))  to tokenExpirationDateTime

[Mario.Morelli]

Hi Uma

As far as I know you cant uses alias field, but that shouldn't be an issue, as you are creating the metric, just name it what you require then the metric will be named as you need it in the health rules and dashboards

[Uma.Boppana]

Hi Mario,

Thanks for the response. When i added the query as metric, i m getting old value.

For ex, the expiration days are 219 days. but it shows 229 days on the day when i created the metric. why is that it not showing the current value. the value is not changing.

[Uma.Boppana]

we are feeding data for every 5 mins and if you see the data its 229 all the time in metric graph where as when we execute the query its different 219 value.

16/12/2024 23:55:00,46,0,229,229,5

17/12/2024 23:55:00,46,0,229,229,5

18/12/2024 23:55:00,46,0,229,229,5