A Web Application Transaction Monitoring (ATM) object can contain several transactions. I currently have 251 ATMs objects. Each of the ATMs can contain one to many transactions. I am trying to get a handle on the number of transactions that are being sent from each server that is configured as a watcher. I feel that the best way to do this would be thru a SQL query.
Can anyone provide any assistance in creating said query?
This is going to be an extremely messy SQL query to write, because the ATM helpfully doesn’t store the URLs that are queried as either a class property or monitor config, but actually stores them in the configuration XML of a probe module that is baked into a Datasource that all the monitors attached to the ATM use. You can see this if you export the MP that contains your ATMs.
This information (to my knowledge) isn’t even synced into the SCOM DW so would only be available in the Ops DB – you can find it in the dbo.ModuleTypeView view, in the ImplementationXML column. You’ll need to use XPATH though to count the number of <Request> nodes, which will tell you how many transactions there are in the ATM (which you’ll need to link via quite a few joins).
Once that’s done, you’d then need to correlate which ATMs are running on watcher nodes – the server names are stored in one of the discovery module configurations, but you can use the MicrosoftSystemCenterWebApplicationLibrary!Microsoft.SystemCenter.WebApplication.ComputerHostsWebApplicationPerspective relationship to find which computers host perspectives. Each watcher node will have an instance of the ATM class.
tl;dr – Does this have to be a SQL query, as it would probably be much easier to build a PowerShell script to accomplish this if you only need this as an on-demand query.