-- ##### Veeam_CheckServiceAvailability_Install.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Veeam_CheckServiceAvailability')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Veeam_CheckServiceAvailability] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Veeam_CheckServiceAvailability]
@mg UNIQUEIDENTIFIER,
@reportType NVARCHAR(MAX)
AS
BEGIN
IF OBJECT_ID('tempdb..#entity') IS NOT NULL DROP TABLE #entity
IF OBJECT_ID('tempdb..#entityEncode') IS NOT NULL DROP TABLE #entityEncode
CREATE TABLE #entity(ManagedEntityRowId INT)
CREATE TABLE #entityEncode
(
id INT IDENTITY(1,1),
ManagedEntityRowId INT,
licExpired NVARCHAR(512),
licEdition NVARCHAR(512)
)
--CAST(CAST(N'' AS XML).value('xs:base64Binary('+''''+ vmeps.PropertyValue+''''+')', 'VARBINARY(MAX)') AS VARCHAR(MAX))
DECLARE @licExpired NVARCHAR(512),
@licEdition NVARCHAR(512),
@RowId INT,
@ManagedEntityRowId INT
--ranging VES by version property
;WITH cte (vrank,vversion,ManagedEntityRowId) AS (
SELECT dense_RANK() OVER (ORDER BY vmeps2.PropertyValue DESC),vmeps2.PropertyValue,vme.ManagedEntityRowId
FROM [dbo].[vManagedEntity] vme
INNER JOIN [dbo].[vManagedEntityType] vmet ON vmet.ManagedEntityTypeRowId = vme.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityTypeProperty AS vmetp2 ON vmetp2.ManagedEntityTypeRowId=vmet.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityPropertySet AS vmeps2 ON vmeps2.PropertyGuid = vmetp2.PropertyGuid and vmeps2.ManagedEntityRowId = vme.ManagedEntityRowId
WHERE vmeps2.ToDateTime IS NULL AND vmetp2.PropertySystemName='version' AND vmet.ManagedEntityTypeSystemName='Veeam.Virt.Extensions.VMware.EMServer'
)
INSERT INTO #entityEncode(ManagedEntityRowId, licExpired, licEdition)
SELECT DISTINCT
vme.ManagedEntityRowId,
vmeps.PropertyValue,
vmeps1.PropertyValue
FROM
[dbo].[vManagedEntity] vme
INNER JOIN [dbo].[vManagedEntityType] vmet ON vmet.ManagedEntityTypeRowId = vme.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityTypeProperty AS vmetp ON vmetp.ManagedEntityTypeRowId=vmet.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityTypeProperty AS vmetp1 ON vmetp1.ManagedEntityTypeRowId=vmet.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityTypeProperty AS vmetp2 ON vmetp2.ManagedEntityTypeRowId=vmet.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityPropertySet AS vmeps ON vmeps.PropertyGuid = vmetp.PropertyGuid and vmeps.ManagedEntityRowId = vme.ManagedEntityRowId
INNER JOIN dbo.vManagedEntityPropertySet AS vmeps1 ON vmeps1.PropertyGuid = vmetp1.PropertyGuid and vmeps1.ManagedEntityRowId = vme.ManagedEntityRowId
INNER JOIN cte AS cte ON cte.ManagedEntityRowId = vme.ManagedEntityRowId
INNER JOIN dbo.vManagementGroup AS vmg ON vmg.ManagementGroupRowId=vme.ManagementGroupRowId
WHERE
vmet.ManagedEntityTypeSystemName='Veeam.Virt.Extensions.VMware.EMServer' AND
(vmetp.PropertySystemName='licExpired' AND vmeps.ToDateTime IS NULL ) AND
(vmetp1.PropertySystemName='licEdition' AND vmeps1.ToDateTime IS NULL )
--get only newest version of VES
AND cte.vrank='1'
AND vmg.ManagementGroupGuid=@mg
SET @RowId = -1
WHILE EXISTS
(
SELECT TOP(1)
id
FROM #entityEncode tgb
WHERE tgb.id > @RowId
)
BEGIN
SELECT TOP(1)
@RowId=tgb.id,
@ManagedEntityRowId=tgb.ManagedEntityRowId,
@licExpired=tgb.licExpired,
@licEdition=tgb.licEdition
FROM #entityEncode AS tgb WHERE tgb.id > @RowId
INSERT INTO #entity(ManagedEntityRowId)
SELECT
@ManagedEntityRowId
WHERE CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@licExpired"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX))='False' AND
CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@licEdition"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX))='Enterprise Plus'
END
DECLARE @rowCount1 INT, @rowCount2 INT
SELECT @rowCount1 = COUNT(*) FROM #entityEncode
SELECT @rowCount2 = COUNT(*) FROM #entity
IF NOT EXISTS (SELECT * FROM #entity) OR @rowCount1 <> @rowCount2
BEGIN
SELECT
[Message] = 'This Veeam MP component requires an Enterprise Plus license. To get more information and obtain an evaluation or full version of the Veeam Management Pack, please contact your Veeam Sales team or click the URL below: ',
[LinkLabel] = 'http://vee.am/mplicense',
[LinkURL] = 'http://vee.am/mplicense'
END
ELSE
BEGIN
;WITH LastDate([Date], ManagedEntityRowId, ManagedEntityMonitorRowId)
AS
(
SELECT
MAX(va.[DateTime]),
vme.ManagedEntityRowId,
va.ManagedEntityMonitorRowId
FROM
dbo.vMonitor AS vm
INNER JOIN dbo.vManagedEntityMonitor AS vmem ON vmem.MonitorRowId = vm.MonitorRowId
INNER JOIN [State].vStateRaw AS va ON va.ManagedEntityMonitorRowId = vmem.ManagedEntityMonitorRowId
INNER JOIN dbo.vManagedEntityManagementGroup AS vmemg ON vmemg.ManagedEntityRowId = vmem.ManagedEntityRowId
INNER JOIN #entity AS vme ON vme.ManagedEntityRowId=vmem.ManagedEntityRowId
WHERE
vm.MonitorSystemName='Veeam.Virt.Extensions.VMware.EMServer.Monitor.EMService' AND vmemg.ToDateTime IS NULL
GROUP BY vme.ManagedEntityRowId,
va.ManagedEntityMonitorRowId
)
SELECT
[Message] = 'This Veeam MP component requires an Enterprise Plus license. To get more information and obtain an evaluation or full version of the Veeam Management Pack, please contact your Veeam Sales team or click the URL below: ',
[LinkLabel] = 'http://vee.am/mplicense',
[LinkURL] = 'http://vee.am/mplicense'
FROM LastDate AS ld
INNER JOIN [State].vStateRaw AS va ON va.ManagedEntityMonitorRowId = ld.ManagedEntityMonitorRowId AND va.[DateTime]=ld.[Date]
WHERE va.NewHealthState <> 1
END
IF OBJECT_ID('tempdb..#entity') IS NOT NULL DROP TABLE #entity
END
GO
GRANT EXECUTE ON dbo.[Veeam_CheckServiceAvailability] TO OpsMgrReader
GO
Home