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

[Uma_Boppana]

We could close this topic. Thanks to Mario 

[Uma_Boppana · Accepted solution]

Issue is with db schema.

The Appleid field was created earlier and deleted, but due to indexing it might have not been deleted.
The new field that we are seeing AppleId(notice the i in id) now, is the one we are seeing but there are two fields showing in the backend.

we had performed a rollover/indexing to have the old field deleted. And it resolved the issue

[iamryan]

Hi @Uma.Boppana,

I wanted to give this thread a nudge to see if you saw Mario's reply and want to keep the conversation going or if you found a solution you could share here.

[Uma_Boppana]

Its null. even if i click on event it shows Null value

[Morelz]

Hi, I believe the problem is not on AppDynamics side. I just tested and inserted an email address into the string field and it shows correctly

Here is my sample Curl populating the data, which works. Can you try and manually post to analytics and see if it works, might be power automate which doesn't format the email address correctly perhaps?

curl -X POST "https://xxxxxxx/events/publish/TEST" -H "X-Events-API-AccountName:xxxxxxxx" -H "X-Events-API-Key:xxxxxx" -H "Content-type: application/vnd.appd.events+json;v=2" -d '[{"expirationDateTime": 1597135561333, "appleId": "test@test.com", "DaysLeft": "176"}]'

[Uma_Boppana]

I am pushing values from power automate to appd schema.

All values getting captured under APPd schema but not AppleId which is email ID which i defined as string

here appleId value is null

why is it not capturing the value

[Morelz]

That's strange

Can you run the query "select appleid from intune_vpp1"  does it show null values as well? Also double click on any of the events and check if the email value is shown there in the popup screen or are they all null?

I know some characters cause the main view to show null event though there is a value in them, have not populated it with an email address, will do a test on my side as well

[Morelz]

Hi Uma

What do you mean the value is not getting updated, if you run the query in the query browser does it return the correct value? Is it only in the calculated metric where it doesn't return the correct value?

[Uma_Boppana]

Hi Mario,

Thanks for the update. Since its not creating updated values, we have applied formula at power automate itself.

Now issues is 

    "type": "string",
        "value": "abcd@ef.gh.com"

 value ""abcd@ef.gh.com""not poping up  under AppD schem , though the value is getting parsed.

I have initiated as string and earlier it worked , once i have deleted schema and created again, value showing as null

[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

[Morelz]

I am assuming due to the way the query is being evaluated, it doesn't just take latest value, and hence due to using max, it gets the largest value stored of that field for however long you store data in analytics.

You can either try and change the max to min which should get the lowest value always but better would be to append the following clause to the query to ensure that only the last 5 minutes of data gets used to get the value - SINCE 5 minutes

[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.

[iamryan]

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

[Morelz]

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

[iamryan]

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) 

[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

[Morelz]

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

[Morelz]

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 🙂