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?
Hey Michouser,
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.
Do you have any specific class in mind for this?
SCOMTR,
No I do not have any particular class in mind. I am looking to get a total of the transactions that are each watcher that I have currently in use.
Here is some additional info that might be of use.
I believe the class that you are looking for is called ‘Web Application Perspective.’
This I believe is the parent class, because the individual requests are contained in each Web Application Perspective. This is what I call an ATM.
I discovered that each request is a component under a single monitor. So this is what it would look like
Root ATM (monitor)
Entity Health
Availability
Web Application – blah blah blah
Request 1 – blah 1
Request 2 – blah 2
Request 3 – blah 3 ……
Configuration
Performance
Security
There has to be a field that indicates what the watcher is related to the over all ATM. The requests is what I am trying to total.
Item labeled one is the watcher node and item labeled two is the transaction.
I would like to try a SQL query first only because I do not know how to represent it graphical through squared up, can you help? I have next to no experience writing SQL queries. Do you know of a way to graphically represent this value in Squared Up? Any help you can provide would be greatly appreciated.
I’m afraid I don’t have the free time to drop into putting a query of that complexity together, hopefully another community member will.
I know this isn’t exactly what you are looking for, but since I ended up writing it out of my own curiosity, here is a PowerShell script that can retrieve the info you are after. If you run it on a MGMT server it’ll connect to that by default, otherwise you’ll need to specify your management server.
Param(
[Parameter(Mandatory=$false)]
[ValidateNotNullOrEmpty()]
[string] $ManagementServer = ‘localhost’
)
Import-Module OperationsManager -ErrorAction Stop
$connection = New-SCOMManagementGroupConnection -ComputerName $ManagementServer -ErrorAction Stop -PassThru
$mg = Get-SCOMManagementGroup | where {$_.Name -eq $connection.ManagementGroupName}
#Define recursive function to harvest all unit Monitors
function Get-SCOMUnitMonitors
{
Param (
[Parameter(Mandatory=$true)]
$node
)
if ($node.Item.XmlTag -eq ‘UnitMonitor’)
{
return $node.Item
}
elseif ($node.childnodes.count -gt 0)
{
Foreach ($child in $node.ChildNodes)
{
Get-SCOMUnitMonitors -node $child
}
}
}
$agents = @{}
$webTransactionMonitors = Get-SCOMClass -Name ‘Microsoft.SystemCenter.WebApplication.Perspective’ | Get-SCOMClassInstance
foreach ($transaction in $webTransactionMonitors)
{
# Update the agent dictionary
$unitMonitor = Get-SCOMUnitMonitors -node ($transaction.GetMonitorHierarchy()) | select -First 1
$ds = $mg.GetMonitoringModuleType($mg.GetUnitMonitorType($unitMonitor.TypeID.Id).Datasourcecollection[0].typeid.id)
$agents[$transaction.path] += ([xml]"$($ds.ProbeActionCollection[0].Configuration)").SelectNodes(’//Request’).count
}
#Return results
$agents.GetEnumerator() | Select-Object @{N=‘Agent’;E={$.key}},@{N=‘Requests’;E={$.value}} | Write-Output
