Page Comparison

About Collectors

...

• Python 3.6 - 3.10

• Access to the KADA Collector repository that contains the SQL Server whl

  • The repository is currently hosted in KADA’s Azure Blob Storage. You will be given a SAS token to access the repository. Reach out to KADA Support (support@kada.ai) if you do not have access.

  • Download the SQL Server whl (e.g. kada_collectors_extractors_sqlserver-#.#.#-py3-none-any.whl)

• Access to K landing directory

• Access to SQL Server (see section below)

• Check the SQLServer instance port

  • Run the following query and note the local tcp port.

    Code Block
    SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID GO

...

• INFORMATION_SCHEMA.ROUTINES

• INFORMATION_SCHEMA.VIEWS

• INFORMATION_SCHEMA.TABLE_CONSTRAINTS

• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

• INFORMATION_SCHEMA.TABLES

• INFORMATION_SCHEMA.COLUMNS

• INFORMATION_SCHEMA.VIEWS

• sys.foreign_key_columns

• sys.objectobjects

• sys.tables

• sys.schemas

• sys.columns

• VIEW SERVER STATE permission on the server

  • Required for Extended Event log

• VIEW Definition

  • All databases

    Code Block
    USE master GO GRANT VIEW ANY DEFINITION TO Kadauser

  • Selected databases. Repeat for each database

    Code Block
    USE <REPLACE WITH A DATABASE> GO GRANT VIEW ANY DEFINITION TO Kadauser

...

Some python packages also have dependencies on the OS level packages, so you may be required to install additional OS packages if the below fails to install.

You can download the latest Core Library and whl via Platform Settings → Sources → Download Collectors

...

install.

Run the following command to install the collector

pip install kada_collectors_extractors_<version>sqlserver-3.0.0-py3-none-any.whl

You will also need to install the common library kada_collectors_lib-1.0.0 for this collector to function properly.

pip install kada_collectors_lib-<version>1.0.0-py3-none-any.whl

...

{
    "server": "",
    "username": "",
    "password": "",
    "databases": [""],
    "host": ""
    "sqlserver_version": "2016",
    "driver": "ODBC Driver 17 for SQL Server",
    "events_path": "/tmp/Extendedevents*.xel",
    "output_path": "/tmp/output",
    "mask": true,
    "compress": true
}

...

import os
import argparse
from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger
from kada_collectors.extractors.sqlserver import Extractor

get_generic_logger('root') # Set to use the root logger, you can change the context accordingly or define your own logger

_type = 'sqlserver'
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type))

parser = argparse.ArgumentParser(description='KADA SqlServer Extractor.')
parser.add_argument('--config', '-c', dest='config', default=filename, help='Location of the configuration json, default is the config json in the same directory as the script.')
args = parser.parse_args()

start_hwm, end_hwm = get_hwm(_type)

ext = Extractor(**load_config(args.config))
ext.test_connection()
ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm})

publish_hwm(_type, end_hwm)

...

_hwm": start_hwm, "end_hwm": end_hwm})

publish_hwm(_type, end_hwm)

...

class Extractor(username: str = None, password: str = None, server: str = None, \
    host: str = None, driver: str = None, events_path: str = None, databases: list = [], \
    sqlserver_version: str = None, output_path: str = './output', mask: bool = False, \
    compress: bool = False, meta_only: bool = False) -> None

username: username to sign into sqlserver
password: password to sign into sqlserver
server: sqlserver host
host: the onboarded host value in K, generally it will be the same as the server
driver: sqlserver driver name
events_path: regex location of the events files on the server
databases: list of databases to extract
sqlserver_version: Release name for the SQLServer supported is 2012, 2016, 2017, 2019
output_path: full or relative path to where the outputs should go
mask: To mask the META/DATABASE_LOG files or not
compress: To gzip output files or not
meta_only: To extract without extended events enabled

...