topic Re: Looking to get help with DQL in DQL

Looking to get help with DQL

MAYUMUKHERJEE — Fri, 24 May 2024 09:39:22 GMT

I am using Dynatrace to monitor Salesforce using Salesforce Insight app connector.

I have a scheduled workflow which scans BIZEVENTS to find list of specific user's login count in last 1 hr.

The query I have is:

fetch bizevents, from:now()-1h
| filter (event.type == "salesforce.LoginEvent" AND Application == "Browser")
| join [ fetch logs, from:now()-1d
| filter dt.system.bucket == "monitoring_user_list"
| filter contains(attributes, "user")], on:{left[UserId] == right[id]}

So we nee to monitor only users stored in "monitoring_user_list" and I am doing a join to get only those login events where user already exists in "monitoring_user_list".

But I also need to find how many times these users already found in above query logged-in as well (salesforce.LoginEvent - Dynatrace get this event bizevent data when someone login to Salesforce).

Re: Looking to get help with DQL

krzysztof_hoja — Sat, 25 May 2024 08:27:15 GMT

If I understand the need correctly and to introduce minimal changes to the query, I would add only this line to it:

| summarize {cnt=count(), firstLogin=takeMin(timestamp), lastLogin=takeMax(timestamp)}, by:{UserId}

as a additional advice, attention needs to be paid to result size of subqueries (in this case in "join [<subquery>]"). If result size it too big, query will fail. In this case use of dedup or summarize inside increases chances for success, i.e.

fetch bizevents, from:now()-1h | filter (event.type == "salesforce.LoginEvent" AND Application == "Browser") | filter UserId in [ fetch logs, from:now()-1d | filter dt.system.bucket == "monitoring_user_list" | filter contains(attributes, "user") | dedup id | fields id ] | summarize {cnt=count(), firstLogin=takeMin(timestamp), lastLogin=takeMax(timestamp)}, by:{UserId}

Kris