I am writing a query to run against the SCOM 2012 OperationsManager database. SQL instance is Microsoft SQL Server 2012 – 11.0.5058.0 (X64).
Within the database is a table called Monitors
. The schema of the DB can be found here: Link to Schema
I can run the following query:
select monitorid, configurationxml
from dbo.monitor
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'
and it returns this:
monitorid configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>
(so the Monitor GUID and XML in the column configurationxml
I can then run this query:
Select
D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
D.C.value('CounterName[1]','varchar(4000)') Countertype
FROM (Select n.c.query('.') as xmlquery
from
(Select cast(ConfigurationXML as xml) Recxml
FROM [dbo].[monitor] mt)
a Cross Apply Recxml.nodes('/') N(C)) r
Cross Apply xmlquery.nodes('/') D(C)
which shreds (not sure if that is the right term) the XML and allows me to retrieve specific element values against that Column:
CheckInterval SamplesBeforeDown SystemDriveWarningThreshold Countertype
900 4 10 PercentFree
The problem I have is that I would like to be able to pull specific element values linked to the GUID in that specific row so that I can then have the GUID and the xpecific XML element values that I want, something that would look like this:
monitorid CheckInterval SamplesBeforeDown SystemDriveWarningThreshold Countertype
4960E39A-59C8-A2C2-99B1-59B73D73156F 900 4 10 PercentFree
The main problem I am encountering is that when I shred the XML it loses its reference to the row it came from.
How can I link the XML content to the row it is coming from?
Best Answer
Try this:
Note I have changed a couple of things about your code: 1) always use the
text()
accessor for better performance against untyped XML and 2) I've specified better datatypes.Let me know if that works for you.