Skip to main content

Site CollectorSite Collector Administration Guide

Set Up Microsoft SQL Collector

If you use Microsoft SQL Relational Database Management Systems (RDBMS), you can set up the Microsoft SQL collector to pull logs from your RDBM sources. The Microsoft SQL collector is a set of Site Collector flows, pre-built processors, groups, custom processors, other components, and integrations that pull logs in JSON format from your databases and push the logs to the New-Scale Security Operations Platform.

This collector supports the following Microsoft SQL database versions: Microsoft SQL 2016 SP3, 2017, 2019, and 2022.

To set up a Microsoft SQL collector:

  1. Log in to the New-Scale Security Operations Platform with your registered credentials.

  2. Navigate to Collectors > Site Collectors.

  3. Ensure that Site Collector is installed and in running state.

  4. On the Site Collector page, click the Collectors Library tab, then click Microsoft SQL.

    MS_SQL_1.png

  5. In the Definition section, enter the required information as follows.

    MS_SQL_2.png

    • Collector Name – Specify a name for the Microsoft SQL collector.

      Note

      Ensure that you specify different names for Site Collector instance and the collector.

    • Site Collector Instance – Select the site collector instance for which you want to set up the Microsoft SQL collector.

    • MS SQL Hostname or IP – Enter the IP address of the Microsoft SQL database server from which you want the Microsoft SQL collector to pull logs.

    • Port – Enter the port number of your Microsoft SQL server.

    • Database – Enter the database name of the destination.

    • Enable SSL – Select to enable secure connection between the Microsoft SQL collector and your Microsoft SQL server.

  6. Click Next.

  7. In the Authentication section, enter the username and password of an existing database user of your MS SQL server, for establishing connection with the Microsoft SQL source.

    MS_SQL_3.png

  8. Click Next.

  9. In the Data section, enter the required information as follows.

    MS_SQL_5.png

    • Fetch Interval – Select the time interval within which you want the Microsoft SQL collector to pull logs. For example: 30 sec, 1 min, 2 min, 3 min, 4 min, or5 min.

    • Search Query – Enter the following details:

      • Iterator Column – Enter the value that will be used for incremental data pull. Ensure that the column value is unique, and is returned in the query result set.

      • Iterator column initial value – Enter the value for the initial data pull. Use the iterator column initial value to pull historical data.

      • Query – Enter the Microsoft SQL query to specify the type of data that you want the collector to pull.

        For example: To fetch logs from the Microsoft SQL database, use the following query.

        Select *
FROM <table>
tmp.id > 0

        where id is an iterator column

        The Iterator Column value must be returned as a uniquely named column in the dataset. If you use joins, use AS to specify a rename for the iterator. For example:

        Select table1.id as iterator, table2.id, table2.value from table1 join table2 on table1.id = table2.fk where table2.value > 0

    • Query Preview – View the preview of the query that you enter.

      For example:

      SELECT *

FROM

(SELECT *

FROM <table>

)

WHERE id > 0

  10. Click Setup.

    The Microsoft SQL collector is set up and is ready to pull logs from your Microsoft SQL database.

    After the Microsoft SQL collector is set up, Site Collector Core starts pulling logs periodically based on the query entered by the user and uploads logs to New-Scale Security Operations Platform. If the Microsoft SQL database is not available, Site Collector core resumes pulling logs from the place where it stopped.

    Note

    BLOB fields collection is not supported.

    In case of installation failure, the collector is disabled, and the configuration is saved. You can check the status of the collector by accessing the user interface or by using the support package.

Examples of ID-based or Time-based Microsoft SQL Queries

Refer to the following use cases for Microsoft SQL Queries that are often Id-Based or Time-Based.

ID-based Microsoft SQL Queries

In the ID- based Microsoft SQL queries, the iterator column is a sequential number as explained in the following example of Badge Access use case. In this use case, users swipe their badges at a card reader to get into the company’s entrances. This badge system has accumulated 30,000 swipes since it’s been online.  Let’s pickup from what we know as a recent log count.

The query includes the following. 

SELECT sequence_id, badge_number, card_reader, door_name, office_name, city_name, access_time, access_result   
FROM badge_accesses
Id-Based_query.png

Time-based Microsoft SQL Queries

In this Time-Based scenario, users swipe their badges at a card reader to get into the company’s entrances. This badge system has a lifetime of logs, yet let’s only pickup from March 1st of 2025.  The default format for matching time field is a string 'YYYY-MM-DD'.

The query includes the following. 

SELECT badge_number, card_reader, door_name, office_name, city_name, access_time, access_result   FROM badge_accesses
Time-based_query.png