topic Re: Creating custom metrics for ADQL query for monitoring Intune connectors in Splunk AppDynamics

Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Thu, 21 Nov 2024 11:46:25 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

iamryan — Mon, 25 Nov 2024 18:08:39 GMT

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)

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Tue, 26 Nov 2024 09:50:21 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Thu, 05 Dec 2024 09:55:48 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

iamryan — Thu, 05 Dec 2024 20:22:34 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Fri, 06 Dec 2024 08:15:16 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Fri, 06 Dec 2024 11:39:39 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Wed, 11 Dec 2024 07:38:52 GMT

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 🙂

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Thu, 19 Dec 2024 10:36:56 GMT

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.

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Thu, 19 Dec 2024 10:40:48 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Thu, 19 Dec 2024 10:46:54 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Fri, 20 Dec 2024 06:50:41 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Fri, 20 Dec 2024 09:45:38 GMT

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?

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Fri, 20 Dec 2024 09:55:17 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Fri, 20 Dec 2024 10:04:16 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Fri, 20 Dec 2024 10:11:30 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Morelz — Fri, 20 Dec 2024 10:22:15 GMT

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"}]'

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

iamryan — Mon, 06 Jan 2025 18:41:52 GMT

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.

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Wed, 22 Jan 2025 08:23:47 GMT

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

Re: Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana — Wed, 22 Jan 2025 08:24:55 GMT

We could close this topic. Thanks to Mario