The XQuery below returns the list of records in which the BuildCmdString is zero and duration to execute is greater than 1 second.
When coupled with the up and coming BAM Services Adaptor for ESB.NET, this will provide the functionality to raise alerts on such queries.
eg. Send an alert when you get say 10 or more of these entries appearing etc.
WITH XMLNAMESPACES (
'urn:keystroke-com-au:ESB:Envelope:ESBEnvelope:Version:1-0-3' AS e,
'urn:schemas-biztalk-org:biztalk:BuildCmd2:BuildCmd2' as d
)
SELECT
ResolveName,
Context,
Duration,
RequestXml.query('
data(/e:ESBEnvelope/e:body/e:documentBody/d:BuildCmd2/d:BuildCmdString)') as Result
FROM
ESBLogSummary
WHERE
ResolveName ='BuildCmd2'
and
duration > 1000
and
RequestXml.value
('contains
(string
(
(/e:ESBEnvelope/e:body/e:documentBody/d:BuildCmd2/d:BuildCmdString
)[1]
), "0"
)','bit'
)=1
SELECT count(*)
FROM
ESBLogSummary
WHERE
ResolveName ='BuildCmd2'
and
duration > 1000
and
RequestXml.value
('contains
(string
(
(/e:ESBEnvelope/e:body/e:documentBody/d:BuildCmd2/d:BuildCmdString
)[1]
), "0"
)','bit'
)=1