Ray, couple of thoughts on this:
- DPA persists only certain data about session activity currently (select metadata - basically the dimensional tabs in the UI)
- You can watch for current sessions and keep refreshing them, but we're only as good as what information is sent from the client application - some send more client info or session metadata than others
- If you want to do this programmatically, you could probably capture it periodically using an alert that can send you some data, but it's going to be a manual process to collate it - it's just a collection engine now to gather raw data
Note that all of this is pretty custom and not what DPA was designed to do, but I'd think with some creative thought, you could use DPA's mechanisms to at least do some sampling.
If you do come up with some good stuff on this, it's always appreciated by the community to share back what you might come up with...
Let me know if that isn't the right direction you were looking to go.
I was hoping that DPA was capturing some of the detail for the active sessions for at least a short period of time but I guess it's just aggregating it.
Sounds like what I'll have to do is either setup something that captures info on the active sessions on a defined interval, and/or setup an alert that captures the info when the number of active sessions exceeds a certain threshold.
All I'm really looking for is the session login name, host name, and application name if set.
I use a stored proc (sp_whoisactive) when I'm monitoring manually, I think I can use the same proc to capture the info I need when triggered.
I can then collate and aggregate the raw data captured.