sqlinsight-pro/tests/SQLInsight.IntegrationTests/ServerWebTests.cs

using System.Net;
using System.Security.Cryptography;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using SQLInsight.Core.Configuration;
using SQLInsight.Core.Findings;
using SQLInsight.Core.Servers;
using SQLInsight.Core.SyntheticChecks;
using SQLInsight.Repository;
using SQLInsight.Rules.Engine;

namespace SQLInsight.IntegrationTests;

/// <summary>Windows-servere and Syntetiske tjek in SQLInsight Web: the menu, the pages, the forms, the grant script.</summary>
[Collection(SqlServerTestGroup.Name)]
[Trait("Category", "Integration")]
public sealed partial class ServerWebTests(SqlServerFixture sql) : IAsyncLifetime
{
    private readonly string _database = "SQLInsightSrvWeb_" + Guid.NewGuid().ToString("N")[..8];
    private readonly RSA _agentKey = RSA.Create(3072);
    private WebApplicationFactory<Program> _factory = null!;
    private InsightRepository _repository = null!;

    [GeneratedRegex("name=\"__RequestVerificationToken\"[^>]*value=\"([^\"]+)\"")]
    private static partial Regex AntiforgeryField();

    public async Task InitializeAsync()
    {
        await sql.ExecuteAsSaAsync($"CREATE DATABASE [{_database}];");
        _repository = new InsightRepository(sql.DatabaseConnectionString(_database));
        await _repository.MigrateAsync();
        await _repository.PublishAgentKeyAsync(new AgentKeyRow
        {
            KeyId = "agent-test", PublicKey = _agentKey.ExportSubjectPublicKeyInfo(), MachineName = "AGENT01", ServiceAccount = @"NT SERVICE\SQLInsight Agent",
            Domain = "FIRMA", CreatedAt = DateTime.UtcNow, LastSeenAt = DateTime.UtcNow,
        });
        _factory = new WebApplicationFactory<Program>().WithWebHostBuilder(b =>
        {
            b.UseEnvironment("Development");
            b.UseSetting("SQLInsight:Repository:ConnectionString", sql.DatabaseConnectionString(_database));
            b.UseSetting("SQLInsight:Web:Authentication", "None");
        });
    }

    public async Task DisposeAsync()
    {
        _agentKey.Dispose();
        await _factory.DisposeAsync();
        await sql.ExecuteAsSaAsync($"ALTER DATABASE [{_database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{_database}];");
    }

    private HttpClient Client() => _factory.CreateClient(new WebApplicationFactoryClientOptions { AllowAutoRedirect = false });

    private static async Task<HttpResponseMessage> PostAsync(HttpClient client, string page, string action, Dictionary<string, string> fields)
    {
        var html = await client.GetStringAsync(page);
        var values = new List<KeyValuePair<string, string>> { new("__RequestVerificationToken", WebUtility.HtmlDecode(AntiforgeryField().Match(html).Groups[1].Value)) };
        values.AddRange(fields);
        using var content = new FormUrlEncodedContent(values);
        return await client.PostAsync(action, content);
    }

    private static int IdFrom(HttpResponseMessage response, string page) =>
        int.Parse(Regex.Match(response.Headers.Location!.OriginalString, page + @"/(\d+)").Groups[1].Value, System.Globalization.CultureInfo.InvariantCulture);

    [Fact]
    public async Task The_menu_has_a_servers_group_that_opens_on_its_pages()
    {
        var page = await Client().GetStringAsync("/servere");
        var checks = await Client().GetStringAsync("/syntetiske-tjek");

        Assert.Contains("<span>Servere</span>", page, StringComparison.Ordinal);
        Assert.Contains("href=\"/servere\"", page, StringComparison.Ordinal);
        Assert.Contains("href=\"/syntetiske-tjek\"", page, StringComparison.Ordinal);
        Assert.Matches("<details class=\"nav-group\" open[^>]*data-nav-group=\"servere\"", page);
        Assert.Matches("<details class=\"nav-group\" open[^>]*data-nav-group=\"servere\"", checks);
        Assert.Contains("Der er ingen Windows-servere endnu", page, StringComparison.Ordinal);
    }

    [Fact]
    public async Task Admin_adds_a_server_sees_its_data_findings_rights_and_the_grant_script()
    {
        var client = Client();
        var posted = await PostAsync(client, "/servere", "/servere/tilfoej", new() { ["host"] = "AOS01.firma.local", ["role"] = "Application", ["watched"] = "AOS60$01, W3SVC" });

        Assert.Equal(HttpStatusCode.Redirect, posted.StatusCode);
        var id = IdFrom(posted, "/servere");
        var row = (await _repository.GetWindowsServerAsync(id))!;
        Assert.Equal(("AOS01", "aos01.firma.local", ServerRoles.Application), (row.Name, row.Host, row.Role));
        Assert.Equal(["AOS60$01", "W3SVC"], row.Watched);
        Assert.Equal(AgentRequestStatus.Pending, (await _repository.GetLatestServerRequestAsync(ServerRequestKind.ServerTest, id))!.Status);

        // What the agent would have collected.
        var now = DateTimeOffset.UtcNow;
        await _repository.RecordServerContactAsync(id, new ServerOsInfo { Caption = "Microsoft Windows Server 2019 Standard", LastBootUpTime = now.AddDays(-3), TotalMemoryMb = 16384 },
            "WS-Man", [new ServerAreaStatus(ServerArea.Events, false, "Adgang nægtet til hændelseslog: mangler Event Log Readers.", ServerArea.Right(ServerArea.Events))], now);
        await _repository.SaveServerPerformanceAsync(id, new ServerPerformanceSample(now.AddMinutes(-2), 35, 6000, 16384, 1, 40));
        await _repository.SaveServerPerformanceAsync(id, new ServerPerformanceSample(now.AddMinutes(-1), 45, 5900, 16384, 1, 40));
        await _repository.SaveServerSnapshotAsync(id, ServerArea.Disks, new ServerDiskSnapshot
        {
            CapturedAt = now, Disks = [new ServerDisk(@"D:\", "Data", "NTFS", 500, 40)], Forecasts = [new DiskForecast(@"D:\", 12, 3.3, 300, 14, 0.9)],
        }, now);
        await _repository.SaveServerSnapshotAsync(id, ServerArea.Services, new ServerServiceSnapshot
        {
            CapturedAt = now, Services = [new ServerService("AOS60$01", "Microsoft Dynamics AX Object Server 6.0$01", "Stopped", "Auto", ExitCode: 1067)],
        }, now);
        await _repository.SaveScopedRuleResultsAsync(FindingScope.Server, id,
        [
            new RuleResult
            {
                RuleId = "SQLI-WIN-006", RuleTitle = "Et drev bliver fuldt om få dage", Outcome = RuleOutcome.Findings,
                Findings = [new Finding { RuleId = "SQLI-WIN-006", Severity = Severity.Medium, Title = @"Disken D:\ er fuld om ca. 12 dage", Explanation = "e", Recommendation = "r", Subject = @"forecast: D:\" }],
            },
            new RuleResult { RuleId = "SQLI-WIN-009", RuleTitle = "Fejl i Windows' hændelseslog", Outcome = RuleOutcome.NotMeasurable, Reason = "Adgang nægtet til hændelseslog: mangler Event Log Readers." },
        ], now);
        var finding = Assert.Single(await _repository.GetServerFindingsAsync(FindingScope.Server, id));

        var detail = await client.GetStringAsync($"/servere/{id}");
        var list = await client.GetStringAsync("/servere");
        var findingPage = await client.GetStringAsync($"/servere/fund/{finding.FindingId}");
        var script = await client.GetAsync($"/servere/{id}/grant-windows-server.ps1");

        Assert.Contains("Microsoft Windows Server 2019 Standard", detail, StringComparison.Ordinal);
        Assert.Contains("fuld om ca. 12 dage", detail, StringComparison.Ordinal);
        Assert.Contains("Microsoft Dynamics AX Object Server 6.0$01", detail, StringComparison.Ordinal);
        Assert.Contains("Ikke målbar", detail, StringComparison.Ordinal);
        Assert.Contains("mangler Event Log Readers", detail, StringComparison.Ordinal);
        Assert.Contains(@"[string] $Account = &#x27;FIRMA\AGENT01$&#x27;", detail, StringComparison.Ordinal);
        Assert.Contains("aldrig være lokal administrator", detail, StringComparison.Ordinal);
        Assert.Contains("Delvist målbar", list, StringComparison.Ordinal);
        Assert.Contains(@"D:\ fuld om ca. 12 dage", list, StringComparison.Ordinal);
        Assert.Contains(@"Disken D:\ er fuld om ca. 12 dage", findingPage, StringComparison.Ordinal);
        Assert.Equal("text/plain; charset=utf-8", script.Content.Headers.ContentType!.ToString());
        var bytes = await script.Content.ReadAsByteArrayAsync();
        Assert.Equal([0xEF, 0xBB, 0xBF], bytes[..3]);
        Assert.Contains(@"$Account = 'FIRMA\AGENT01$'", System.Text.Encoding.UTF8.GetString(bytes), StringComparison.Ordinal);

        var saved = await PostAsync(client, $"/servere/{id}", $"/servere/{id}/indstillinger", new() { ["role"] = "File", ["watched"] = "LanmanServer", ["ignored"] = "MyBatch*" });
        Assert.Equal(HttpStatusCode.Redirect, saved.StatusCode);
        Assert.Equal((ServerRoles.File, "LanmanServer", "MyBatch*"), ((await _repository.GetWindowsServerAsync(id))!.Role, (await _repository.GetWindowsServerAsync(id))!.Watched[0],
            (await _repository.GetWindowsServerAsync(id))!.Ignored[0]));
        // The test asked for when the server was added still waits for the agent: it is reused, not queued again.
        await PostAsync(client, $"/servere/{id}", $"/servere/{id}/test", []);
        Assert.Equal(1, (await _repository.GetLatestServerRequestAsync(ServerRequestKind.ServerTest, id))!.RequestId);
        await _repository.ClaimAgentRequestAsync(DateTimeOffset.UtcNow);
        await PostAsync(client, $"/servere/{id}", $"/servere/{id}/test", []);
        Assert.Equal(2, (await _repository.GetLatestServerRequestAsync(ServerRequestKind.ServerTest, id))!.RequestId);
        await PostAsync(client, $"/servere/{id}", $"/servere/{id}/fjern", []);
        Assert.Null(await _repository.GetWindowsServerAsync(id));
    }

    [Fact]
    public async Task Bad_input_is_refused_with_a_reason_and_a_sql_host_cannot_be_removed()
    {
        var client = Client();
        var bad = await PostAsync(client, "/servere", "/servere/tilfoej", new() { ["host"] = @"sql01\PROD" });
        Assert.Contains("uden instansnavn", Uri.UnescapeDataString(bad.Headers.Location!.OriginalString), StringComparison.Ordinal);

        await _repository.SyncSqlHostServersAsync([("sql01.firma.local", "SQL01")], DateTimeOffset.UtcNow);
        var host = Assert.Single(await _repository.GetWindowsServersAsync());
        var removed = await PostAsync(client, $"/servere/{host.ServerId}", $"/servere/{host.ServerId}/fjern", []);
        Assert.Contains("SQL Server-host", Uri.UnescapeDataString(removed.Headers.Location!.OriginalString), StringComparison.Ordinal);
        Assert.NotNull(await _repository.GetWindowsServerAsync(host.ServerId));
        await PostAsync(client, $"/servere/{host.ServerId}", $"/servere/{host.ServerId}/pause", new() { ["paused"] = "true" });
        Assert.True((await _repository.GetWindowsServerAsync(host.ServerId))!.Paused);
    }

    [Fact]
    public async Task Admin_adds_checks_of_each_kind_and_a_sql_password_is_sealed_for_the_agent()
    {
        var client = Client();
        var server = await _repository.AddWindowsServerAsync(new NewWindowsServer("AOS01", "aos01", ServerRoles.Application, [], []), null, DateTimeOffset.UtcNow);
        var ax = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej", new()
        {
            ["name"] = "AOS01 AX-logon", ["kind"] = "AxLogon", ["serverId"] = server.ToString(System.Globalization.CultureInfo.InvariantCulture), ["serviceName"] = "AOS60$01",
            ["host"] = "aos01", ["company"] = "DAT", ["businessConnectorLogon"] = "true", ["intervalMinutes"] = "5", ["url"] = "https://ignoreres/",
        });
        var sqlCheck = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej", new()
        {
            ["name"] = "SQL01 AX-login", ["kind"] = "Sql", ["server"] = @"sql01\AX", ["database"] = "AX2012", ["authentication"] = "SqlLogin", ["userName"] = "axapp",
            ["password"] = "Hemmelig-AX-1!", ["query"] = "SELECT COUNT(*) FROM dbo.DATAAREA", ["intervalMinutes"] = "10",
        });
        var http = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej", new()
        {
            ["name"] = "Enterprise Portal", ["kind"] = "Http", ["url"] = "https://ep.firma.local/", ["expectedStatus"] = "200, 401", ["warnMilliseconds"] = "2000",
        });
        var writes = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej", new()
        {
            ["name"] = "Skriver", ["kind"] = "Sql", ["server"] = "sql01", ["query"] = "UPDATE dbo.CustTable SET Name = 'x'",
        });

        var axId = IdFrom(ax, "/syntetiske-tjek");
        var axRow = (await _repository.GetSyntheticCheckAsync(axId))!;
        Assert.Equal((SyntheticCheckKind.AxLogon, (int?)server, "AOS60$01", "DAT", (Uri?)null, 300), (axRow.Kind, axRow.ServerId, axRow.Settings.ServiceName, axRow.Settings.Company,
            axRow.Settings.Url, axRow.IntervalSeconds));
        Assert.Equal(SyntheticCheckLimits.DefaultAxTimeoutSeconds, axRow.TimeoutSeconds);
        Assert.Equal(AgentRequestStatus.Pending, (await _repository.GetLatestServerRequestAsync(ServerRequestKind.SyntheticCheckRun, axId))!.Status);
        var sqlRow = (await _repository.GetSyntheticCheckAsync(IdFrom(sqlCheck, "/syntetiske-tjek")))!;
        Assert.True(sqlRow.HasSecret);
        Assert.Equal("Hemmelig-AX-1!", CredentialEnvelope.Open(sqlRow.SecretCipher!, "agent-test", _agentKey, sqlRow.Settings.SecretContext()));
        Assert.DoesNotContain("Hemmelig-AX-1!", sqlRow.SettingsJson, StringComparison.Ordinal);
        var httpRow = (await _repository.GetSyntheticCheckAsync(IdFrom(http, "/syntetiske-tjek")))!;
        Assert.Equal([200, 401], httpRow.Settings.ExpectedStatus);
        Assert.Contains("SELECT", Uri.UnescapeDataString(writes.Headers.Location!.OriginalString), StringComparison.Ordinal);

        await _repository.RecordSyntheticCheckResultAsync(axId, new SyntheticCheckResult
        {
            At = DateTimeOffset.UtcNow, Success = true, DurationMs = 12, Partial = true,
            Message = "AOS-porten svarer (12 ms). Kun porten er testet: .NET Business Connector (AX 2009/2012) er ikke installeret på agentens server, så et AX-logon kan ikke prøves.",
        });
        var page = await client.GetStringAsync($"/syntetiske-tjek/{axId}");
        var list = await client.GetStringAsync("/syntetiske-tjek");
        var edit = await client.GetStringAsync($"/syntetiske-tjek/{sqlRow.CheckId}");
        Assert.Contains("Kun porten er testet", page, StringComparison.Ordinal);
        Assert.Contains("AOS01 AX-logon", list, StringComparison.Ordinal);
        Assert.Contains("aos01:2712", list, StringComparison.Ordinal);
        Assert.DoesNotContain("Hemmelig-AX-1!", edit, StringComparison.Ordinal);
        Assert.Contains("tom: behold den gemte", edit, StringComparison.Ordinal);

        // Moving the SQL check to another server needs the password again.
        var moved = await PostAsync(client, $"/syntetiske-tjek/{sqlRow.CheckId}", $"/syntetiske-tjek/{sqlRow.CheckId}/ret", new()
        {
            ["name"] = "SQL01 AX-login", ["kind"] = "Sql", ["server"] = "evil.example", ["authentication"] = "SqlLogin", ["userName"] = "axapp",
        });
        Assert.Contains("Indtast adgangskoden igen", Uri.UnescapeDataString(moved.Headers.Location!.OriginalString), StringComparison.Ordinal);
        // "Kør nu" while the first run still waits for the agent shows that run; once taken, a new one is queued.
        var firstRun = (await _repository.GetLatestServerRequestAsync(ServerRequestKind.SyntheticCheckRun, axId))!.RequestId;
        var again = await PostAsync(client, $"/syntetiske-tjek/{axId}", $"/syntetiske-tjek/{axId}/koer", []);
        Assert.EndsWith("koersel=" + firstRun.ToString(System.Globalization.CultureInfo.InvariantCulture), again.Headers.Location!.OriginalString, StringComparison.Ordinal);
        while (await _repository.ClaimAgentRequestAsync(DateTimeOffset.UtcNow) is not null)
        {
        }

        var run = await PostAsync(client, $"/syntetiske-tjek/{axId}", $"/syntetiske-tjek/{axId}/koer", []);
        Assert.Contains("koersel=", run.Headers.Location!.OriginalString, StringComparison.Ordinal);
        Assert.True((await _repository.GetLatestServerRequestAsync(ServerRequestKind.SyntheticCheckRun, axId))!.RequestId > firstRun);
        await PostAsync(client, $"/syntetiske-tjek/{axId}", $"/syntetiske-tjek/{axId}/fjern", []);
        Assert.Null(await _repository.GetSyntheticCheckAsync(axId));
    }

    [Fact]
    public async Task Bad_numbers_are_refused_a_paused_check_is_not_run_and_seconds_are_kept()
    {
        var client = Client();
        var port = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej", new() { ["name"] = "Port", ["kind"] = "Tcp", ["host"] = "aos01", ["port"] = "27x12" });
        var interval = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej",
            new() { ["name"] = "Port", ["kind"] = "Tcp", ["host"] = "aos01", ["port"] = "2712", ["intervalMinutes"] = "fem" });
        var huge = await PostAsync(client, "/syntetiske-tjek", "/syntetiske-tjek/tilfoej",
            new() { ["name"] = "Port", ["kind"] = "Tcp", ["host"] = "aos01", ["port"] = "2712", ["intervalMinutes"] = "2000000000" });

        Assert.Contains("Porten skal være et tal.", Uri.UnescapeDataString(port.Headers.Location!.OriginalString), StringComparison.Ordinal);
        Assert.Contains("helt antal minutter", Uri.UnescapeDataString(interval.Headers.Location!.OriginalString), StringComparison.Ordinal);
        Assert.Contains("mellem 1 minut og 24 timer", Uri.UnescapeDataString(huge.Headers.Location!.OriginalString), StringComparison.Ordinal);
        Assert.Empty(await _repository.GetSyntheticChecksAsync());

        // Set to 90 seconds through the API: the form keeps the seconds, the page says so.
        var id = await _repository.AddSyntheticCheckAsync(new SyntheticCheckInput("AOS01 port", SyntheticCheckKind.Tcp, null,
            new SyntheticCheckSettings { Host = "aos01", Port = 2712 }, null, null, 90, 15), null, DateTimeOffset.UtcNow);
        var page = await client.GetStringAsync($"/syntetiske-tjek/{id}");
        Assert.Contains("hvert 90. sekund", page, StringComparison.Ordinal);
        Assert.Contains("name=\"intervalSeconds\"", page, StringComparison.Ordinal);
        await PostAsync(client, $"/syntetiske-tjek/{id}", $"/syntetiske-tjek/{id}/ret",
            new() { ["name"] = "AOS01 port", ["kind"] = "Tcp", ["host"] = "aos01", ["port"] = "2712", ["intervalSeconds"] = "90" });
        Assert.Equal(90, (await _repository.GetSyntheticCheckAsync(id))!.IntervalSeconds);

        await _repository.SetSyntheticCheckPausedAsync(id, true, null, DateTimeOffset.UtcNow);
        var paused = await PostAsync(client, $"/syntetiske-tjek/{id}", $"/syntetiske-tjek/{id}/koer", []);
        Assert.Contains("pause", Uri.UnescapeDataString(paused.Headers.Location!.OriginalString), StringComparison.Ordinal);
        Assert.Null(await _repository.GetLatestServerRequestAsync(ServerRequestKind.SyntheticCheckRun, id));
        Assert.DoesNotContain("Kør nu</button>", await client.GetStringAsync($"/syntetiske-tjek/{id}"), StringComparison.Ordinal);
    }

    [Fact]
    public async Task Paused_servers_findings_are_not_on_the_overview_and_a_removed_servers_checks_stay()
    {
        var client = Client();
        var now = DateTimeOffset.UtcNow;
        var server = await _repository.AddWindowsServerAsync(new NewWindowsServer("FIL01", "fil01", ServerRoles.File, [], []), null, now);
        var check = await _repository.AddSyntheticCheckAsync(new SyntheticCheckInput("FIL01 SMB", SyntheticCheckKind.Tcp, server,
            new SyntheticCheckSettings { Host = "fil01", Port = 445, ServiceName = "LanmanServer" }, null, null, 300, 15), null, now);
        await _repository.SaveScopedRuleResultsAsync(FindingScope.Server, server,
        [
            new RuleResult
            {
                RuleId = "SQLI-WIN-005", RuleTitle = "Disk", Outcome = RuleOutcome.Findings,
                Findings = [new Finding { RuleId = "SQLI-WIN-005", Severity = Severity.High, Title = "Disk E: is nearly full", Explanation = "e", Recommendation = "r", Subject = "disk: E:" }],
            },
        ], now);
        Assert.Contains("Disk E: is nearly full", await client.GetStringAsync("/servere"), StringComparison.Ordinal);

        await PostAsync(client, $"/servere/{server}", $"/servere/{server}/pause", new() { ["paused"] = "true" });
        var list = await client.GetStringAsync("/servere");
        Assert.DoesNotContain("Disk E: is nearly full", list, StringComparison.Ordinal);
        Assert.Contains("Servere og tjek sat på pause er ikke med: FIL01", list, StringComparison.Ordinal);
        Assert.Contains("Disk E: is nearly full", await client.GetStringAsync($"/servere/{server}"), StringComparison.Ordinal);

        await PostAsync(client, $"/servere/{server}", $"/servere/{server}/fjern", []);
        var row = (await _repository.GetSyntheticCheckAsync(check))!;
        Assert.Equal((null, null), (row.ServerId, row.Settings.ServiceName));
        var saved = await PostAsync(client, $"/syntetiske-tjek/{check}", $"/syntetiske-tjek/{check}/ret",
            new() { ["name"] = "FIL01 SMB", ["kind"] = "Tcp", ["host"] = "fil01", ["port"] = "445", ["intervalMinutes"] = "5" });
        Assert.Contains("gemt=1", saved.Headers.Location!.OriginalString, StringComparison.Ordinal);
    }
}