Splunk AppDynamics

Creating custom metrics for ADQL query for monitoring Intune connectors

Uma_Boppana
Path Finder

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

expirationDateTimeeventTimestamppickupTimestamp
2025-07-26T23:00:03+05:302024-11-21T17:06:33+05:302024-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

Labels (1)
0 Karma
1 Solution

Uma_Boppana
Path Finder

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

View solution in original post

0 Karma

Uma_Boppana
Path Finder

We could close this topic. Thanks to Mario 

0 Karma

Uma_Boppana
Path Finder

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

0 Karma

iamryan
Community Manager
Community Manager

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.

0 Karma

Uma_Boppana
Path Finder

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

image.png

0 Karma

Morelz
Builder

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

image.png

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

0 Karma

Uma_Boppana
Path Finder

I am pushing values from power automate to appd schema.

image.png

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

here appleId value is null

image.png

why is it not capturing the value

0 Karma

Morelz
Builder

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

0 Karma

Morelz
Builder

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?

0 Karma

Uma_Boppana
Path Finder

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

0 Karma

Uma_Boppana
Path Finder

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

0 Karma

Morelz
Builder

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

0 Karma

Uma_Boppana
Path Finder

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.

image.png

0 Karma

iamryan
Community Manager
Community Manager

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

0 Karma

Morelz
Builder

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
Community Manager
Community Manager

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) 

0 Karma

Uma_Boppana
Path Finder

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.

image.png

could you help on the query

0 Karma

Morelz
Builder

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
Path Finder

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

image.png

0 Karma

Morelz
Builder

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 🙂

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.3.2411?

Hey Splunky People! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2411. This release ...

Buttercup Games: Further Dashboarding Techniques (Part 6)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...