MSSQL Data

The Splunk Add-on for Microsoft SQL Server is required for the field extraction.

Splunkbase Download: https://splunkbase.splunk.com/app/2648

Installation and Configuration Guide: https://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/About

The Cyences App has support following data collection mechanisms

  1. Window Application log using Splunk Add-on for Microsoft Windows (Recommended)
  2. By reading Audit table using Splunk DB Connect app
    • https://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration

    • https://www.ultimatewindowssecurity.com/sqlserver/auditlog/auditobject.aspx

    • Details needed from DBA Team to configure identity and connection on DB connect app:
      • IP Address or FQDN of DB server
      • Port number for DB server
      • DB table name that contains Audit Trail data
      • Username & Password - Should have Readonly access to the Audit Trail Table in the DB
      • Default database name and Database name
      • Timezone on the database server
    • While creating input on DB connect app, follow the below steps:
      1. Select the appropriate Connection form left hand side panel.
      2. Try to build query by selecting Catalog, Schema and Table values from dropdown OR write query manually as mentioned below and run it. 
         SELECT *
 FROM <<TABLE>>;
        • Replace «TABLE» with table name.
      3. Once query runs successfully, select Type “Rising” on right hand side panel. (This step is required to get only latest data.)
      4. After selecting type “Rising”, follow the all steps mentioned on the right hand side panel and run the search and click on “Next”.
      5. Set appropriate index, source and sourcetype and execution frequency (interval after which input invokes everytime) and click on “Finish”.
  • Make sure that you have installed Splunk_JDBC_mssql Add-on https://splunkbase.splunk.com/app/6150 on your HF (where DB connect is installed). This is requirement for DB Connect App for database driver availability for Oracle.
  • Make sure that you have installed Splunk_TA_microsoft-sqlserver Add-on https://splunkbase.splunk.com/app/2648 on both your HF (where DB connect is installed) & on the SH.
  • Make sure to use mssql:audit sourcetype when configuring the data input in DB Connect App.
  1. By reading *.sqlaudit files using Splunk DB Connect app.

    • Audit logs stored in *.sqlaudit files are in binary format hence we can’t read it using splunk monitor input and need to read it via DB connect app.

    • Details needed from DBA Team to configure identity and connection on DB connect app:
      • IP Address or FQDN of DB server
      • Port number for DB server
      • Username & Password - Should have permission as mentioned here
      • Default Database name
      • Timezone on the database server
      • File path where *.sqlaudit files are located
    • While creating input on DB connect app, follow the below steps:
      1. Select the appropriate Connection form left hand side panel.
      2. Add following search and run it. 
         SELECT *
 FROM sys.fn_get_audit_file(
 '<<FILE_PATH>>',
 DEFAULT,
 DEFAULT
 )
        • Replace «FILE_PATH» with appropriate path where *.sqlaudit files are located.
      3. Once query runs successfully, select Type “Rising” on right hand side panel. (This step is required to get only latest data.)
      4. After selecting type “Rising”, follow the all steps mentioned on the right hand side panel and run the search and click on “Next”.
      5. Set appropriate index, source and sourcetype and execution frequency (interval after which input invokes everytime) and click on “Finish”.
  • Make sure that you have installed Splunk_JDBC_mssql Add-on https://splunkbase.splunk.com/app/6150 on your HF (where DB connect is installed). This is requirement for DB Connect App for database driver availability for Oracle.
  • Make sure that you have installed Splunk_TA_microsoft-sqlserver Add-on https://splunkbase.splunk.com/app/2648 on both your HF (where DB connect is installed) & on the SH.
  • Make sure to use mssql:audit sourcetype when configuring the data input in DB Connect App.

  1. Azure MSSQL using Splunk Add-on for Microsoft Cloud Services

    1. Enable Audit Log: https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-audit-logs

    2. Collect using Azure Event Hub Input: https://splunk.github.io/splunk-add-on-for-microsoft-cloud-services/Configureeventhubs/

      • Use mssql:audit:json as sourcetype when creating input

Note: Use both index=mssql for data collection or update the macro definition for cs_mssql (Settings > Configuration).

Estimated Data Size

The license usage consumed by the Splunk Add-on for Microsoft SQL Server is based on the audit policy and database usage of your environment

Copyright © 2024 CrossRealms International.