Silly question, but I am stuck. Our BI team wanted to use Solarwinds SQL User Experience Monitor to alert on errors that occurred on Sharepoint Services Reporting Server (SSRS). The ExecutionLog table to contains data "rsSuccess" about success or failure when reports are run. I was given the query which is below. When I use this within the experience monitor it fails with the error "Unable to convert query result"
CONVERT(VARCHAR(13), TimeStart, 20) AS 'Date'
,count(*) 'Report Count'
FROM [sps_ssrs_10_onestop_reportserver].[dbo].[ExecutionLog] e with (nolock)
join [sps_ssrs_10_onestop_reportserver].[dbo].Catalog c with (nolock)
on e.ReportID = c.ItemID
WHERE TimeStart between '2017-05-01 00:00:00' and '2017-05-09 23:59:59'
and status <> 'rsSuccess'
group by CONVERT(VARCHAR(13), TimeStart, 20), InstanceName
order by CONVERT(VARCHAR(13), TimeStart, 20), InstanceName
The monitor would trigger if there were more than 10 errors over a given period of time. My SWQL and SQL skills are a bit lacking and I am wits end. I found that the convert string doesnt work in SWQL, and I cannot find a proper replacement, or I am missing something where in the documentation.
Thanks in advance for any assistance provided.
SWQL queries are not sent directly to SQL Server. They are processed according to the SWIS schemas in the product to determine what data needs to be fetched from where. Since the SWIS schemas don't know anything about "[sps_ssrs_10_onestop_reportserver].[dbo].Catalog", this is not going to work.