Hi Team,
I’m using below query for availability report in SCOM DW sql database.But while im using same query in grafana receiving below error. As im new to grafana not able identify the issue. Please help me with the issue.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SELECT ws.DisplayName AS ServerName,ISNULL(AVAL.[DownTime (in MS)],0) AS ‘DownTime (in MS)’,
–ws.ManagedEntityRowId
PMM.StartDateTime,
PMM.EndDateTime,
case
when PMM.StartDateTime IS NOT NULL and Pmm.EndDateTime IS NULL then ‘Yes’
else ‘No’
End AS ‘SCOMMaintainenceMode’
FROM OperationsManagerDW.dbo.XXXCustom_WindowsServer ws
LEFT JOIN
(
SELECT me.DisplayName, SUM(CAST(InRedStateMilliseconds AS BIGINT)) AS ‘DownTime (in MS)’–MM.StartDateTime,MM.EndDateTime,me.FullName
–case
–when mm.EndDateTime IS NULL then ‘yes’
–else ‘no’
–End AS ‘MaintainenceMode’
FROM OperationsManagerDW.State.vStateDaily sr
INNER JOIN OperationsManagerDW.dbo.vManagedEntityMonitor vem ON vem.ManagedEntityMonitorRowId = sr.ManagedEntityMonitorRowId
INNER JOIN OperationsManagerDW.dbo.vMonitor m ON m.MonitorRowId = vem.MonitorRowId
INNER JOIN OperationsManagerDW.dbo.vManagedEntity me ON me.ManagedEntityRowId = vem.ManagedEntityRowId
INNER JOIN OperationsManagerDW.dbo.vDisplayString ds ON ds.ElementGuid = m.MonitorGuid
–INNER JOIN dbo.vMaintenanceMode AS MM ON ME.ManagedEntityRowId = MM.ManagedEntityRowId
–INNER JOIN vMaintenanceMode mm on me.ManagedEntityRowId=mm.ManagedEntityRowId
WHERE (ds.Name = ‘Computer Not Reachable’
and (sr.DateTime Between DATEADD(MINUTE,-480,DATEADD(d,DATEDIFF(d,0,@FromDate),0)) and DATEADD(MINUTE,-480,DATEADD(d,DATEDIFF(d,0,@ToDate),0)))) and InRedStateMilliseconds != 0
GROUP BY me.DisplayName
) AVAL ON ws.DisplayName=AVAL.DisplayName
LEFT JOIN v_rs_permmaintmode PMM ON WS.DisplayName=PMM.DisplayName
–MAINT ON MAINT.ManagedEntitiyRowId=dbo.vMaintenanceMode.ManagedEntityRowId)
WHERE ws.DisplayName NOT IN (‘Microsoft.SystemCenter.AgentWatchersGroup’) AND ws.DisplayName NOT LIKE ‘%XXX-%-Group%’ --and AVAL.FullName Like ‘%Microsoft.Windows.Computer:%’ and AVAL.EndDateTime IS NULL
ORDER BY ws.DisplayName
–and AVAL.FullName like ‘%Microsoft.Windows.Computer:%’ and mm.EndDateTime IS NULL
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
mssql: Must declare the scalar variable “@FromDate”.