---
policy: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more
logic: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more/prod.logic.yaml
executionTime: 2025-04-24T23:45:47.778491699Z
rows:
  - id: test1
    match: true
    status:
      expected: DISAPPEARED
      actual: DISAPPEARED
    conditionIndex:
      expected: 99
      actual: 99
    conditionText:
      expected: isDisappeared(CA10__disappearanceTime__c)
      actual: isDisappeared(CA10__disappearanceTime__c)
    runtimeError: {}
  - id: test2
    match: true
    status:
      expected: INCOMPLIANT
      actual: INCOMPLIANT
    conditionIndex:
      expected: 199
      actual: 199
    conditionText:
      expected: extract('CA10__auditing__c') == 'Disabled'
      actual: extract('CA10__auditing__c') == 'Disabled'
    runtimeError: {}
  - id: test3
    match: true
    status:
      expected: INCOMPLIANT
      actual: INCOMPLIANT
    conditionIndex:
      expected: 299
      actual: 299
    conditionText:
      expected: extract('CA10__auditingRetentionDays__c') < number(90.0) && extract('CA10__auditingRetentionDays__c')
        > number(0.0)
      actual: extract('CA10__auditingRetentionDays__c') < number(90.0) && extract('CA10__auditingRetentionDays__c')
        > number(0.0)
    runtimeError: {}
  - id: test4
    match: true
    status:
      expected: COMPLIANT
      actual: COMPLIANT
    conditionIndex:
      expected: 399
      actual: 399
    conditionText:
      expected: extract('CA10__auditingRetentionDays__c') >= number(90.0)
      actual: extract('CA10__auditingRetentionDays__c') >= number(90.0)
    runtimeError: {}
  - id: test5
    match: true
    status:
      expected: COMPLIANT
      actual: COMPLIANT
    conditionIndex:
      expected: 499
      actual: 499
    conditionText:
      expected: extract('CA10__auditingRetentionDays__c') == number(0.0)
      actual: extract('CA10__auditingRetentionDays__c') == number(0.0)
    runtimeError: {}
  - id: test6
    match: true
    status:
      expected: UNDETERMINED
      actual: UNDETERMINED
    conditionIndex:
      expected: 101
      actual: 101
    conditionText:
      expected: CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()
      actual: CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()
    runtimeError: {}
usedFiles:
  - path: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more/policy.yaml
    md5Hash: BE9BE65706327F3FF9B0458E1F9AC8ED
    content: |
      ---
      names:
        full: Azure SQL Server Auditing Retention is less than 90 days
        contextual: Server Auditing Retention is less than 90 days
      description: SQL Server Audit Retention should be configured to be greater than 90
        days. Audit Logs can be used to check for anomalies and give insight into suspected
        breaches or misuse of information and access.
      type: COMPLIANCE_POLICY
      categories:
        - RELIABILITY
        - SECURITY
      frameworkMappings:
        - "/frameworks/cis-azure-v2.1.0/04/01/06"
        - "/frameworks/cis-azure-v3.0.0/05/01/06"
        - "/frameworks/cloudaware/logging-and-monitoring/logging-and-monitoring-configuration"
      similarPolicies:
        internal:
          - dec-x-89d5ed7a
        cloudConformity:
          - url: https://www.trendmicro.com/cloudoneconformity/knowledge-base/azure/Sql/auditing-retention.html
            name: SQL Auditing Retention
  - path: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more/prod.logic.yaml
    md5Hash: 9710ECB8D088E6D968BFEE6872444CDD
    content: "---\ninputType: \"CA10__CaAzureSqlServer__c\"\ntestData:\n  - file:\
      \ \"test-data.json\"\nimportExtracts:\n  - file: \"/types/CA10__CaAzureSqlServer__c/object.extracts.yaml\"\
      \nconditions:\n  - status: \"INCOMPLIANT\"\n    currentStateMessage: \"SQL Server\
      \ auditing is disabled.\"\n    remediationMessage: \"Consider enabling SQL Server\
      \ auditing.\"\n    check:\n      IS_EQUAL:\n        left:\n          EXTRACT:\
      \ \"CA10__auditing__c\"\n        right:\n          TEXT: \"Disabled\"\n  - status:\
      \ \"INCOMPLIANT\"\n    currentStateMessage: \"SQL Server auditing retention\
      \ is less than 90 days.\"\n    remediationMessage: \"Consider setting SQL Server\
      \ auditing retention to be greater than 90 days or 0 for unlimited retention.\"\
      \n    check:\n      AND:\n        args:\n          - LESS_THAN:\n          \
      \    left: \n                EXTRACT: \"CA10__auditingRetentionDays__c\"\n \
      \             right:\n                NUMBER: 90.0\n          - GREATER_THAN:\n\
      \              left: \n                EXTRACT: \"CA10__auditingRetentionDays__c\"\
      \n              right:\n                NUMBER: 0.0\n  - status: \"COMPLIANT\"\
      \n    currentStateMessage: \"SQL Server auditing retention is 90 days or more.\"\
      \n    check:\n      GREATER_THAN_EQUAL:\n        left: \n          EXTRACT:\
      \ \"CA10__auditingRetentionDays__c\"\n        right:\n          NUMBER: 90.0\n\
      \  - status: \"COMPLIANT\"\n    currentStateMessage: \"SQL Server auditing retention\
      \ is unlimited.\"\n    check:\n      IS_EQUAL:\n        left: \n          EXTRACT:\
      \ \"CA10__auditingRetentionDays__c\"\n        right:\n          NUMBER: 0.0\n\
      otherwise:\n  status: \"UNDETERMINED\"\n  currentStateMessage: \"Unexpected\
      \ value in the field.\""
  - path: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more/test-data.json
    md5Hash: 1633EEA3B5D89E5888050400080BFD71
    content: |-
      [
          {
              "expectedResult": {
                  "status": "DISAPPEARED",
                  "conditionIndex": "99",
                  "conditionText": "isDisappeared(CA10__disappearanceTime__c)",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test1",
              "CA10__disappearanceTime__c": "2024-06-07T13:58:24Z",
              "CA10__auditing__c": "Enabled",
              "CA10__auditingRetentionDays__c": 0
          },
          {
              "expectedResult": {
                  "status": "INCOMPLIANT",
                  "conditionIndex": "199",
                  "conditionText": "extract('CA10__auditing__c') == 'Disabled'",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test2",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Disabled",
              "CA10__auditingRetentionDays__c": 0
          },
          {
              "expectedResult": {
                  "status": "INCOMPLIANT",
                  "conditionIndex": "299",
                  "conditionText": "extract('CA10__auditingRetentionDays__c') < number(90.0) && extract('CA10__auditingRetentionDays__c') > number(0.0)",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test3",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Enabled",
              "CA10__auditingRetentionDays__c": 1
          },
          {
              "expectedResult": {
                  "status": "COMPLIANT",
                  "conditionIndex": "399",
                  "conditionText": "extract('CA10__auditingRetentionDays__c') >= number(90.0)",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test4",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Enabled",
              "CA10__auditingRetentionDays__c": 180
          },
          {
              "expectedResult": {
                  "status": "COMPLIANT",
                  "conditionIndex": "499",
                  "conditionText": "extract('CA10__auditingRetentionDays__c') == number(0.0)",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test5",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Enabled",
              "CA10__auditingRetentionDays__c": 0
          },
          {
              "expectedResult": {
                  "status": "UNDETERMINED",
                  "conditionIndex": "101",
                  "conditionText": "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-13T04:17:29Z"
              },
              "Id": "test6",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "",
              "CA10__auditingRetentionDays__c": 0
          }
      ]
  - path: /types/CA10__CaAzureSqlServer__c/object.extracts.yaml
    md5Hash: E77808BBEDF31D54E009EEA59AC5EF4B
    content: "---\nextracts:\n# Values: Enabled, Disabled. Not Nullable.\n  - name:\
      \ \"CA10__auditing__c\"\n    value: \n      FIELD:\n        path: \"CA10__auditing__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__auditing__c\"\n            currentStateMessage: \"Unable to determine\
      \ Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read\"\
      \n          isEmpty: \"SQL Server auditing settings are not populated yet\"\n\
      # Number field\n  - name: \"CA10__auditingRetentionDays__c\"\n    value: \n\
      \      FIELD:\n        path: \"CA10__auditingRetentionDays__c\"\n        undeterminedIf:\n\
      \          noAccessDelegate:\n            path: \"CA10__auditingRetentionDays__c\"\
      \n            currentStateMessage: \"Unable to determine Server auditing settings.\
      \ Possible permission issue with Microsoft.Sql/servers/auditingSettings/read\"\
      \n# Values: servicemanaged, azurekeyvault. Not Nullable. \n  - name: \"CA10__encryptionProtectorKind__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__encryptionProtectorKind__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__encryptionProtectorKind__c\"\n            currentStateMessage: \"Unable\
      \ to determine Server Encryption Protectors. Possible permission issue with\
      \ Microsoft.Sql/servers/encryptionProtector/read\"\n          isEmpty: \"SQL\
      \ Server Encryption Protectors are not populated yet\"\n# Values: ServiceManaged,\
      \ AzureKeyVault. Not Nullable. \n  - name: \"CA10__encryptionProtectorServerKeyType__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__encryptionProtectorServerKeyType__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__encryptionProtectorServerKeyType__c\"\n            currentStateMessage:\
      \ \"Unable to determine Server Encryption Protectors. Possible permission issue\
      \ with Microsoft.Sql/servers/encryptionProtector/read\"\n          isEmpty:\
      \ \"SQL Server Encryption Protectors are not populated yet\"\n# Nullable. \n\
      \  - name: \"CA10__encryptionProtectorUri__c\"\n    value: \n      FIELD:\n\
      \        path: \"CA10__encryptionProtectorUri__c\"\n        undeterminedIf:\n\
      \          noAccessDelegate:\n            path: \"CA10__encryptionProtectorUri__c\"\
      \n            currentStateMessage: \"Unable to determine Server Encryption Protectors.\
      \ Possible permission issue with Microsoft.Sql/servers/encryptionProtector/read\"\
      \n# Values: ActiveDirectory, null. Nullable. \n  - name: \"CA10__activeDirectoryAdminType__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__activeDirectoryAdminType__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__activeDirectoryAdminType__c\"\n            currentStateMessage: \"Unable\
      \ to determine Server AD Administrator type. Possible permission issue with\
      \ Microsoft.Sql/servers/administrators\"\n  - name: \"CA10__firewallRulesJson__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__firewallRulesJson__c\"\n\
      \        returnType: BYTES\n        undeterminedIf:\n          noAccessDelegate:\n\
      \            path: \"CA10__firewallRulesJson__c\"\n            currentStateMessage:\
      \ \"Unable to determine Firewall Rules. Possible permission issue with Microsoft.Sql/servers/firewallRules/read\"\
      \n  - name: \"caJsonFrom__firewallRulesJson__c\"\n    value: \n      JSON_FROM:\n\
      \        arg: \n          EXTRACT: \"CA10__firewallRulesJson__c\"\n        undeterminedIf:\n\
      \          isInvalid: \"Firewall Rules JSON is invalid.\"\n# Nullable. Values:\
      \ Enabled, Disabled, Can't have no access, retrieved via Microsoft.Sql/servers\n\
      \  - name: \"CA10__publicNetworkAccess__c\"\n    value: \n      FIELD:\n   \
      \     path: \"CA10__publicNetworkAccess__c\"\n"
script: |-
  CREATE TEMP FUNCTION mock_ExpectedResult()
  RETURNS ARRAY<STRUCT<
          Id STRING,
          expectedResult STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, runtimeError STRING>
      >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "Id" : "test1",
    "expectedResult" : {
      "status" : "DISAPPEARED",
      "conditionIndex" : "99",
      "conditionText" : "isDisappeared(CA10__disappearanceTime__c)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test2",
    "expectedResult" : {
      "status" : "INCOMPLIANT",
      "conditionIndex" : "199",
      "conditionText" : "extract('CA10__auditing__c') == 'Disabled'",
      "runtimeError" : null
    }
  }, {
    "Id" : "test3",
    "expectedResult" : {
      "status" : "INCOMPLIANT",
      "conditionIndex" : "299",
      "conditionText" : "extract('CA10__auditingRetentionDays__c') < number(90.0) && extract('CA10__auditingRetentionDays__c') > number(0.0)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test4",
    "expectedResult" : {
      "status" : "COMPLIANT",
      "conditionIndex" : "399",
      "conditionText" : "extract('CA10__auditingRetentionDays__c') >= number(90.0)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test5",
    "expectedResult" : {
      "status" : "COMPLIANT",
      "conditionIndex" : "499",
      "conditionText" : "extract('CA10__auditingRetentionDays__c') == number(0.0)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test6",
    "expectedResult" : {
      "status" : "UNDETERMINED",
      "conditionIndex" : "101",
      "conditionText" : "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()",
      "runtimeError" : null
    }
  } ];
  """;

  CREATE TEMP FUNCTION mock_CA10__CaAzureSqlServer__c()
  RETURNS ARRAY<STRUCT<
      Id STRING,
      CA10__disappearanceTime__c TIMESTAMP,
      CA10__auditing__c STRING,
      CA10__auditingRetentionDays__c FLOAT64,
      context STRUCT<
          snapshotTime TIMESTAMP
      >
  >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test1",
    "CA10__disappearanceTime__c" : new Date("2024-06-07T13:58:24Z"),
    "CA10__auditing__c" : "Enabled",
    "CA10__auditingRetentionDays__c" : 0
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test2",
    "CA10__auditing__c" : "Disabled",
    "CA10__auditingRetentionDays__c" : 0
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test3",
    "CA10__auditing__c" : "Enabled",
    "CA10__auditingRetentionDays__c" : 1
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test4",
    "CA10__auditing__c" : "Enabled",
    "CA10__auditingRetentionDays__c" : 180
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test5",
    "CA10__auditing__c" : "Enabled",
    "CA10__auditingRetentionDays__c" : 0
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-13T04:17:29Z")
    },
    "Id" : "test6",
    "CA10__auditing__c" : "",
    "CA10__auditingRetentionDays__c" : 0
  } ];
  """;

  CREATE TEMP FUNCTION process_CA10__CaAzureSqlServer__c(
      obj STRUCT<
          Id STRING,
          CA10__disappearanceTime__c TIMESTAMP,
          CA10__auditing__c STRING,
          CA10__auditingRetentionDays__c FLOAT64
      >,
      snapshotTime TIMESTAMP
  )
  RETURNS STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
      var TextLib = new function () {
          this.normalize = function(arg) {
              return arg == null ? '' : arg.replace(/\s+/g, ' ').trim().toLowerCase();
          };
          this.isEmpty = function(arg) {
              return this.normalize(arg) == '';
          };
          this.isNotEmpty = function(arg) {
              return this.normalize(arg) != '';
          };
          this.equal = function(left, right) {
              return this.normalize(left) == this.normalize(right);
          };
          this.notEqual = function(left, right) {
              return this.normalize(left) != this.normalize(right);
          };
          this.startsWith = function(arg, substring) {
              return this.normalize(arg).startsWith(this.normalize(substring));
          };
          this.endsWith = function(arg, substring) {
              return this.normalize(arg).endsWith(this.normalize(substring));
          };
          this.contains = function(arg, substring) {
              return this.normalize(arg).includes(this.normalize(substring));
          };
      }();
      var IsEmptyLib = new function () {
          this.simpleIsEmpty = function(arg) {
              return arg == null;
          };
          this.simpleIsNotEmpty = function(arg) {
              return arg != null;
          };
      }();
      var references1 = [];
      // condition[0], conditionIndex:[0..99]
      references1.push('Deleted From Azure [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c);
      if (obj.CA10__disappearanceTime__c != null) {
          return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
      }
      // condition[1], conditionIndex:[100..199]
      function fieldChecked4() {
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:101", {cause: {status: 'UNDETERMINED', conditionIndex: 101, conditionText: "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:102", {cause: {status: 'UNDETERMINED', conditionIndex: 102, conditionText: "CA10__auditing__c.isEmpty()", currentStateMessage: "SQL Server auditing settings are not populated yet", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditing__c;
      }
      function extract3() { if (!this.out) { this.out = fieldChecked4(); } return this.out; };
      references1.push('Auditing [obj.CA10__auditing__c]: ' + obj.CA10__auditing__c);
      try {
          if (TextLib.equal(extract3.call(extract3), 'Disabled')) {
              return {status: 'INCOMPLIANT', conditionIndex: 199, conditionText: "extract('CA10__auditing__c') == 'Disabled'", currentStateMessage: "SQL Server auditing is disabled.", currentStateReferences: references1.join('\n'), remediation: "Consider enabling SQL Server auditing.", runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      // condition[2], conditionIndex:[200..299]
      function fieldChecked8() {
          if (IsEmptyLib.simpleIsEmpty(obj.CA10__auditingRetentionDays__c)) {
              throw new Error("UNDETERMINED condition:201", {cause: {status: 'UNDETERMINED', conditionIndex: 201, conditionText: "CA10__auditingRetentionDays__c.delegatedTo(CA10__auditingRetentionDays__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditingRetentionDays__c;
      }
      function extract7() { if (!this.out) { this.out = fieldChecked8(); } return this.out; };
      references1.push('Auditing: Retention Days [obj.CA10__auditingRetentionDays__c]: ' + obj.CA10__auditingRetentionDays__c);
      try {
          if ((extract7.call(extract7) != null && 90.0 != null && extract7.call(extract7) < 90.0) && (extract7.call(extract7) != null && 0.0 != null && extract7.call(extract7) > 0.0)) {
              return {status: 'INCOMPLIANT', conditionIndex: 299, conditionText: "extract('CA10__auditingRetentionDays__c') < number(90.0) && extract('CA10__auditingRetentionDays__c') > number(0.0)", currentStateMessage: "SQL Server auditing retention is less than 90 days.", currentStateReferences: references1.join('\n'), remediation: "Consider setting SQL Server auditing retention to be greater than 90 days or 0 for unlimited retention.", runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      // condition[3], conditionIndex:[300..399]
      function fieldChecked14() {
          if (IsEmptyLib.simpleIsEmpty(obj.CA10__auditingRetentionDays__c)) {
              throw new Error("UNDETERMINED condition:301", {cause: {status: 'UNDETERMINED', conditionIndex: 301, conditionText: "CA10__auditingRetentionDays__c.delegatedTo(CA10__auditingRetentionDays__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditingRetentionDays__c;
      }
      function extract13() { if (!this.out) { this.out = fieldChecked14(); } return this.out; };
      try {
          if (extract13.call(extract13) != null && 90.0 != null && extract13.call(extract13) >= 90.0) {
              return {status: 'COMPLIANT', conditionIndex: 399, conditionText: "extract('CA10__auditingRetentionDays__c') >= number(90.0)", currentStateMessage: "SQL Server auditing retention is 90 days or more.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      // condition[4], conditionIndex:[400..499]
      function fieldChecked17() {
          if (IsEmptyLib.simpleIsEmpty(obj.CA10__auditingRetentionDays__c)) {
              throw new Error("UNDETERMINED condition:401", {cause: {status: 'UNDETERMINED', conditionIndex: 401, conditionText: "CA10__auditingRetentionDays__c.delegatedTo(CA10__auditingRetentionDays__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditingRetentionDays__c;
      }
      function extract16() { if (!this.out) { this.out = fieldChecked17(); } return this.out; };
      try {
          if (extract16.call(extract16) == 0.0) {
              return {status: 'COMPLIANT', conditionIndex: 499, conditionText: "extract('CA10__auditingRetentionDays__c') == number(0.0)", currentStateMessage: "SQL Server auditing retention is unlimited.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      return {status: 'UNDETERMINED', conditionIndex: 500, conditionText: "otherwise", currentStateMessage: "Unexpected value in the field.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
  """;


  SELECT
    expectedResult.Id as Id,
    IF (
      IFNULL(expectedResult.expectedResult.status, '') = IFNULL(sObject.result.status, '')
      AND IFNULL(expectedResult.expectedResult.conditionIndex, -1) = IFNULL(sObject.result.conditionIndex, -1)
      AND IFNULL(expectedResult.expectedResult.conditionText, '') = IFNULL(sObject.result.conditionText, '')
      AND IFNULL(expectedResult.expectedResult.runtimeError, '') = IFNULL(sObject.result.runtimeError, ''),
      "MATCH",
      "FAIL"
    ) as match,
    expectedResult.expectedResult.status as expectedStatus, sObject.result.status as actualStatus,
    expectedResult.expectedResult.conditionIndex as expectedConditionIndex, sObject.result.conditionIndex as actualConditionIndex,
    expectedResult.expectedResult.conditionText as expectedConditionText, sObject.result.conditionText as actualConditionText,
    expectedResult.expectedResult.runtimeError as expectedRuntimeError, sObject.result.runtimeError as actualRuntimeError
  FROM UNNEST(mock_ExpectedResult()) expectedResult
  LEFT JOIN (
      SELECT
          sObject.Id AS Id,
          sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
          sObject.CA10__auditing__c AS CA10__auditing__c,
          sObject.CA10__auditingRetentionDays__c AS CA10__auditingRetentionDays__c,
          process_CA10__CaAzureSqlServer__c(
              STRUCT(
                  sObject.Id AS Id,
                  sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                  sObject.CA10__auditing__c AS CA10__auditing__c,
                  sObject.CA10__auditingRetentionDays__c AS CA10__auditingRetentionDays__c
              ),
              sObject.context.snapshotTime
          ) as result
      FROM UNNEST(mock_CA10__CaAzureSqlServer__c()) AS sObject
  ) sObject ON sObject.Id = expectedResult.Id;
