MSSQL

The MSSQL component lookup allows you to form components from the records in a Postgres database.

In this example below, we form components from all the tables in the incident_commander database.

mssql-check.yml
apiVersion: canaries.flanksource.com/v1
kind: Topology
metadata:
  name: mssql-tables
  namespace: default
spec:
  schedule: '@every 30s'
  components:
    - name: MSSQL
      type: Table
      icon: mssql
      lookup:
        mssql:
          - connection: mssql://sa:yourStrong(!)Password@localhost:1433/incident_commander
            query: |
              SELECT 
                s.name AS schema_name,
                t.name AS table_name,
                p.rows AS num_rows
              FROM 
                sys.tables t
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                INNER JOIN sys.partitions p ON p.object_id = t.object_id
                INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
              WHERE
                t.is_ms_shipped = 0
              ORDER BY
                p.rows DESC;
            display:
              expr: |
                results.rows.map(row, {
                  'name': row.schema_name + '.' + row.table_name,
                  'type': "Table",
                  'properties': [{
                    "name": "Records",
                    "headline": true,
                    "value": double(row.num_rows),
                  }]
                }).toJSON()

Field	Description	Scheme	Required
`auth`	Username and password value, configMapKeyRef or SecretKeyRef for Postgres server	Authentication
`connection`	Connection string to connect to the SQL Server server	string	Yes
`display`	Template to display query results in text (overrides default bar format for UI)	Template
`query`	query that needs to be executed on the server	string	Yes
`results`	Number rows to check for	int	Yes

Results

The results variable in the template will contain the following fields

Field	Description	Scheme
`rows`	stderr from the script	`[]map[string]any`
`count`	exit code of the script	`int`

MSSQL

Results​

Results