No Return of Colums with System!System.OleDbProbe

Dashboard Server
,
1

Hello!

I have a monitor for requesting a Database.
The request is running without error. But I don’t get the colums:

<DataItem type="DatabaseResultSet" time="2024-03-01T07:20:01.1085651Z" sourceHealthServiceId="d6192751-74d3-b8ae-610d-af830314c502">
	<DataItem type="System.OleDbData" time="2024-03-01T08:20:01.1085651+01:00" sourceHealthServiceId="D6192751-74D3-B8AE-610D-AF830314C502">
		<HRResult>0</HRResult>
		<ResultLength>8</ResultLength>
		<Result>Success</Result>
		<InitializationTime>44</InitializationTime>
		<OpenTime>0</OpenTime>
		<ExecutionTime>1045</ExecutionTime>
		<FetchTime>1</FetchTime>
		<RowLength>0</RowLength>
		<OriginalDataLength>0</OriginalDataLength>
		<ErrorDescriptionLength>0</ErrorDescriptionLength>
		<ResultCode>0</ResultCode>
	</DataItem>
</DataItem>

RowLength is 0

My Config is:

<ProbeAction ID="Probe" TypeID="System!System.OleDbProbe">
    <ConnectionString>$Config/ConnectionString$</ConnectionString>
    <Query>$Config/SQLQuery$</Query>
    <GetValue>true</GetValue>
    <IncludeOriginalItem>false</IncludeOriginalItem>
    <OneRowPerItem>true</OneRowPerItem>
    <QueryTimeout>30</QueryTimeout>
    <GetFetchTime>true</GetFetchTime>
</ProbeAction>

My Query is

SELECT Product, Mandator, State FROM [xxx].[dbo].[xxx] WITH (NOLOCK) WHERE Product= '$Config/Product$' AND Mandator = '$Config/Mandator$'

What have I forgotten?

rg
Hansi

2

Have you tried simply creating a OLEDB monitor in the authoring pane and see what is in the XML? Haven’t used one but seems (from your XML) like it doesn’t return data. You may need to use a script…

3

thx.
I have tried with script and get back data. Don’t know why. But If i get the solution I wil post it.

rg

4

Quick (untested) script if you want.

param (
    [Parameter(Mandatory = $true)]
    [string]$Server
    ,
    [Parameter(Mandatory = $true)]
    [string]$Database
    ,
    [Parameter(Mandatory = $false)]
    [string]$SQLQuery = 'SELECT Product, Mandator, State FROM [xxx].[dbo].[xxx] WITH (NOLOCK)'
	,
    [Parameter(Mandatory = $false)]
	[switch]$ShowBag = $false
	 ,
	[Parameter(Mandatory = $false)]
	[switch]$ShowLogs = $($Host.Name -notmatch 'OpsMgr PowerShell')
)
$ElapsedTime = [System.Diagnostics.Stopwatch]::StartNew()
$Script:RetLog = "Script Started at $(Get-Date)`nVersion: 1.0.0 -- Last Modified: 2022-00-00 @ 00:00"
$Script:RetLog += "`nUser running script: $($env:USERNAME)`nPoSH Name: $($Host.Name) v$($host.Version)"
$Script:RetLog += "`nParameters Provided:$($PSBoundParameters.Keys | ? {$_ -notmatch 'Password|Pwd' } | ForEach-Object{"`n{0} : {1}" -f $_, $($PSBoundParameters.Item($_))})`n`n"
$ScriptName = "Probe Script"
$RetEventID = 4100
$oAPIMaxLog = 30000

if ($Host.Name -notmatch 'OpsMgr PowerShell') {
	$RetEventID += 1
	"Note that EventID will be different: $RetEventID"
}

$oAPI = New-Object -comObject 'MOM.ScriptAPI'

#Region HelperFunctions
Function Add-Log {
	param
	(
			[Parameter(Mandatory = $true,
					   Position = 0)]
			[ValidateNotNullOrEmpty()]
			[string]$Message,
			[int]$NumSpace = 0,
			[Alias('ShowLogs')]
			[switch]$ALShowLogs = $ShowLogs,
			[int]$EventType = 0
				#0 = Informational
				#1 = Error
				#2 = Warning
	)

	if ($ALShowLogs) {
		$oAPI.LogScriptEvent($ScriptName + ' --DETAILS', $($RetEventID - 1000), $EventType, $Message)
        Switch ($EventType) {
            (0) {Write-Output (("`n" * $NumSpace) + $Message)}
            (1) {Write-Output (("`n" * $NumSpace) + $Message) -ForegroundColor Red}
            (2) {Write-Output (("`n" * $NumSpace) + $Message) -ForegroundColor Yellow }
        }
	}
	$Script:RetLog += "$("`n" * ($NumSpace + 1))$Message"
}

Function Add-Bag {
    param
    (
        [Parameter(Mandatory = $true, Position = 0)]
        [ValidateNotNullOrEmpty()]
        [System.Collections.Hashtable]$BagData
    )
	Add-Log "Adding to Bag: $( $BagData.Keys | %{ "`n`t{0}: {1}" -f $_,$($BagData.Item($_)) } )" -NumSpace 1

    $oBag = $oAPI.CreatePropertyBag()
    $BagData.Keys | %{
        $oBag.AddValue($_, $BagData.Item($_))
    }

   	if ($ShowBag) {
		$oAPI.Return($oBag); "`n"
	} else {
		$oBag
	}
}
#EndRegion

try {
	#Do Probe here
	$ConnectionString = 'Server = {0}; Database = {1}; Integrated Security = True' -f $Server, $Database
    Function Get-DBData {	
		Param(
			[string]$SqlQuery
		)
		try {
           # if ($ShowLogs) { Add-Log ('Running query "{1}" using Connection String "{0}"' -f $ConnectionString,$SqlQuery)  }
			$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
			$SqlConnection.ConnectionString = $ConnectionString
			$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
			$SqlCmd.CommandText = $SqlQuery
			$SqlCmd.Connection = $SqlConnection
			$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
			$SqlAdapter.SelectCommand = $SqlCmd
			$DS = New-Object System.Data.DataSet
			$SqlAdapter.Fill($DS) | Out-Null
			$SqlConnection.Close()
			$DS.Tables.Rows
		} catch {
			Add-Log ('Error capturing data from database. More information: {0}' -f $_.InvocationInfo.PositionMessage) -EventType 2
		}
	}

    Get-DBData -SqlQuery $SQLQuery | %{
        Add-Bag @{
            Product = $_.Product
            Mandator = $_.Mandator
            State = $_.State
        }
    }
	
} catch {
	$LstError = $_
	Add-Log "Error occurred while capturing probe data at line $($LstError.InvocationInfo.ScriptLineNumber):`n$LstError`n$($LstError.InvocationInfo.PositionMessage)" -NumSpace 3 -EventType 1
	if ($ShowLogs) { "`n`n`n"; $Script:RetLog }
	For ($i=0;$i -le ($Script:RetLog.Length - $oAPIMaxLog); $i += $oAPIMaxLog) {
		trap { continue } $oAPI.LogScriptEvent($ScriptName, $RetEventID, 1, $Script:RetLog.Substring($i,$oAPIMaxLog))
	}
	if ($i -le $Script:RetLog.Length){
		trap { continue } $oAPI.LogScriptEvent($ScriptName, $RetEventID, 1, $Script:RetLog.Substring($i))
	}
	$oAPI.LogScriptEvent($ScriptName, $($RetEventID + 100), 1, $LstError) #also write error on separate entry just in case previous fails
} finally {
	Add-Log ("Script Run Time: {0:g} " -f $ElapsedTime.Elapsed)
	For ($i=0;$i -le ($Script:RetLog.Length - $oAPIMaxLog); $i += $oAPIMaxLog) {
		trap { continue } $oAPI.LogScriptEvent($ScriptName, $RetEventID, 0, $Script:RetLog.Substring($i,$oAPIMaxLog))
	}
	if ($i -le $Script:RetLog.Length){
		trap { continue } $oAPI.LogScriptEvent($ScriptName, $RetEventID, 0, $Script:RetLog.Substring($i))
	}
	if ($ShowLogs) {"RetLog Length: $($Script:RetLog.Length) chars"}
	Remove-Variable -Name RetLog -Scope Global -Confirm:$false	
	#(powershell -noprofile -command "$($psISE.CurrentFile.FullPath) -ShowLogs:`$false -showbag") | Select -Skip 1 |Set-Content $($psISE.CurrentFile.FullPath -replace 'ps1$','xml'); ii $($psISE.CurrentFile.FullPath -replace 'ps1$','xml')
}

PS: I’d recommend not filtering the data at the SQL level but in a CD later in the workflow to allow for cookdown.

5

Hello!

Thanks to all answers. I have found the error. The ProbeAction is correct.
But I think I had a problem with my Config-Variables. Maybe it was an error with discovery. I have tried the same sql-request with SQL-Template in SCOM and here i get correct data.
I suspect that something went wrong when importing the discovery data.
So I have changed the discovery and now i get correct data.

thx for help.
rg