SQL

The SQL configuration scraper will execute a SQL query and then create a configuration item for each returned row.

The example below creates a new MSSQL::Database configuration for each database on the sql server, and then creates a roles object container the SQL Server login to database role mapping. With change detection this will highlight when new users are added / modified / removed on an individual databases.

sql-scraper.yaml
apiVersion: configs.flanksource.com/v1
kind: ScrapeConfig
metadata:
  name: sql-scraper
spec:
  sql:
    - connection: 'sqlserver://localhost:1433?database=master'
      auth:
        username:
          value: sa
        password:
          value: password
      type: MSSQL::Database
      id: $.name

      transform:
        full: true # transform the entire configuration item, and not just the configuration data (row)
        script:
          javascript: |+
            var dbs = {}
            for (var i = 0; i < config.rows.length; i++) {
              var db = config.rows[i]
              var name = db.DB
              if (dbs[db.DB] == null) {
                {
                  config:               dbs[db.DB] = {
                    name: name,
                    roles: {}
                  },
                  changes: {

                  },
                  analysis: {

                  }

                }
              }
              dbs[name].roles[db.role] = db.name
            }
            JSON.stringify(_.values(dbs))

      query: |
        declare  @mytable table (
          [DB] [nvarchar](128) NULL,
          [name]  [nvarchar](255)  NOT NULL,
          [role]  [nvarchar](255)  NOT NULL
          )


        DECLARE @command varchar(1000)
        SELECT @command =
        'USE ?; SELECT DB_NAME() as DB, DP1.name AS [user],
          isnull (DP2.name, ''No members'') AS [role]
        FROM sys.database_role_members AS DRM
        RIGHT OUTER JOIN sys.database_principals AS DP1
          ON DRM.role_principal_id = DP1.principal_id
        LEFT OUTER JOIN sys.database_principals AS DP2
          ON DRM.member_principal_id = DP2.principal_id
        WHERE DP1.type = ''R'' and DP2.name is not null'

        insert into @mytable  EXEC sp_MSforeachdb @command

        select * from @mytable

Scraper

Field	Description	Scheme	Required
logLevel	Specify the level of logging.	string
schedule	Specify the interval to scrape in cron format. Defaults to every 60 minutes.	string
full	Set to true to extract changes from scraped configurations. Defaults to false.	bool
retention	Settings for retaining changes, analysis and scraped items	Retention
sql	Specifies the list of SQL configurations to scrape.	[]SQL

SQL

Field	Description	Scheme
id*	A deterministic or natural id for the resource	string or JSONPath
query*	SQL query to execute	SQL
type*	e.g. File::Host, File::Tomcat, File::Pom	string or JSONPath
class		string or JSONPath
createFields	Identify the created time for a resource (if different to scrape time). If multiple fields are specified, the first non-empty value will be used	[]string or []JSONPath
deleteFields	Identify when a config item was deleted. If multiple fields are specified, the first non-empty value will be used	[]string or []JSONPath
format	Format of config item e.g. xml, properties. Defaults to JSON	string
ignore	Fields to ignore and strip out of the config	[]JSONPath
items	Extract multiple config items from this array	JSONPath
name	. Defaults to id	string or JSONPath
properties	Custom templatable properties for the scraped config items.	[]ConfigProperty
tags	Additional tags	map[string]string
timestampFormat	Format to parse timestamps in createFields and deletedFields. Defaults to RFC3339	Go time format
connection	The connection url or name to use`	Connection
url	If connection is specified and it also includes a url, this field will take precedence	string
auth.username		EnvVar
auth.password		EnvVar