﻿-- DBAOps Enterprise™ - Create Functions
-- Generated: 2026-02-05 08:20:54
-- Functions: 14
USE [DBAOpsRepository];
GO

-- [ai].[fn_BuildServerContext]

-- Function to build diagnostic context JSON
CREATE   FUNCTION AI.fn_BuildServerContext(@ServerName NVARCHAR(128))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Context NVARCHAR(MAX);
    
    SELECT @Context = (
        SELECT 
            si.ServerName,
            si.Environment,
            si.SQLVersion,
            si.SQLEdition,
            si.CPUCount,
            si.TotalMemoryGB,
            si.MaxDOP,
            si.CostThreshold,
            si.CMSCriticality,
            si.CMSGroupName
        FROM config.ServerInventory si
        WHERE si.ServerName = @ServerName
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    
    RETURN ISNULL(@Context, '{}');
END;

GO

-- [ai].[fn_GetConfig]

-- Function to get AI config value
CREATE   FUNCTION AI.fn_GetConfig(@Key NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Value NVARCHAR(MAX);
    SELECT @Value = ConfigValue FROM AI.Configuration WHERE ConfigKey = @Key;
    RETURN @Value;
END;

GO

-- [config].[fn_GetMonitoredServers]

CREATE FUNCTION config.fn_GetMonitoredServers()
RETURNS TABLE
AS
RETURN
(
    SELECT DISTINCT
        cms.server_name AS ServerName,
        cms.server_group_id AS GroupId
    FROM msdb.dbo.sysmanagement_shared_registered_servers_internal cms
);

GO

-- [config].[fn_GetServersByRole]

CREATE FUNCTION config.fn_GetServersByRole
(
    @RoleName NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
(
    /*
    PURPOSE: Get all servers for a specific role
    USAGE: SELECT * FROM config.fn_GetServersByRole('Business Intelligence')
    */
    SELECT 
        inv.ServerName,
        inv.Environment,
        inv.SQLVersion,
        inv.SQLEdition,
        inv.IsActive
    FROM config.ServerInventory inv
    INNER JOIN config.ServerRoleMapping srm 
        ON inv.ServerName = srm.ServerName
    WHERE srm.ServerRole = @RoleName
      AND srm.IsActive = 1
      AND inv.IsActive = 1
);

GO

-- [correlation].[fn_GetCMSGroup]

-- =============================================================================
-- FUNCTION: correlation.fn_GetCMSGroup
-- Gets CMS group for a server (SOURCE OF TRUTH)
-- =============================================================================
CREATE   FUNCTION correlation.fn_GetCMSGroup(@ServerName NVARCHAR(128))
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @GroupName NVARCHAR(200);
    
    -- Get from CMS (source of truth)
    SELECT TOP 1 @GroupName = g.name
    FROM msdb.dbo.sysmanagement_shared_registered_servers_internal s
    INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal g 
        ON s.server_group_id = g.server_group_id
    WHERE s.server_name = @ServerName;
    
    -- Fallback to config.ServerInventory if not in CMS directly
    IF @GroupName IS NULL
    BEGIN
        SELECT TOP 1 @GroupName = Environment
        FROM config.ServerInventory
        WHERE ServerName = @ServerName AND IsActive = 1;
    END
    
    RETURN ISNULL(@GroupName, 'UNKNOWN');
END

GO

-- [DBAOps].[fn_CalculateEfficiencyScore]

-- Create efficiency score calculation function
CREATE FUNCTION DBAOps.fn_CalculateEfficiencyScore()
RETURNS INT
AS
BEGIN
    DECLARE @Score INT = 0;
    
    -- Calculate based on evaluation results
    SELECT @Score = AVG(Score)
    FROM DBAOps.EvaluationResults
    WHERE EvaluationDate >= DATEADD(MINUTE, -30, SYSDATETIME());
    
    RETURN ISNULL(@Score, 0);
END;

GO

-- [dbo].[fn_ExtractCUNumber]

CREATE FUNCTION dbo.fn_ExtractCUNumber (@CUString VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0;
    DECLARE @CleanString VARCHAR(50);
    
    -- Handle NULL or empty
    IF @CUString IS NULL OR @CUString = ''
        RETURN 0;
    
    -- Remove common prefixes/suffixes
    SET @CleanString = @CUString;
    
    -- Remove 'CU' prefix
    SET @CleanString = REPLACE(@CleanString, 'CU', '');
    
    -- Remove '+GDR' suffix (the cause of the error!)
    SET @CleanString = REPLACE(@CleanString, '+GDR', '');
    
    -- Remove 'SP' prefix (for service packs)
    SET @CleanString = REPLACE(@CleanString, 'SP', '');
    
    -- Remove any remaining non-numeric characters
    -- Keep only digits
    DECLARE @i INT = 1;
    DECLARE @NumericOnly VARCHAR(50) = '';
    
    WHILE @i <= LEN(@CleanString)
    BEGIN
        IF SUBSTRING(@CleanString, @i, 1) LIKE '[0-9]'
            SET @NumericOnly = @NumericOnly + SUBSTRING(@CleanString, @i, 1);
        ELSE IF @NumericOnly <> ''
            BREAK; -- Stop at first non-numeric after we have digits
        SET @i = @i + 1;
    END
    
    -- Convert to INT if we have a valid number
    IF @NumericOnly <> '' AND ISNUMERIC(@NumericOnly) = 1
        SET @Result = CAST(@NumericOnly AS INT);
    
    RETURN @Result;
END

GO

-- [dbo].[fn_GetSQLVersionInfo]

CREATE FUNCTION dbo.fn_GetSQLVersionInfo(@SQLVersion VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CASE 
            WHEN @SQLVersion LIKE '17.%' THEN 'SQL Server 2025'
            WHEN @SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
            WHEN @SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
            WHEN @SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
            WHEN @SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
            WHEN @SQLVersion LIKE '12.%' THEN 'SQL Server 2014'
            WHEN @SQLVersion LIKE '11.%' THEN 'SQL Server 2012'
            WHEN @SQLVersion LIKE '10.%' THEN 'SQL Server 2008'
            ELSE 'Unknown'
        END AS VersionName,
        CASE 
            WHEN @SQLVersion LIKE '17.%' THEN 17
            WHEN @SQLVersion LIKE '16.%' THEN 16
            WHEN @SQLVersion LIKE '15.%' THEN 15
            WHEN @SQLVersion LIKE '14.%' THEN 14
            WHEN @SQLVersion LIKE '13.%' THEN 13
            WHEN @SQLVersion LIKE '12.%' THEN 12
            WHEN @SQLVersion LIKE '11.%' THEN 11
            WHEN @SQLVersion LIKE '10.%' THEN 10
            ELSE 0
        END AS MajorVersion,
        CASE 
            WHEN @SQLVersion LIKE '17.%' THEN CAST('2030-01-14' AS DATE)
            WHEN @SQLVersion LIKE '16.%' THEN CAST('2027-11-11' AS DATE)
            WHEN @SQLVersion LIKE '15.%' THEN CAST('2030-01-08' AS DATE)
            WHEN @SQLVersion LIKE '14.%' THEN CAST('2027-10-12' AS DATE)
            WHEN @SQLVersion LIKE '13.%' THEN CAST('2026-07-14' AS DATE)
            WHEN @SQLVersion LIKE '12.%' THEN CAST('2024-07-09' AS DATE)
            WHEN @SQLVersion LIKE '11.%' THEN CAST('2022-07-12' AS DATE)
            ELSE NULL
        END AS EndOfSupport,
        CASE 
            WHEN @SQLVersion LIKE '11.%' OR @SQLVersion LIKE '12.%' THEN 0 
            ELSE 1 
        END AS IsSupported,
        CASE 
            WHEN @SQLVersion LIKE '11.%' OR @SQLVersion LIKE '12.%' THEN 'End of Life'
            WHEN @SQLVersion LIKE '13.%' OR @SQLVersion LIKE '14.%' THEN 'Extended Support'
            ELSE 'Mainstream Support'
        END AS SupportStatus
);

GO

-- [Patching].[fn_GetConfig]

-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
-- SECTION 9: HELPER FUNCTION - GET CONFIG
-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
CREATE   FUNCTION Patching.fn_GetConfig(@Key NVARCHAR(100))
RETURNS NVARCHAR(500)
AS
BEGIN
    DECLARE @Value NVARCHAR(500);
    SELECT @Value = ConfigValue FROM Patching.Configuration WHERE ConfigKey = @Key;
    RETURN @Value;
END;

GO

-- [Patching].[fn_GetCUFromBuild]

-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
-- SECTION 8: HELPER FUNCTION - GET CU FROM BUILD
-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
CREATE   FUNCTION Patching.fn_GetCUFromBuild(@Build NVARCHAR(50))
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @CU NVARCHAR(20);
    SET @CU = CASE 
        WHEN @Build LIKE '16.0.4225%' THEN 'CU22'
        WHEN @Build LIKE '16.0.4222%' THEN 'CU21'
        WHEN @Build LIKE '16.0.4215%' THEN 'CU20'
        WHEN @Build LIKE '16.0.4210%' THEN 'CU19'
        WHEN @Build LIKE '16.0.4205%' THEN 'CU18'
        WHEN @Build LIKE '16.0.1%' THEN 'RTM'
        WHEN @Build LIKE '15.0.4455%' THEN 'CU29-GDR'
        WHEN @Build LIKE '15.0.4430%' THEN 'CU32'
        WHEN @Build LIKE '15.0.2%' THEN 'RTM'
        WHEN @Build LIKE '13.0.6%' THEN 'SP3'
        ELSE 'Unknown'
    END;
    RETURN @CU;
END;

GO

-- [Patching].[fn_GetNonProdPatchDate]

CREATE FUNCTION Patching.fn_GetNonProdPatchDate(@PatchTuesday DATE)
RETURNS DATE
AS
BEGIN
    -- Thursday of Patch Tuesday week (2 days after Tuesday)
    RETURN DATEADD(DAY, 2, @PatchTuesday);
END

GO

-- [Patching].[fn_GetPatchTuesday]

CREATE FUNCTION Patching.fn_GetPatchTuesday(@Month DATE)
RETURNS DATE
AS
BEGIN
    -- Patch Tuesday is the 2nd Tuesday of the month
    DECLARE @FirstDay DATE = DATEFROMPARTS(YEAR(@Month), MONTH(@Month), 1);
    DECLARE @FirstTuesday DATE;
    DECLARE @PatchTuesday DATE;
    
    -- Find first Tuesday
    SET @FirstTuesday = DATEADD(DAY, (10 - DATEPART(WEEKDAY, @FirstDay)) % 7, @FirstDay);
    
    -- Second Tuesday
    SET @PatchTuesday = DATEADD(DAY, 7, @FirstTuesday);
    
    RETURN @PatchTuesday;
END

GO

-- [Patching].[fn_GetProdPatchDate]

CREATE FUNCTION Patching.fn_GetProdPatchDate(@NonProdPatchDate DATE)
RETURNS DATE
AS
BEGIN
    -- 7 days after Non-Prod Thursday
    RETURN DATEADD(DAY, 7, @NonProdPatchDate);
END

GO

-- [playbook].[fn_CanExecuteOnServer]

-- =============================================================================
-- FUNCTION: playbook.fn_CanExecuteOnServer
-- Checks if playbook can run on server based on CMS group restrictions
-- =============================================================================
CREATE   FUNCTION playbook.fn_CanExecuteOnServer(
    @PlaybookID INT,
    @ServerName NVARCHAR(128)
)
RETURNS BIT
AS
BEGIN
    DECLARE @CanExecute BIT = 1;
    DECLARE @AllowedGroups NVARCHAR(500);
    DECLARE @ServerCMSGroup NVARCHAR(200);
    DECLARE @OverallRisk INT;
    
    -- Get playbook restrictions
    SELECT @AllowedGroups = AllowedCMSGroups, @OverallRisk = OverallRiskLevel
    FROM playbook.Playbooks WHERE PlaybookID = @PlaybookID;
    
    -- Get server's CMS group
    SET @ServerCMSGroup = correlation.fn_GetCMSGroup(@ServerName);
    
    -- If playbook has group restrictions, check them
    IF @AllowedGroups IS NOT NULL
    BEGIN
        SET @CanExecute = 0;
        -- Check if server's group matches any allowed pattern
        SELECT @CanExecute = 1
        FROM STRING_SPLIT(@AllowedGroups, ',')
        WHERE @ServerCMSGroup LIKE LTRIM(RTRIM(value));
    END
    
    -- Also check risk level restrictions
    IF @CanExecute = 1 AND @OverallRisk >= 4  -- High or Critical risk
    BEGIN
        DECLARE @RiskAllowedGroups NVARCHAR(500);
        SELECT @RiskAllowedGroups = AllowedCMSGroups 
        FROM playbook.RiskLevels WHERE RiskLevelID = @OverallRisk;
        
        IF @RiskAllowedGroups IS NOT NULL
        BEGIN
            SET @CanExecute = 0;
            SELECT @CanExecute = 1
            FROM STRING_SPLIT(@RiskAllowedGroups, ',')
            WHERE @ServerCMSGroup LIKE LTRIM(RTRIM(value));
        END
    END
    
    RETURN @CanExecute;
END

GO

