SonarC Azure integration

SonarC has 3 modes of integration with Azure: 1. integrating DB logs from MS-SQL DBs on Azure into the existing SonarC data models and workflows 2. integrating DB logs from CosmosDB events into the existing SonarC data models and workflows 3. Import of generic event-hub messages into a collection in the SonarC warehouse

Azure Credentials:

In order for SonarC to access Azure there are 4 parameters that are required:

  1. Application ID
  2. AD Application Key
  3. Directory ID
  4. Subscription ID

Getting the 4 keys required

TENANT_ID

  • Log in to the Azure portal, navigate to Active Directory
  • Click on “Properties”
  • In the field: “Directory ID” you’ll find the TENANT_ID

CLIENT_ID

  • Log in to the Azure portal, navigate to Active Directory
  • Click on “App Registration”
  • Click on the “Management API”
  • In the field: “Application ID” you’ll find the CLIENT_ID

Note: the “application” ID used here, needs to have access to the storage account used by the DB server(s)

AD application key (a.k.a. KEY)

Is the key created for the “Management API” It cannot be retrieved, but a new one can be created at:

  • Log in to the Azure portal, navigate to “Active Directory”
  • Click on “App Registration”
  • Click on the “Management API”
  • Keys

SUBSCRIPTION_ID

  • Log in to the Azure portal, go to your DB Server
  • Click on “Properties”
  • In the field: “Subscription ID” you’ll find the SUBSCRIPTION_ID

Creating a Generic Event-Hub on Azure

You can either use an existing events hub or create a new one as described here or refer to the Microsoft documentation section below.

Go to the newly created or an existing events-hub:

Click on the “+ Event Hub”
Provide the required information (Name, Partition Count)
click on “Create”

Set the Policy: (you can set the policy in the root level or for each hub seperately):

Select the newly created hub, or the root Events-Hub
Select “Shared access policies”
Add a policy
Name
Select “Manage”
click "Create"

Adding a Container (required for SonarC integration)

For each eventhub you also need to create a storage container

Go to a storage account you want to use (preferably in the same zone of the eventhub):

Select “Blobs”
Click on “+Container”
Provide a name
Click “OK”

Setting events hub integration in SonarC

Preperations

  1. Enable the import ports

edit the inputs to enable the import ports

$ sudo vi /etc/rsyslog.d/sonargateway.conf

Remove the comment from:

#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/cosmosdb.conf
#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/eventhub.conf
#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/mssql_eventhub.conf

Restart rsyslog:

sudo systemctl restart rsyslog
  1. Add the definition for the formats to the DB

Open the mongo-shell and connect to SonarW

Insert the documents into the DB:

> use sonargd

> db.cloud_formats_eventhub.insert([{“name” : “Envelope”, “formatter” : { “type” : “Envelope”, “parameters” : { } }, “endpoint” : { “type” : “Syslog”, “parameters” : { “host” : “127.0.0.1”, “port” : NumberInt(10544) } } },{“name” : “Cosmos”, “formatter” : { “type” : “Cosmos”, “parameters” : { } }, “endpoint” : { “type” : “Syslog”, “parameters” : { “host” : “127.0.0.1”, “port” : NumberInt(10540) } } },{“name” : “Sql”, “formatter” : { “type” : “Envelope”, “parameters” : { } }, “endpoint” : { “type” : “Syslog”, “parameters” : { “host” : “127.0.0.1”, “port” : NumberInt(10572) } }}])

CosmosDB Integration with SonarC

The CosmosDB Integration requires the next items/steps:

  1. Azure Event hub Namespace. If you don’t already have an event-hub, refer to Creating a generic event-hub section.
  2. Azure Storage Account With container blob, If you don’t already have a Storage Account With container blob refer to Adding a container section.
  3. CosmosDB with Diagnostic logs Turned on (explained below).

Setting CosmosDB Diagnostic logs

This section assumes you already have a working CosmosDB on Azure.

In order to get the logs from CosmosDB you need to Enable Diagnostics:

  • Go to the Resource group (associated with the CosmosDB account)

  • Select “diagnostic Logs”

  • Select the CosmosDB account

  • Select “Turn on diagnostics”

  • Provide a name (when creating new diagnostics) or Select “On”

  • Select “Stream to an event-hub”

  • Click on “Event hub Configure” and

    • Select event-hub namespace, name & Policy name and click OK
  • Check the “DataPlaneRequests” checkbox

  • Check the “MongoRequests” checkbox

  • Check the “Requests” checkbox

  • “Save”

If you wish to get the same logs in a storage account select also: “Archive to a storage account” and specify the account to be used.

  1. Next you need to follow the instruction in the ”Setting the events pull” section

  2. The last step is to add the CosmosDB account credentials By:

    • click on “System Stored Credentials”
    • click on “Azure CosmosDb”
    • click “Add Azure CosmosDb” and enter the credentials for your CosmosDb account: Subscription ID, Resource Group, Database Account and Access Key.

SQL DB Integration with SonarC:

The SQL DB Integration requires the next items/steps:

  1. Azure Event hub Namespace. If you don’t already have an event-hub, refer to Creating a generic event-hub section.
  2. Azure Storage Account With container blob, If you don’t already have a Storage Account With container blob refer to Adding a container section.
  3. SQL DB with Diagnostic logs Turned on (explained below).

Setting SQL DB Diagnostic logs

This section assumes you already have a working SQL DB on Azure.

In order to get the logs from SQL DB you need to Enable Diagnostics:

  • Navigate to you SQL DB on Azure
  • click on “Diagnostics Settings”
  • “Add diagnostic setting”, give it a name and check the box for “Stream to an event Hub”
  • click on “configure”, select your previously created eventhub namespace and policy.
  • click “ok”, check the box for “Audit” then “Save”
  1. Next you need to follow the instruction in the ”Setting the events pull” section

Setting the events pull

Pulling from a standard Eventhub

Login to the SonarC system with a user that has the SonarCloudAdmin privileges

  • In “Cloud Mgt.” select “cloud sources”
  • Select “Add New Cloud Source”
  • Select “Add Azure Event Hub”

Provide the Azure credentials to access the Azure portal:

Application ID
AD Application Key
Directory ID
Subscription ID

Refer to the “Azure Credentials” section for details on how to get these parameters

Once filled the system will query Azure and will provide a list of available eventhubs.

  • Select the hub you want to set (a unique hub is defined by the namespace + name”
  • Set the Checkpoint Msg Count (How many events to read on each access)
  • Define the message formatter based on the formats defined in the previous step (Envelope for standard eventshub, Cosmos for CosmosDB or Sql for sql DB events)
  • Ensure “Skip Invalid Msg” is turned On
  • Select the storage account where you added the container to
  • Select the container
  • Click on “Add Azure Event Hub(s)”

A new item is now available in the list of Cloud sources (it is disabled)

You can just click on the “Enabled” button to start it or click on the item to edit any of the setting.

Once enabled the icon will turn yellow and once connection is made with the event-hub (this could take some time) the icon will turn green (make sure to refresh the page every few minutes).