Looking to get help with DQL

[MAYUMUKHERJEE]

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

[krzysztof_hoja]

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