SonarGateway Systems

SonarGateway is a set of packages and systems that allow you to insert data into SonarC. SonarGateway ingests data using two basic mechanisms: (1) rsyslog using SonarGateway, (2) handling log streams that can be directed to syslog and by tailing and syncing complete log files to SonarC.

SonarGateway comprises of the following modules:

  1. SonarGateway - responsible for receiving remote messages, normalizing them, transforming them, and inserting them into appropriate collections in SonarC.
  2. SonarLogstash - a Logstash process that can write directly into SonarW, SonarC’s data repository.
  3. SonarOracleGateway - A process that runs on Oracle hosts and sync xml log file to SonarC.
  4. SonarMaprGateway - A package that installs, verifies, and configures rsyslog on a mapr system.
  5. SonarMaprAgent - A package that configures periodic sync of MapR log files from the MapR file system to a local file system, where rsyslog monitors it.

SonarGateway

SonarGateway is a user-configurable rule-based input adapter for SonarC. It processes input in three general steps:

  1. Normalizing a message into Key/Value pairs using a specified Event Format
  2. Transforming those Key/Value pairs into typed and renamed BSON Field/Value pairs
  3. Dynamic, content-aware, routing of the resulting BSONs to specific collections in a database

Sometimes we refer to all three steps as “normalization”. The Event Format defines basic text processing parameters such as delimiters and rules for valid Key names, etc. This step specifies the syntax of the raw input data.

The second step can be thought of as “what” the data should look like; the third step as “where” the data should reside. These two steps specify the desired semantics of the data.

SonarGateway normalization flexibly adapts unstructured text input into meaningful collections of documents that can be further queried, analyzed, and visualized.

SonarGateway Installation

SonarGateway is installed as part of the SonarC product suite. Once SonarC is installed the SonarGateway service will be running and ready to accept incoming syslog data.

We use separate network ports for the normalization of different Event Formats.

The normalization syntax for the Event Formats is fixed in source code. User editable configuration files control the transformation and dynamic routing of content to specific collections and databases; in other words, by editing the configuration files the user can change the semantics of the data to match their requirements.

Important: For Red Hat systems you will need to set selinux to Permissive to allow SonarGateway to access the Event Format ports:

$ sudo bash
$ setenforce 0
$ getenforce
Permissive

SonarGateway Configuration

For each Event Format there are three configuration files that control how SonarGateway transforms and routes incoming content:

/etc/sonar/gateway/{mssql,oracle,qradar, ... etc ...}.json -
/etc/rsyslog.d/sonar/gateway/rulesets/{mssql,oracle,qradar, ... etc ...}.conf

Event normalization and transformation are configured by one of /etc/sonar/gateway/*.json. This file can be configured by the user. It controls the semantics mentioned above.

Network ports, format-specific parameters, and binary command line options are configured by files in the /etc/rsyslog.d/sonar/gateway/ directory. For most applications this file does not need to be changed.

Important: Every change to a configuration file requires:

$ systemctl restart rsyslog

before the configuration changes will take effect.

Configuration Validation

SonarGateway requires successful validation of the provided configuration file. If the configuration file does not validate SonarGateway will not run and no events will be processed. Let’s start with getting a basic Hello SonarGateway configuration to validate correctly. Once we have this we can proceed to demonstrate more SonarGateway functionality by building on this configuration file.

First, jump forward to the SonarGateway logfile section and increase the verbosity of the logging by enabling INFO level logs.

We automatically validate the JSON configuration every time we restart the SonarGateway service. The validation checks for syntax errors in the JSON, that all required fields are present, and that we can connect to any specified Sonar URIs. As you create more and more complex configurations it will be helpful to run this validation yourself before starting the service and processing syslog events. Open up a terminal and run the following:

/usr/lib/sonar/gateway/sonargateway –validate –config /etc/sonar/gateway/helloworld.json –delay_depot_dir /var/lib/sonar/gateway

You will likely see something like the following error:

2017-07-03 15:50:54,922 ERROR Connecting using default collection: instance

2017-07-03 15:50:54,922 ERROR Client error message: No suitable servers found (serverSelectionTryOnce set): [connection refused calling ismaster on ‘127.0.0.1:27119’]

2017-07-03 15:50:54,922 ERROR SonarGateway failed to validate config file at: /etc/sonar/gateway/helloworld.json

SonarGateway can’t connect to the running SonarW instance. Let’s fix that, assuming that SonarW is running at port 27117 on the localhost. Edit the configuration file /etc/sonar/gateway/helloworld.json and notice that the port number is 27119 in the sonar_URI JSON field, a database connection string where you can specify user, password, etc. as per the SonarW documentation:

[
    {
            "output_connection": {
                    "event_format": {
                            "standard": "CEF"
                    },
            "sonar_URI": "mongodb://CN=admin@localhost:27119/ ... ",
                    "target_db": "sonargateway",
                    "default_collection": "instance",
                    "unique_label": "instance_cef",
                    "group_label": "instance_cef",
                    "collection_selectors": {
                            "select_by_key_value": {
                                    "cs2": "MSSQL"
                            }
                    }
            }
    }
]

Change the sonar_URI to whatever is the correct URI for your running SonarW instance, save the file, and rerun the validation. You will probably only need to change port 27119 to 27117 before running:

sudo /usr/lib/sonar/gateway/sonargateway –validate –config /etc/sonar/gateway/helloworld.json –delay_depot_dir /var/lib/sonar/gateway

You should see that the configuration is successfully validated, like this:

2017-07-03 15:50:26,603 INFO No sonar_DR_URI specified in output_connection with unique label: instance. You may specify one in instance or in global_settings.sonar_DR_URI

2017-07-03 15:50:26,603 INFO sonargateway successfully validated config file at: /etc/sonar/gateway/helloworld.json

Once you see this success, double check that you can authenticate as well as connect with the given sonar_URI string. In the example below you’ll see that the simpler URI string doesn’t return success because it’s missing user name, password, and authenticaton database. When these are added to the URI, we see that we can connect and authenticate:

##      vvvv sonar_URI string vvv
$ mongo mongodb://127.0.0.1:27117 --eval "db.hostInfo()" | grep -F "\"ok\" : 1"
## didn't return "ok" : 1 -- we connected but did NOT authenticate

##      vvvvvvv correct sonar_URI string vvvvvvvvv
$ mongo mongodb://[user]:'[pass]'@[ip]:27117/[authdb] --eval "db.hostInfo()"|grep ok
"ok" : 1
## returned "ok" : 1 -- we connected and authenticated

IMPORTANT: Keep editing the file, running the validation, and checking the URI authentication from the command line, until the file validates and the db.hostInfo() check returns ok. SonarGateway will not run until all these steps are successful.

You can tail the logfile in a separate terminal window to monitor the status and progress of SonarGateway as we work through the following sections. Open a terminal and type tail -f /var/log/sonar/gateway/sonargateway.log to follow the logfile.

Event Normalization

The /etc/sonar/gateway/*.json files are arrays of JSON objects. The most important objects are output_connections. They configure the normalization and final destination for JSON documents generated from the incoming syslog events.

As mentioned in the SonarGateway introduction, basic normalization is performed according to the event_format specified in the SonarGateway Configuration. Those formats are mostly fixed industry standards along with some customer-specific event formats.

You can configure SonarGateway to change the “what” and “where” semantics of the data via Field Translations and Collection Selectors. Again, we use “normalization” as convenient short-hand to describe the sequence of all of the steps.

Let’s build up towards more complex normalization tasks by starting with a basic Hello SonarGateway example.

Hello SonarGateway

Here is a minimal output_connection object. It will attempt to normalize using the Common Event Format (CEF) and if successful then inserts the resulting JSON document to the collection instance in database sonargateway:

"output_connection": {
        "event_format": {
                "standard": "CEF"
        },
        "sonar_URI": "mongodb://CN=admin@localhost:27117/admin? ... ",
        "target_db": "sonargateway",
        "default_collection": "instance",
        "unique_label": "instance",
        "group_label": "instance",
        "collection_selectors": {
                "select_by_key_value": {
                        "cs2": "MSSQL",
                }
        }
}

Notes:

  • event_format is one of our supported event formats
  • sonar_URI is the database connection string. You can specify user, password, etc. as per the SonarW documentation
  • target_db – all collections created by this output_connection will be in this database
  • unique_label is a string to uniquely identify this output_connections object
  • group_label – event processing stops for all subsequent output_connections with matching group_label once one output_connection has successfully processed an event in that group. If you want all output_connections to attempt to process events use the unique_label as the group_label
  • collection_selectors contains selection rules used to determine if the incoming event should be processed and what collection it should be inserted into

Let’s try this out. You will need sudo access for the following. We are using ncat (yum install nmap-ncat on RedHat systems) to echo a syslog string in CEF format to port 10512 on the localhost. Open a terminal and:

$ sudo bash
$ vi /etc/rsyslog.d/sonar/gateway/sonargateway.conf

Comment in the helloworld.conf line:

...
#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/eventhub.conf
$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/helloworld.conf
#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/horton.conf
...

$ vi /etc/sonar/gateway/helloworld.json ## change sonar_URI if necessary
$ systemctl restart rsyslog

Then:

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=MYSQL cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Notice that we are using port 10512 for these examples. You can freely edit the helloworld.conf file without interfering with any other configurations.

You should see the following if you tail -f /var/log/sonar/syslog/sonargateway.log:

2017-06-30 02:09:38,338 INFO Successfully connected to default collection: instance

2017-06-30 02:09:38,339 WARNING SonarGateway Release v1.2.1-238-g919e1bc: Logging started/reconfigured, pid 10889

2017-06-30 02:09:38,339 INFO Looking for delayed messages in /var/lib/sonar/gateway/instance_cef_4716186055225792270.journal …

2017-06-30 02:09:38,339 INFO No delayed messages in /var/lib/sonar/gateway/instance_cef_4716186055225792270.journal.recovering

2017-06-30 02:09:38,339 INFO Creating a collection buffer in namespace sonargateway.instance

2017-06-30 02:09:53,322 INFO Insert 1 bson to sonargateway.instance

And from the Sonar shell you should see:

> use sonargateway
switched to db sonargateway
> show collections
instance
system.ingest
> db.runCommand({flush: "instance"})
{ "ok" : 1 }
> db.instance.findOne()
{
    "_id" : ObjectId("59e7ff0896cb25067166bff5"),
    "CEF Version" : 0,
    "Device Product" : "Guardium",
    "Device Vendor" : "IBM",
    "Device Version" : 10,
    "Name" : "Log all SQLs - Full SQL Template",
    "Severity" : "5",
    "Signature ID" : NumberLong(20011),
    "deviceAction" : "SQL_LANG",
    "applicationProtocol" : "MYSQL",
    "Server Type" : "MYSQL",
    "DB Protocol Version" : "10.0.0",
    "destinationPort" : NumberLong(47741),
    "destinationAddress" : "192.168.0.22",
    "destinationUserName" : "ROOT",
    "externalId" : NumberLong(-1),
    "message" : "select @@version_comment limit 1",
    "deviceReceiptTime" : ISODate("2017-06-02T16:32:02.951Z"),
    "sourceProcessName" : "MYSQL CLIENT",
    "sourcePort" : NumberLong(12160),
    "sourceAddress" : "192.168.0.22",
    "startTime" : ISODate("2017-06-02T16:32:02.951Z")
}

Notice that we have applied the CEF standard while normalizing and, for example, have translated the input syslog string rt=1496421122951 to an ISODate "deviceReceiptTime" : ISODate("2017-06-02T16:32:02.951Z") in the final JSON document.

In the next section we’ll expand on the Hello SonarGateway JSON configurations to add Field Translations. These translations rename and typecast matching syslog key/value pairs before adding them to the final JSON document.

SonarGateway logfile

If you run tail -f /var/log/sonar/gateway/sonargateway.log` and ncat a syslog event to SonarGateway, you see something like the following (except without the added line numbers like Line 1 ---->):

Line 1 —-> 2017-06-30 02:09:38,338 INFO Successfully connected to default collection: instance

Line 2 —-> 2017-06-30 02:09:38,339 WARNING SonarGateway Release v1.2.1-238-g919e1bc: Logging started/reconfigured, pid 10889

Line 3 —-> 2017-06-30 02:09:38,339 INFO Looking for delayed messages in /var/lib/sonar/gateway/instance_cef_4716186055225792270.journal …

Line 4 —-> 2017-06-30 02:09:38,339 INFO No delayed messages in /var/lib/sonar/gateway/instance_cef_4716186055225792270.journal.recovering

Line 5 —-> 2017-06-30 02:09:38,339 INFO Creating a collection buffer in namespace sonargateway.instance

Line 6 —-> 2017-06-30 02:09:53,322 INFO Insert 1 bson to sonargateway.instance

SonarGateway will occasionally write other messages to this log file.

Line 1 is from the Configuration Validation.

Line 2 is important: it comes from the running SonarGateway process and shows the build revision and process ID (pid), in this case 10889. If you see many Logging started/reconfigured, pid ... lines with different process IDs this usually means there’s a configuration error causing the SonarGateway to exit and restart. The pid in Line 2 should live until you manually restart the service.

Lines 3 and 4 are journal files we use to store syslog events in the case that SonarW is temporarily unavailable. Note that the location of these journal files can be changed via the --delay-depot-dir argument in /usr/lib/sonarw/run_sonargateway.sh.

Line 5 shows the database/collection namespace where the document will be inserted.

Line 6 shows that the normalized syslog BSON has been inserted to SonarW.

Before you can see these lines you must can increase the verbosity of the logging by editing /etc/sonar/gateway/logging.conf:

* GLOBAL:
   FORMAT               =  "%datetime %level %msg"
   FILENAME             =  "/var/log/sonar/gateway/sonargateway.log"
   ENABLED              =  true
   TO_FILE              =  true
   TO_STANDARD_OUTPUT   =  true
   PERFORMANCE_TRACKING =  false
   MAX_LOG_FILE_SIZE    =  10485760 ## 10MB
   LOG_FLUSH_THRESHOLD  =  1
* TRACE:
   ENABLED              =  true  ## do not disable
* DEBUG:
   ENABLED              =  false
* FATAL:
   ENABLED              =  true
* ERROR:
   ENABLED              =  true
* WARNING:
   ENABLED              =  true
* INFO:
   ENABLED              =  false

To increase the verbosity of the logfile, you can enable INFO as follows:

* INFO:
   ENABLED              =  true

You change other parameters – for example the location of the logfile – if you wish.

Then save /etc/sonar/gateway/logging.conf and, as always:

$ systemctl restart rsyslog

for the logging configuration changes to take effect.

After this change, return to tail -f /var/log/sonar/gateway/sonargateway.log` and ncat a syslog event to SonarGateway, you will only see the TRACE output:

2017-06-30 02:11:23,912 WARNING SonarGateway Release v1.2.1-238-g919e1bc: Logging started/reconfigured, pid 25103

Field Translations

SonarGateway uses Field Translations to, for example,

  • rename input Keys to desired JSON field names
  • type cast input Value to desired BSON types

There are other Translations available (covered in the configuration reference below). As described in the SonarGateway introduction, Field Translations determine “what” the final data looks like. Later we’ll see how Collection Selectors determine “where” the data reside and the combination of “what” and “where” define the semantics of the data.

In the last example we saw the following field/values in the final JSON:

"Severity" : "5"
"message" : "select @@version_comment limit 1"

Let’s do the following Translations:

  • type cast Severity to an Integer
  • rename message to “Login Message”.

Open /etc/sonar/gateway/helloworld.json with a text editor. You’ll see the JSON from the Hello SonarGateway example. Edit the file, adding the three Field Translations JSON objects side by side with the output_connection:

[
        {
                "output_connection": {
                        "event_format": {
                                "standard": "CEF"
                        },
                        "sonar_URI": "mongodb://CN=admin@localhost:27117/admin? ... ",
                        "target_db": "sonargateway",
                        "default_collection": "instance",
                        "unique_label": "instance_cef",
                        "group_label": "instance_cef",
                        "redact_unmatched_fields": true,
                        "collection_selectors": {
                                "select_by_key_value": {
                                        "cs2": "MYSQL"
                                }
                        }
                },
                "Severity": {
                        "type": 18
                },
                "msg": {
                        "rename": "Login Message"
                }
        }
]

When you’ve edited this file open a terminal and enter the following commands:

$ sudo bash

$ systemctl restart rsyslog ## for the configuration settings to take effect

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=Guardium cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

And then from the Sonar shell:

> use sonargateway
switched to db sonargateway
> show collections
instance
system.ingest
> db.runCommand({flush: "instance"})
{ "ok" : 1 }
> db.instance.find().sort({_id:-1}).pretty()
{
    "_id" : ObjectId("595a62f8769f2b4b3a6c9cc2"),
    "Severity" : NumberLong(5),
    "Login Message" : "select @@version_comment limit 1"
}
{
    "_id" : ObjectId("59e7ff0896cb25067166bff5"),
    "CEF Version" : 0,
    "Device Product" : "Guardium",
    "Device Vendor" : "IBM",
    "Device Version" : 10,
    "Name" : "Log all SQLs - Full SQL Template",
    "Severity" : "5",
    "Signature ID" : NumberLong(20011),
    "deviceAction" : "SQL_LANG",
    "applicationProtocol" : "MYSQL",
    "Server Type" : "MYSQL",
    "DB Protocol Version" : "10.0.0",
    "destinationPort" : NumberLong(47741),
    "destinationAddress" : "192.168.0.22",
    "destinationUserName" : "ROOT",
    "externalId" : NumberLong(-1),
    "message" : "select @@version_comment limit 1",
    "deviceReceiptTime" : ISODate("2017-06-02T16:32:02.951Z"),
    "sourceProcessName" : "MYSQL CLIENT",
    "sourcePort" : NumberLong(12160),
    "sourceAddress" : "192.168.0.22",
    "startTime" : ISODate("2017-06-02T16:32:02.951Z")
}

The fields have been translated as we specified:

"Severity" : NumberLong(5),
"Login Message" : "select @@version_comment limit 1"

Let’s revisit the JSON for each.

For each Field Translation the name of the object is the key name in the original syslog input.

The type field specifies the final BSON type for the value.

The rename value specifies the final field name in the output JSON.

Redacting and Ingesting

In the section on Field Translations there were two important details that were glossed over. First, there was the following line in the output_connection configuration:

"redact_unmatched_fields": true

The redact_unmatched_fields is not required and is false by default. However, it’s a very useful configuration setting because when true only fields that have matching Field Translations will be added to the final BSON. This allows for very precise output document structure and will save disk space by redacting unnecessary fields.

The second important detail was the need to flush the collection from the Sonar shell:

> use sonargateway
switched to db sonargateway
> show collections
instance
system.ingest
> db.runCommand({flush: "instance"})

By default SonarGateway creates ingested collections. In the above example, even though SonarGateway has flushed the BSON, db.instance.count() will return 0 until the ingest queue is flushed using db.runCommand({flush: "instance"}) or until SonarW decides to automatically flush. See the SonarW documentation for more details. Collections are ingested by default since that allows us to sustain a higher data input rate into SonarW. You can turn off ingest by collection name using global_settings.

Edit /etc/sonar/gateway/helloworld.json. Copy/paste the output_connection to create a second one and change the default_collection, unique_label, group_label, and turn off ingestion for this collection. This is done with the do_not_ingest field which takes an array of collection names:

[
{
    "global_settings": {
    "do_not_ingest": [
        "session"
    ],
    "sonar_URI": "mongodb://CN=admin@localhost:27117/admin? ... ",
    "target_db": "sonargateway"
    }
},
{
    "output_connection": {
    "event_format": {
        "standard": "CEF"
    },
    "default_collection": "session",
    "unique_label": "session_cef",
    "group_label": "session_cef",
    "redact_unmatched_fields": true,
    "collection_selectors": {
        "select_by_key_value": {
        "cs2": "MYSQL"
        }
    }
    },
    "Severity": {
    "type": 18
    },
    "msg": {
    "rename": "Login Message"
    }
},
{
    "output_connection": {
    "event_format": {
        "standard": "CEF"
    },
    "sonar_URI": "mongodb://CN=admin@localhost:27117/admin? ... ",
    "target_db": "sonargateway",
    "default_collection": "instance",
    "unique_label": "instance_cef",
    "group_label": "instance_cef",
    "redact_unmatched_fields": true,
    "collection_selectors": {
        "select_by_key_value": {
        "cs2": "MYSQL"
        }
    }
    },
    "Severity": {
    "type": 18
    },
    "msg": {
    "rename": "Login Message"
    }
}
]

In the Sonar shell, drop the example database with:

> use sonargateway
switched to db sonargateway
> db.dropDatabase()
{ "dropped" : "sonargateway", "ok" : 1 }

Open a terminal and run:

$ sudo bash

$ systemctl restart rsyslog ## for the configuration settings to take effect

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=Guardium cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Running tail -f /var/log/sonar/gateway/sonargateway.log, you’ll see:

[date] INFO parsing global settings
[date] TRACE Global target DB name is sonargateway
[date] TRACE Global DB URI is set.
[date] INFO Successfully connected to default collection: session
[date] INFO Successfully connected to default collection: instance
[date] WARNING SonarGateway Release [version]: Logging started/reconfigured, [pid]
[date] INFO Creating a collection buffer in namespace sonargateway.session
[date] INFO Creating a collection buffer in namespace sonargateway.instance
[date] INFO Insert 1 bson to sonargateway.session
[date] INFO Insert 1 bson to sonargateway.instance

Note the INFO parsing global settings – that’s going to mark session as do_not_ingest. And note that the Collection Selectors in both output_connections have matched which creates two collection buffers (those are the last two lines of the log file example shown).

Back in the SonarW shell run the following commands. Note that the count() for instance is 0 because it is being ingested. The BSON item is still in the queue as you can see in the ingeststats. However db.session.findOne() finds a document immediately since session is not marked as ingest:

> show collections
instance
session
system.ingest
> db.instance.count()
0
> db.session.count()
1
> db.system.ingest.find()
{"_id" : "instance", "allow_duplicate_ids":true, "buffer_size":NumberLong(1073741824)}
> db.runCommand({ingeststats: "instance"})
{
        "details" : [
                {
                        "bytes_in_queue" : NumberLong(92),
                        "items_in_queue" : NumberLong(1),
                        "bytes_ingested" : NumberLong(0),
                        "ingesting" : false,
                        "tid" : NumberLong(0),
                        "deleted" : NumberLong(0),
                        "mapped_size" : NumberLong(1048576),
                        "mapped_size_string" : "1024.000000 KB",
                        "last_flush_time" : ISODate("2017-10-19T20:27:43Z")
                },
                {
                        "bytes_in_queue" : NumberLong(0),
                ..... etc .....
                        "last_flush_time" : ISODate("2017-10-19T20:27:43Z")
                }
        ],
        "ok" : 1
}

> db.session.findOne()
{
        "_id" : ObjectId("59e90ac496cb257314181648"),
        "Severity" : NumberLong(5),
        "Login Message" : "select @@version_comment limit 1"
}

Note that the document fields and types for session are the same as for instance because we used the same Field Translations and "redact_unmatched_fields": true for both output_connections.

Collection Selectors

The collection_selectors section of an output_connection contains selection rules used to determine if the incoming event should be inserted into the default_collection. Sometimes the selector will dynamically determine a target collection instead of using the default_collection.

In all of the Hello SonarGateway examples we used select_by_key_value to route the BSON to a collection:

"default_collection": "instance",
"collection_selectors": {
        "select_by_key_value": {
                "cs2": "MSSQL",
        }
}

With this selector specified if the raw syslog text has the Key cs2 and Value MSSQL it will be inserted the BSON into the instance collection.

Open /etc/rsyslog.d/sonar/gateway/rulesets/helloworld.conf change the CONFIG_FILE to:

CONFIG_FILE=/etc/sonar/gateway/collection_selectors.json

Open /etc/sonar/gateway/collection_selectors.json and change all the sonar_URI fields to match the one you had in /etc/sonar/gateway/helloworld.json. Then check your configuration with:

$ sudo bash

$ /usr/lib/sonar/gateway/sonargateway –validate –config /etc/sonar/gateway/collection_selectors.json –delay_depot_dir /var/lib/sonar/gateway

You should see the following once you’ve fixed the sonar_URI fields:

[date] INFO parsing global settings

[date] INFO Successfully connected to default collection: all_sessions

[date] INFO Successfully connected to default collection: mssql_sessions

[date] INFO Successfully connected to default collection: oracle_sessions

[date] INFO Successfully connected to default collection: sessions_by_key

[date] INFO Output connections at array indexes 4, 5 are grouped by group_label: session_by_key_value

[date] INFO SonarGateway successfully validated config file at: /etc/sonar/gateway/collection_selectors.json

Now you’ve successfully validated the sonargateway_collection_selectors.json file let’s take a closer look at the file. The first two objects are global for all output_connection objects:

[
{
    "global_settings": {
    "sonar_URI": "mongodb://CN=admin@localhost:27117/admin? ... ",
    "target_db": "sonargateway",
    "do_not_ingest": [
        "all_sessions",
        "oracle_sessions",
        "mssql_sessions",
        "sessions_by_key"
    ]
    }
},
{
    "global_field_translations": {
    "msg": {
        "rename": "Greeting"
    },
    "app": {
        "rename": "Product"
    },
    "cs2": {
        "rename": "Database"
    }
    }
},
.....

We saw global_settings in the Redacting and Ingesting section. The global_field_translations are new. These are the same as the ones we saw before but they will be used by all output_connections. The rest of the file consists of JSON configuration we have seen before, except for the introduction of a one new Collection Selector: select_by_key, which takes an array of Key strings. If the raw syslog text has a Key that matches any in the array, the BSON will be inserted the BSON default_collection.

Here are excerpted parts of the configuration. The all_sessions output_connection will match any CEF formatted syslog since they are always specified to have a Device Vendor field.:

{
        "output_connection": {
                "default_collection": "all_sessions",
                "unique_label": "all_sessions",
                "group_label": "all_sessions",
                "collection_selectors": {
                        "select_by_key": [
                                "Device Vendor"
                        ]
                }
        }
}

The session_by_key_value_1 output_connection will match if the raw syslog text has the Key cs2 and Value MSSQL. It will be inserted the BSON into the mssql_sessions collection:

{
        "output_connection": {
                "default_collection": "mssql_sessions",
                "unique_label": "session_by_key_value_1",
                "group_label": "session_by_key_value",
                "collection_selectors": {
                        "select_by_key_value": {
                                "cs2": "MSSQL"
                        }
                }
        }
}

The session_by_key_value_1 output_connection will match if the raw syslog text has the Key cs2 and Value ORACLE. It will insert into the oracle_sessions collection:

{
        "output_connection": {
                "default_collection": "oracle_sessions",
                "unique_label": "session_by_key_value_2",
                "group_label": "session_by_key_value",
                "collection_selectors": {
                        "select_by_key_value": {
                                "cs2": "ORACLE"
                        }
                }
        }
}

The session_by_key output_connection will match if the raw syslog text has the Key cs2 or msg. It will insert into the sessions_by_key collection:

{
        "output_connection": {
                "default_collection": "sessions_by_key",
                "unique_label": "session_by_key",
                "group_label": "session_by_key",
                "collection_selectors": {
                        "select_by_key": [
                                "cs2",
                                "msg"
                        ]
                }
        }
}

There are other collection selectors, as you can see in the reference section below.

In the Sonar shell, drop the example database with:

> use sonargateway
switched to db sonargateway
> db.dropDatabase()
{ "dropped" : "sonargateway", "ok" : 1 }

Open a terminal and run:

$ sudo bash

$ systemctl restart rsyslog ## for the configuration settings to take effect

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=Guardium cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Running tail -f /var/log/sonar/gateway/sonargateway.log, you’ll see three of the four output_connections were triggered because their collection_selectors matched:

[date] INFO Successfully connected to default collection: all_sessions

[date] INFO Successfully connected to default collection: mssql_sessions

[date] INFO Successfully connected to default collection: oracle_sessions

[date] INFO Successfully connected to default collection: sessions_by_key

[date] INFO Output connections at array indexes 4, 5 are grouped by group_label: session_by_key_value

[date] WARNING SonarGateway Release v1.2.1-238-g919e1bc: Logging started/reconfigured, pid 27701

[date] INFO Creating a collection buffer in namespace sonargateway.all_sessions

[date] INFO Creating a collection buffer in namespace sonargateway.mssql_sessions

[date] INFO Creating a collection buffer in namespace sonargateway.sessions_by_key

[date] INFO Insert 1 bson to sonargateway.all_sessions

[date] INFO Insert 1 bson to sonargateway.mssql_sessions

[date] INFO Insert 1 bson to sonargateway.sessions_by_key

Three collections were created:

  1. all_sessions because the Device Vendor Key is present
  2. mssql_sessions because the cs2 Key has Value MSSQL
  3. sessions_by_key because the either the cs2 or the msg Key is present

In the Sonar shell let’s quickly check that we are heading in the right direction:

> use sonargateway
switched to db sonargateway
> show collections
all_sessions
sessions_by_key
mssql_sessions
> db.all_sessions.findOne()
{
    "_id" : ObjectId("595c36a2769f2b7705301756"),
    "Product" : "Guardium",
    "Database" : "MSSQL",
    "Greeting" : "select @@version_comment limit 1"
}

Using the group_label

Notice that the session_by_key Collection Selector matched the syslog in the previous example. That’s because it does not share a group_label with any of the previous ``output_connection``s.

SonarGateway stops processing collection_selectors in the same group_label once the first output_connection has matched. This can help prevent redundant data from being stored in SonarW. For the sake of this example, let’s give the session_by_key the same group_label as the all_sessions output_connection. Then restart the SonarGateway service, rerun the syslog input, and see what we get.

Open /etc/sonar/gateway/collection_selectors.json and change "group_label": "session_by_key" to "group_label": "all_sessions" and save the file.

In the Sonar shell, drop the example database with:

> use sonargateway
switched to db sonargateway
> db.dropDatabase()
{ "dropped" : "sonargateway", "ok" : 1 }

Open a terminal and run:

$ sudo bash

$ systemctl restart rsyslog ## for the configuration settings to take effect

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=MYSQL cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Now, running tail -f /var/log/sonar/gateway/sonargateway.log, you’ll see only two of the four output_connections were triggered:

[date] INFO parsing global settings

[date] INFO Successfully connected to default collection: all_sessions

[date] INFO Successfully connected to default collection: mssql_sessions

[date] INFO Successfully connected to default collection: oracle_sessions

[date] INFO Successfully connected to default collection: sessions_by_key

[date] INFO Output connections at array indexes 3, 6 are grouped by group_label: all_sessions

[date] INFO Output connections at array indexes 4, 5 are grouped by group_label: session_by_key_value

[date] WARNING SonarGateway Release v1.2.1-238-g919e1bc: Logging started/reconfigured, pid 1356

[date] INFO Creating a collection buffer in namespace sonargateway.all_sessions

[date] INFO Creating a collection buffer in namespace sonargateway.mssql_sessions

[date] INFO Insert 1 bson to sonargateway.all_sessions

[date] INFO Insert 1 bson to sonargateway.mssql_sessions

Two collections were created:

  1. all_sessions because the Device Vendor Key is present
  2. mssql_sessions because the cs2 Key has Value MSSQL

The sessions_by_key was not processed because all_sessions successfully matched and has the same group_label.

In the Sonar shell:

> show collections
all_sessions
mssql_sessions
> db.all_sessions.findOne()
{
        "_id" : ObjectId("59e923d296cb25054c07dd0c"),
        "Product" : "Guardium",
        "Database" : "MYSQL",
        "Greeting" : "select @@version_comment limit 1"
}
> db.mssql_sessions.findOne()
{
        "_id" : ObjectId("59e923d296cb25054c07dd0d"),
        "Product" : "Guardium",
        "Database" : "MYSQL",
        "Greeting" : "select @@version_comment limit 1"
}

The documents in the two collections have the same content because we used global_field_translations. You could customize the content for each collection if you moved those global_field_translations back into the individual output_connections. Before we move on to a more complicated example showing that kind of customization, let’s run two more inputs into the current configuration:

$ sudo bash

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 12:32:02 cs1=INFO cs1Label=Severity cs2=ORACLE cs2Label=Server Type app=Guardium cs4=10.0.0 cs4Label=DB Protocol Version sproc=ORACLE CLIENT act=ORACLE_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Via tail -f /var/log/sonar/gateway/sonargateway.log, you’ll see one new collection created and two flushed BSONs:

2017-10-19 22:20:25,392 INFO Creating a collection buffer in namespace sonargateway.oracle_sessions

2017-10-19 22:20:30,416 INFO Insert 1 bson to sonargateway.all_sessions

2017-10-19 22:20:30,449 INFO Insert 1 bson to sonargateway.oracle_sessions

The oracle_sessions collection is created via the select_by_key_value collection_selector in the output_connection with "unique_label": "session_by_key_value_2". Notice that this shares a group_label with another output_connection but because the previous one didn’t match, we normalize it and the new collection is created.

In the Sonar shell:

> show collections
all_sessions
mssql_sessions
oracle_sessions
> db.all_sessions.count()
2
> db.mssql_sessions.count()
1
> db.oracle_sessions.count()
1

Automatic Type Detection

You might have noticed in the Field Translations that we showed a type Translation for Severity which converted the string “5” to NumberLong(5). But there were other fields in the example that were converted automatically. Notice that Signature ID, destinationPort, deviceReceiptTime, startTime and others were converted to sensible types:

{
    "_id" : ObjectId("59e7ff0896cb25067166bff5"),
    "CEF Version" : 0,
    "Device Product" : "Guardium",
    "Device Vendor" : "IBM",
    "Device Version" : 10,
    "Name" : "Log all SQLs - Full SQL Template",
    "Severity" : "5",
    **"Signature ID" : NumberLong(20011),**
    "deviceAction" : "SQL_LANG",
    "applicationProtocol" : "MYSQL",
    "Server Type" : "MYSQL",
    "DB Protocol Version" : "10.0.0",
    **"destinationPort" : NumberLong(47741),**
    "destinationAddress" : "192.168.0.22",
    "destinationUserName" : "ROOT",
    "externalId" : NumberLong(-1),
    "message" : "select @@version_comment limit 1",
    **"deviceReceiptTime" : ISODate("2017-06-02T16:32:02.951Z"),**
    "sourceProcessName" : "MYSQL CLIENT",
    "sourcePort" : NumberLong(12160),
    "sourceAddress" : "192.168.0.22",
    **"startTime" : ISODate("2017-06-02T16:32:02.951Z")**
}

We do our best to infer and cache the type conversion for all input fields. You can always override the automatic type detection by explicitly specifying the type using a Field Translation. Sometimes this is necessary for integers being interpreted as epoch ISODates instead of plain integers.

Maybe the most important reason for Automatic Type Detection is help with the conversion of DateTime strings into ISODates. Many queries and analyses require correct ISODates.

In the Sonar shell, drop the example database with:

> use sonargateway
switched to db sonargateway
> db.dropDatabase()
{ "dropped" : "sonargateway", "ok" : 1 }

Open a terminal and run:

$ sudo bash

$ systemctl restart rsyslog ## for the configuration settings to take effect

## going to break the “start” formatting

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=MYSQL cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Notice that because we broke the format of the start field, the input is no longer automatically converted to an ISODate and instead is interpreted as a string:

> db.all_sessions.findOne().startTime
2017-06-02

Now edit the following file:

$ vi /etc/sonar/gateway/dt_fmt_strings

and add %Y-%m-%d to the end of the file so it looks like:

.....
.....
%Y-%m-%d %T
%Y-%m-%dT%H:%M:%S.%fZ
%Y-%m-%dT%T
%Y-%m-%d %H:%M:%S
%Y-%m-%d

In the Sonar shell, drop the example database again with:

> use sonargateway
switched to db sonargateway
> db.dropDatabase()
{ "dropped" : "sonargateway", "ok" : 1 }

Open a terminal and run:

$ systemctl restart rsyslog ## for the configuration settings to take effect

## now have a format string to match

$ echo “Jun 2 12:32:03 test_machine sqlguard[19870]: CEF:0|IBM|Guardium|10.0|20011|Log all SQLs - Full SQL Template|5|rt=1496421122951 start=2017-06-02 cs1=INFO cs1Label=Severity cs2=MYSQL cs2Label=Server Type app=MYSQL cs4=10.0.0 cs4Label=DB Protocol Version sproc=MYSQL CLIENT act=SQL_LANG externalId=-1 duser=ROOT dst=192.168.0.22 dpt=47741 src=192.168.0.22 spt=12160 msg=select @@version_comment limit 1” | ncat localhost 10512

Now we have an ISODate:

> db.all_sessions.findOne().startTime
ISODate("2017-06-02T00:00:00Z")

You can add as many date time format strings to /etc/sonar/gateway/dt_fmt_strings as you need, however you must restart the rsyslog service for those new format strings to be read by SonarGateway.

Sending logs from MsSql to SonarGateway

Configuring the SonarC host

  1. Add the MsSql data source to SonarGateway:

    sudo vi /etc/rsyslog.d/sonar/gateway/sonargateway.conf
    

Un-comment this line:

#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/mssql.conf
  1. Save and exit.

  2. Run:

    sudo systemctl restart rsyslog
    
  3. To verify:

    netstat -tpln | grep rsyslog
    

And look for port 10536.

Configuring the MsSql host

  1. Create MsSql audit to application, following MsSql documentation.
  2. Check what source-name is used for the MsSql logs, see Windows Log >> application, typically in Windows Event Viewer. Record this source-name.
  3. Install a syslog forwarder application, in this example we use NXlog. Install NXlog following the NXlog documentation, see http://nxlog.org/docs/
  4. Modify NXlog.conf, here is a sample file, use the specific syntax per the relevant NXlog version documentation:
##  nxlog.conf
## This is a sample configuration file. See the nxlog reference manual about the
## configuration options. It should be installed locally and is also available
## online at http://nxlog.org/docs/

## Please set the ROOT to the folder your nxlog was installed into,
## otherwise it will not start.

#define ROOT C:\Program Files\nxlog
define ROOT <replace with NXlog root>

Moduledir %ROOT%\modules
CacheDir %ROOT%\data
Pidfile %ROOT%\data\nxlog.pid
SpoolDir %ROOT%\data
LogFile %ROOT%\data\nxlog.log

<Extension _syslog>
    Module      xm_syslog
</Extension>

<Extension _json>
    Module    xm_json
</Extension>

<Input in>
    Module      im_msvistalog
## add here your sourcename from event viewer. Use double quotation around the name.
    Exec if $SourceName != "<use source-name from step #2 above>" drop();
    Exec $Hostname=hostname_fqdn(); $hostname_ip=host_ip(); to_json();
    Exec to_json();
</Input>

#Connect to syslog listener on sonarg host
<Output out>
    Module      om_tcp
## add your ip and port to send ms-sql logs to
    Host        <sonarg hostname or IP>
    Port        10536
    Exec to_json(); $Message = $raw_event; to_syslog_bsd();
#    Exec $raw_event = replace($raw_event, '{', ':{', 1);

</Output>

<Route 1>
    Path        in => out
</Route>
  1. restart nxlog : go to services.msc, Click on nxlog >> restart

Sending logs from Oracle to SonarGateway

Configuration on the SonarC host

  1. Add the Oracle data source to SonarGateway:

    sudo vi /etc/rsyslog.d/sonar/gateway/sonargateway.conf
    

Un-comment this line:

#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/oracle.conf
  1. Save and exit.

  2. Run:

    sudo systemctl restart rsyslog
    
  3. To verify:

    netstat -tpln | grep rsyslog
    

And look for port 10518.

Configuration on the Oracle host

  1. Connect to the oracle server (using a sql client), and set audit_trail to “os”
  2. Set the auditing level according to your requirements. Refer to the relevant Oracle documentation.
  3. configure rsyslog to send logs from a specific audit level to the SonarC machine.
  4. Restart rsyslog.
  5. Apply the updates to oracle.

Here is a specific example on Oracle 12.1.0:

  1. Login to Oracle server as admin user and set audit_trail.
SQL> startup;
SQL> show parameter audit;
    NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracle/admin/orcl/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

Here audit_syslog_level is not set and audit trail is set to DB To pass audit to syslog we have to set audit trail to OS, we define the change in a config file (spfile).

SQL> alter system set audit_trail=OS scope = SPFILE;
SQL> create pfile from spfile;

In order to get audit logs that would fit well into the SonarC system, we want to audit by access, for example:

SQL> audit all by access;
SQL> audit select any table by access;
SQL> audit update any table by access;
SQL> audit insert any table by access;
SQL> audit alter any table by access;
SQL> audit delete any table by access;

You need to restart the oracle server in order for the changes to take effect (example for Oracle-xe):

sudo systemctl restart oracle-xe
  1. Set the audit level, for example, info:

    sudo echo “*.audit_syslog_level=local0.info” >> /oracle/product/12.1.0/dbhome_1/dbs/initorcl.ora

  2. Configure rsyslog to send local0.info logs to a SonarC machine on IP 1.2.3.4 (replace with your SonarC host IP):

    sudo echo "local0.info  @@1.2.3.4:10518" >> /etc/rsyslog.conf
    
  3. Restart rsyslog:

    sudo systemctl restart rsyslog
    
  4. Apply updates to Oracle:

SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> startup;

SonarOracleGateway

This package is meant to be installed on a machine running oracle. It will copy Oracle XML log files to SonarC for further processing.

Setup

Prerequisite: Oracle package installed.

  1. Install the sonarOracleGateway package

After installing, you will need to configure the |product| remote destination. There are several steps to carry out on the oracle machine, and on the |product| machine.

On the Oracle Machine

  1. Edit the file /etc/sonar-oracle-gateway.conf. The commented values are the defaults - uncomment and modify as needed to match your installation.
  2. Set up ssh connectivity for rsync.

Create a file (or append to) ~oracle/.ssh/config, with the following contents:

  host sonarg
    hostname <sonarg hostname>
    user sonargd

Note: If you have modified the ``DEST`` parameter in
      ``/etc/sonar-oracle-gateway.conf``, make sure it matches
      ``~oracle/.ssh/config`` as well. The template above is based on the
      default values.

Copy (e.g. to the clipboard) the contents of the file ~oracle/.ssh/id_rsa.pub

On the SonarC Machine

Paste the contents of the file you copied in the last step into
/var/lib/sonargd/.ssh/authorized_keys. If the file does not exist, create it, if it does, append to it. Make sure it is owned by sonargd.sonar and that its permissions are 640. The containing .ssh directory should be owned the same, with permissions 700.

Change the shell for sonargd user to be bash:

  $ sudo usermod -s /bin/bash sonargd

This is required to allow connecting to the *|product|* machine using *rsync*
from the *Oracle* machine.
  1. Configure Sonargd to process the xml files. This will depend on the format of the XML file names and on the collection that you’ll want to route them to.

Edit sonargd.conf configuration file, typically found in the following path:

sudo vi /etc/sonar/sonargd.conf

There are two sections to edit, ‘plugins’ section to tell sonargd how to process the information in the files, and ‘misc-files’ section to route the processed information to the collection, giving example for Oracle 12c and routing the input to a collection called ‘oracle_logs’.

Insert / merge the following lines into sonargd.conf:

plugins:

default:

sonargdm

oracle_logs:

oracle
misc-files:
  • match: orcl_ora_[a-zA-z0-9] collection: oracle_logs
  1. Restart sonargd service to apply the change in configuration:

    sudo systemctl restart sonargd
    

Using SonarW to Send Syslog Messages

The underlying datastore, SonarW, has pieline operators that allow you to send data directly over syslog as well as projection operators that allow you to format JSON documents as LEEF.

As an example, if a collection has documents of the form:

> db.webservice.findOne({number: 'INC0000002'})
{
        "_id" : ObjectId("59cf96748a7597550015e40f"),
        "number" : "INC0000002",
        "IP Address" : "localhost",
        "SonarG Source" : "SonarDispatcher-Web-Service",
        "__status" : "success",
        "active" : true,
        "activity_due" : "2017-08-10 14:51:11",
        "approval" : "null",
        "assigned_to" : "Howard Johnson",
        "assignment_group" : "Network",
        "caller_id" : "Fred Luddy",
        "category" : "Network",
        "close_code" : "null",
        "cmdb_ci" : "FileServerFloor2",
        "comments" : "[date] - Administrator (comments)\nAdded an attachment\n\n",
        "comments_and_work_notes" : "[date]-[user](comments)\nAdded an attachment\n\n",
        "contact_type" : "null",
        "description" : "User can't get to any of his files on the file server.",
        "escalation" : "Overdue",
        "hold_reason" : "Awaiting Vendor",
        "impact" : "1 - High",
        "incident_state" : "On Hold",
        "knowledge" : false,
        "location" : "1050 Sunnyview Road Northeast, Salem,OR",
        "made_sla" : false,
        "notify" : "Do Not Notify",
        "opened_at" : ISODate("2017-05-04T16:07:12Z"),
        "opened_by" : "Joe Employee",
        "priority" : "1 - Critical",
        "problem_id" : "PRB0000007",
        "reassignment_count" : NumberLong(1),
        "section" : "web_service",
        "severity" : "1 - High",
        "short_description" : "Network file shares access issue",
        "sla_due" : "UNKNOWN"
}

and you want to send the contents over syslog in a LEEF format you can run:

db.webservice.aggregate(
        {$project:{'*':1}},
        {$out:{
                format:'leef',
                vendor:'default_vendor',
                product:'$SonarG Source',
                product_version:'$number',
                ignore_fields:["_id","SonarG Source","number"],
                value_replace:[{from:'\n',to:'\\n'}],
                fstype:'syslog',
                host:'localhost',
                loglevel:'notice',
                facility: 'user',
                protocol:'udp',
                port:10516
        }})

Producing:

Oct 13 13:27:39 localhost sonarw: LEEF:2.0|default_vendor|SonarDispatcher-Web-Service|INC0000002|59cf96748a7597550015e40f|IP Address=localhost#011__status=success#011active=true#011activity_due=2017-08-10 14:51:11#011approval=null#011assigned_to=Howard Johnson#011assignment_group=Network#011caller_id=Fred Luddy#011category=Network#011close_code=null#011cmdb_ci=FileServerFloor2#011comments=2017-08-03 16:13:23 - System Administrator (Additional comments)nAdded an attachmentnn#011comments_and_work_notes=2017-08-03 16:13:23 - System Administrator (Additional comments)nAdded an attachmentnn#011contact_type=null#011description=User can’t get to any of his files on the file server.#011escalation=Overdue#011hold_reason=Awaiting Vendor#011impact=1 - High#011incident_state=On Hold#011knowledge=false#011location=1050 Sunnyview Road Northeast, Salem,OR#011made_sla=false#011notify=Do Not Notify#011opened_at=2017-05-04T16:07:12#011opened_by=Joe Employee#011priority=1 - Critical#011problem_id=PRB0000007#011reassignment_count=1#011section=web_service#011severity=1 - High#011short_description=Network file shares access issue#011sla_due=UNKNOWN

Tab is the default delimiter. To specify a delimiter (e.g. an & delimiter) add delimiter: ‘&’.

SonarMaprGateway

The integration of MapR audit logs to sonargateway involves the following steps, some of them are manual and relate to your specific needs, some of them are automatic and being carried out by the sonar packages:

  1. Upgrade rsyslog.
  2. Set up the relevant audit in the MapR cluster.
  3. Mount the HDFS on one of the MapR nodes, or on a machine external to the cluster.
  4. Run expandaudit periodcly on one, or more of the MapR nodes.
  5. Periodically run rsync to copy the logs from the HDFS mount to a local filesystem. This is required for rsyslog monitoring.
  6. Run rsyslog to monitor the local file system for new log files.
  7. Add MapR data source to SonarC.

Upgrading rsyslog

Add rsyslog repository:

sudo cat <<'EOF' | sudo tee /etc/yum.repos.d/rsyslog.repo
[rsyslog_v8]
name=Adiscon CentOS-$releasever - local packages for $basearch
baseurl=http://rpms.adiscon.com/v8-stable/epel-7/$basearch
enabled=1
gpgcheck=0
gpgkey=http://rpms.adiscon.com/RPM-GPG-KEY-Adiscon
protect=1
EOF

Upgrade rsyslog:

sudo yum update rsyslog

Setting up audit on MapR

For up-to-date instructions, please refer to MapR documentation.

In general, in order to get audit logs from a MapR cluster, you need to set up auditing on three levels: cluster, volume and object, an object can be a table, or a folder that you want to audit. you can enable audit from the GUI, or using a CLI. The following examples will use a MapR cli.

  1. Cluster Level

To enable logging for cluster management operations:

maprcli audit cluster -enabled True

To enable filesystem and table operations:

maprcli audit data -enabled True

This only allows data operations logging in volumes.

  1. Volume Level

To enable actual logging per volume:

maprcli volume audit -name <volume> -enabled true <options>

To get a list of volumes:

maprcli volume list -columns volumename

To enable full auditing for all volumes:

for volume in $(maprcli volume list \
                -columns volumename | tail -n +2); do
  maprcli volume audit -name $volume -enabled true -coalesce 1 -dataauditops +all
done

Get info on the volume (verify that it is being audited):

maprcli volume info  -name <name> -json

Alternatively, use =hadoop mfs ls= command:

hadoop mfs -ls /

The output has three capital letters (right after the permissions) that correspond to Compression, Encryption and Auditing for each entry. ~A~ means enabled, ~U~ means disabled. If the third letter is ~A~ then auditing is enabled. Example:

[mapr@maprdemo ~]$ hadoop mfs -ls /|grep /tables
vrwxr-xr-x  Z U A   1 root root          1 2017-07-24 15:53  268435456 /tables

In this case, we can see that auditing is enabled.

  1. Object Level

To enable actual logging per directory/file/table:

hadoop mfs -setaudit on <directory|file|table>

to view all hadoop directories:

hadoop mfs -ls /

Note: setting audit on a hadoop directory can be non-recursive, so one needs to set audit for each directory one is interested on auditing.

So to set up auditing for all objects, one could run:

hadoop mfs -lsr / |awk '/rw/ {print $12}' | xargs -n 1 hadoop mfs -setaudit on

After this the auditing flag should be ~A~ for each directory/file/table in the system

Source: http://maprdocs.mapr.com/home/SecurityGuide/EnablingAuditing.html

Mounting HDFS

If you intend to install and run SonarMaprGateway on one of the MapR nodes, make sure that this node have ‘mapr-nfs’ service running and that the MapR file system is mounted on ‘/mapr’. If you intend to run SonarMaprGateway on a machine external to the cluster, you will need to mount the MapR file system to ‘/mapr’ folder, you can do that using ‘mapr-loopbacknfs’, see:

http://maprdocs.mapr.com/home/AdministratorGuide/c_POSIX_loopbacknfs_client.html

SonarMaprAgent

The basic MapR logs only contain references to users names, folder paths etc and not the actual names and paths. In order to get the needed details in the logs, we will expand them using the MapR expandaudit utility. Install the sonar-mapr-agent package on one or more MapR nodes, this package will setup a periodic cronjob on the node that will expand the logs.

sonar-mapr-gateway Package

Install this package on a machine that have the MapR file system mounted (see above). Update the target SonarC machine IP address and restart rsyslog for the change to take affect.

Install package:

sudo yum install sonar-mapr-gateway

Update the target SonarC machine IP:

sudo vi /etc/rsyslog.d/sonar-mapr-gateway.conf

Replace “SonarG IP” with the IP address of your SonarC machine.

Restart rsyslog:

sudo systemctl restart rsyslog

This will do two things:

a. Add periodic rsyncs of the audit files from MapR file system to local file system.
b. Setup rsyslog service to monitor the local file system folder and to send the
data to SonarGateway on the |product| machine.

Note: you need to make sure that you have an open connection from the host where sonar-mapr-gateway is installed to the SonarC host, configure your firewall etc. accordingly.

Add MapR data source on the SonarC Machine

  1. Add the MapR data source to SonarGateway:

    sudo vi /etc/rsyslog.d/sonar/gateway/sonargateway.conf
    

Un-comment this line:

#$IncludeConfig /etc/rsyslog.d/sonar/gateway/rulesets/oracle.conf
  1. Save and exit.

  2. Run:

    sudo systemctl restart rsyslog
    
  3. To verify:

    netstat -tpln | grep rsyslog
    

And look for port 10530.

Creating Secure SonarGateway Connections

This section describes how to set up Transport Layer Security (TLS) connections between machines on an internal corporate network and a SonarGateway running SonarGateway. The authentication here is two-way. The sender authenticates with the receiver, and vice-versa. Communication is encrypted and can be optionally compressed.

Assume we have three machines:

  1. master – this machine generates certificates and is not on the same network as the other machines.
  2. sonargateway – this machine receives events. It is external to the corporate network and is installed as part of SonarC.
  3. agent – this machine forwards events to sonargateway. It is internal to the corporate network.

In the following we assume that master, sonargateway, and agent are RedHat machines. Also, the instructions work for multiple agent machines. You can copy the same agent certificates (if that suits your security needs) to multiple agent machines and they will authenticate in the same fashion.

For this example we use the standard syslog ports:

  1. Port 6514 as the TLS secured port on all machines
  2. Port 514 as the unsecured port on the agent machines that are internal to the corporate network.

Certificate Creation

We need to create certificates on the master to be used by sonargateway and the agent. On the master:

$ sudo yum install -y gnutls-utils rsyslog-gnutls

On the master we generate a private key and a self-signed CA certificate. You will be asked some questions during the certificate generation. For this example we chose some simple values and used mostly defaults. Configure the certificate generation to suit your organization:

$ certtool --generate-privkey --outfile ca-key.pem
Generating a 2048 bit RSA private key...
$ certtool --generate-self-signed --load-privkey ca-key.pem --outfile ca.pem
Generating a self signed certificate...
Details of the certificate's distinguished name. Press enter to ignore a field.
Common name: SonarGateway
.....
State or province name: BC
Country name (2 chars): CA
.....
The certificate will expire in (days): 4000
Extensions.
Does the certificate belong to an authority? (y/N): y
.....
Enter a dnsName of the subject of the certificate: SonarGateway
.....
Enter the e-mail of the subject of the certificate: sonargateway@jsonar.com
.....
Will the certificate be used to sign other certificates? (y/N): y
.....
.....
.....
Other Information:
        Public Key ID:
                cf5168551caeda4d2ea28f873ed924e74a76208c
        Public key's random art:
                +--[ RSA 2048]----+
                |            .oo. |
                |           o ..  |
                |          o . .  |
                |     o   . . .   |
                |    E o S . . .  |
                |       ..+o+ +   |
                |        oO* o o  |
                |       o+++. .   |
                |       .==.      |
                +-----------------+

Is the above information ok? (y/N): y
Signing certificate...
$ chmod 400 ca-key.pem
$ ls -l
total 12
-r--------. 1 root root 5823 Oct 20 13:31 ca-key.pem
-rw-r--r--. 1 root root 1216 Oct 20 13:37 ca.pem

The security of the TLS connections depends on the security of ca-key.pem. If this file is not secured, the system is compromised.

Now we generate certificates for the sonargateway and agent machines, similar to the CA certificate. The key here is the Common name setting. We will have to refer to these names later when we configure rsyslog.

Repeat this section twice – once for the sonargateway machine and once for the agent machines. Again, we are assuming that the agent machines can share certificates:

$ certtool --generate-privkey --outfile key.pem
Generating a 2048 bit RSA private key...
$ certtool --generate-request --load-privkey key.pem --outfile request.pem
Generating a PKCS #10 certificate request...
Common name: sonargateway ###### <----- 2nd time use 'agent' ######
Organizational unit name:
Organization name:
Locality name:
State or province name: BC
Country name (2 chars): CA
Enter the subject's domain component (DC):
UID:
Enter a dnsName of the subject of the certificate:
Enter a URI of the subject of the certificate:
Enter the IP address of the subject of the certificate:
Enter the e-mail of the subject of the certificate:
Enter a challenge password:
Does the certificate belong to an authority? (y/N): N
Will the certificate be used for signing (DHE and RSA-EXPORT ciphersuites)? (Y/n):
Will the certificate be used for encryption (RSA ciphersuites)? (Y/n):
Is this a TLS web client certificate? (y/N): y
Is this a TLS web server certificate? (y/N): y
Self signature: verified

$ certtool --generate-certificate --load-request request.pem --outfile cert.pem
--load-ca-certificate ca.pem --load-ca-privkey ca-key.pem

Generating a signed certificate...
.....
The certificate will expire in (days): 4000
.....
Is this a TLS web client certificate? (y/N): y
.....
Is this a TLS web server certificate? (y/N): y
.....
.....
Other Information:
        Public Key ID:
                12af4f2b06018fc1f114a030f763385f1eeed463
        Public key's random art:
                +--[ RSA 2048]----+
                |o.+oo.           |
                |.++=             |
                |. o== +          |
                |  .+o= =         |
                |    ..= E        |
                |    .o + .       |
                |     .o .        |
                |      oo .       |
                |     . .o        |
                +-----------------+

Is the above information ok? (y/N): y
Signing certificate...

Now rename the two generated files:

$ mv cert.pem sonargateway-cert.pem ###### <---- 2nd time, to agent-cert.pem #######
$ mv key.pem sonargateway-key.pem ###### <---- 2nd time, to agent-key.pem #######

Then copy the sonargateway*.pem and ca.pem files to the sonargateway machine and the agent*.pem and ca.pem to the agent machine(s). The commands can look something like this:

## if sonargateway machine IP is 54.183.244.39...
$ scp -i ~/.ssh/aws.pem {sonargateway*,ca}.pem ec2-user@54.183.244.39:
## if agent machine IP is 52.53.165.0, etc.
$ scp -i ~/.ssh/aws.pem {agent*,ca}.pem ec2-user@52.53.165.0:

On sonargateway and agent machines, move the files, restrict permissions, and set a selinux type. By using semanage we can keep selinux ‘Enforcing’:

$ sudo bash
$ mkdir -p /etc/rsyslog.d/sonargateway/tls
$ mv *pem /etc/rsyslog.d/sonargateway/tls
$ chmod 400 /etc/rsyslog.d/sonargateway/tls/*.pem
$ chown root:root /etc/rsyslog.d/sonargateway/tls
$ chown root:root /etc/rsyslog.d/sonargateway/tls/*
$ semanage fcontext -a -t syslog_conf_t "/etc/rsyslog.d/sonargateway/tls(/.*)?"
$ restorecon /etc/rsyslog.d/sonargateway/tls/*

Configuring the SonarGateway machine

Add the following lines to /etc/rsyslog.d/sonargateway.conf on the sonargateway machine:

$ cat >> /etc/rsyslog.d/sonargateway.conf <<'EOF'

global(defaultNetstreamDriverCAFile="/etc/rsyslog.d/sonargateway/tls/ca.pem"
  defaultNetstreamDriverCertFile="/etc/rsyslog.d/sonargateway/tls/sonargateway-cert.pem"
  defaultNetstreamDriverKeyFile="/etc/rsyslog.d/sonargateway/tls/sonargateway-key.pem")
EOF

And the following ruleset:

$ cat >> /etc/rsyslog.d/sonargateway/rulesets/tls_test.conf <<'EOF'
template(name="tls_test" type="list") {
        constant(value=" { ")
        property(format="jsonf" outname="Source Machine" name="hostname")
        constant(value=",")
        property(format="jsonf" outname="Timestamp" name="timestamp"...
        ...dateFormat="unixtimestamp")
        constant(value=",")
        property(format="jsonf" outname="Message" name="msg")
        constant(value=",")
        property(format="jsonf" outname="Facility" name="syslogfacility-text")
        constant(value=",")
        property(format="jsonf" outname="Severity" name="syslogseverity-text")
        constant(value=",")
        property(format="jsonf" outname="Program Name" name="programname")
        constant(value=" } ")
        constant(value="\n")
}

ruleset(name="6514_tls_test") {
    action(type="omfile" file="/tmp/6514_tls_test.txt" template="tls_test")
    stop
}

input(type="imtcp" port="6514" ruleset="6514_tls_test")
EOF

Then modify the input modules:

$ sed -i '/imtcp/d' /etc/rsyslog.d/sonargateway/include/modules.conf
$ cat >> /etc/rsyslog.d/sonargateway/include/modules.conf <<'EOF'
module(load="imtcp"
    streamdriver.name="gtls"
    streamdriver.mode="1"
    streamdriver.authmode="x509/name"
    permittedpeer=["agent"]
    keepalive="on"
)
EOF

Configuring the Agent machine(s)

On the agent machine(s), set sonargateway as the permitted peer. Notice that this permitted peer is different from the target in the omfwd ruleset. The target is the public IP of the sonargateway machine.

$ cat >> /etc/rsyslog.d/sonargateway.conf <<'EOF'

global(defaultNetstreamDriverCAFile="/etc/rsyslog.d/sonargateway/tls/ca.pem"
    defaultNetstreamDriverCertFile="/etc/rsyslog.d/sonargateway/tls/agent-cert.pem"
    defaultNetstreamDriverKeyFile="/etc/rsyslog.d/sonargateway/tls/agent-key.pem")

EOF

$ cat >> /etc/rsyslog.d/sonargateway/rulesets/tls_test.conf <<'EOF'
ruleset(name="6514_tls_test") {
action(type="omfwd"
    keepalive="on"
    streamdriver="gtls"
    streamdrivermode="1"
    streamdriverauthmode="x509/name"
    streamdriverpermittedpeers="sonargateway"
    ziplevel="9"               ## remove for uncompressed
    compression.mode="single"  ## remove for uncompressed
    protocol="tcp"
    target="54.183.244.39"     ## the address of the sonargateway machine
    port="6514")
stop
}

input(type="imptcp" port="514" ruleset="6514_tls_test")
EOF

Note the ziplevel and compression.mode parameters in the omfwd action above. These enable per-message (not stream) compression. The maximum compression is ziplevel 9 and no compression is ziplevel 0. The amount of compression varies depending on message length. Short messages may not be compressed at all. Compression will kick in for longer messages depending on how redundant the information is. As informal (and conservative) tests you can try something like the following bash one-liners from the master machine to check compression at ziplevel 9. These commands roughly simulate messages of length 2kb. You can use iptraf-ng to read the byte counts at the agent (uncompressed) and at the sonargateway machine (compressed):

$ for x in {1..100}; do for y in {1..10}; do logger -t RandomStuff -p user.warn cat /dev/urandom | tr -dc ‘a-zA-Z0-9’ | fold -w 2048 | head -n 1; done; sleep 1; done

$ for x in {1..100}; do for y in {1..10}; do logger -t RandomStuff -p user.warn shuf -n250 /usr/share/dict/words | paste -d, -s; done; sleep 1; done

In the first example we’re sending random characters. We see a compression ratio of ~1.25X for the random case. In the second example we’re sending random words from a dictionary. We expect more redundancy here and see that with a compression ratio of ~1.6X.

Validating the Connection

On sonargateway:

$ systemctl restart rsyslog
$ yum install -y lsof
# should see something like the following on sonargateway
$ lsof -i :6514
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
rsyslogd 9688 root    6u  IPv4  36936      0t0  TCP *:syslog-tls (LISTEN)
rsyslogd 9688 root    7u  IPv6  36937      0t0  TCP *:syslog-tls (LISTEN)
$ touch /tmp/6514_tls_test.txt
$ tail -f /tmp/6514_tls_test.txt

On the agent we can check the connection by writing to port 514 locally:

$ yum install -y nmap-ncat
$ echo "<83>Oct 21 15:57:38 agent agetty[79106]: Hello from Agent" | nc localhost 514
## if you installed more than one agent, run this on the other agent
$ echo "<83>Oct 21 15:57:38 agent agetty[79106]: Hello from Agent 2" | nc localhost 514

You should see on the sonargateway machine via tail -f /tmp/6514_tls_test.txt:

{ “Source Machine”:”agent”,”Timestamp”:”1508601458”,”Message”:” Hello from Agent”,”Facility”:”authpriv”,”Severity”:”err”,”Program Name”:”agetty” } { “Source Machine”:”agent”,”Timestamp”:”1508601458”,”Message”:” Hello from Agent 2”,”Facility”:”authpriv”,”Severity”:”err”,”Program Name”:”agetty” }

If multiple agents have connections and you go back to the sonargateway machine, you can see each connection using lsof:

$ lsof -i :6514
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
rsyslogd 9688 root    6u  IPv4  36936      0t0  TCP *:syslog-tls (LISTEN)
rsyslogd 9688 root    7u  IPv6  36937      0t0  TCP *:syslog-tls (LISTEN)
rsyslogd 9688 root   10u  IPv4  37005      0t0  TCP [ip]:syslog-tls->[dns]:42664
rsyslogd 9688 root   12u  IPv4  37011      0t0  TCP [ip]:syslog-tls->[dns]:58630

Let’s assume that you have rsyslog installed on the master machine that created the certificates and that this machine can access two agent machines at 54.153.107.32 and 52.53.165.0. Append the following lines to the end of /etc/rsyslog.conf (you’ll want to remove them afterwards):

module(load="imuxsock") # provides support for local system logging
module(load="imklog")   # provides kernel logging support (previously done by rklogd)
$WorkDirectory /var/lib/rsyslog # where to place spool files
$ActionQueueFileName fwdRule1 # unique name prefix for spool files
$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
$ActionQueueType LinkedList   # run asynchronously
$ActionResumeRetryCount -1    # infinite retries if host is down
*.* @@54.153.107.32:514
*.* @@52.53.165.0:514

Run the following commands on master:

1. $ sudo systemctl restart rsyslog
2. $ sudo bash
3. $ logger -t SomeProgram -p user.notice "Called without required number of parameters"
4. $ logger -t UnAuthProgram -p auth.warning "Tried to access some restricted resource"

Return to the sonargateway machine and the tail -f /tmp/6514_tls_test.txt. You should see four lines one for each of the commands above with each duplicated since rsyslog is forwarding to two agents:

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imtcp: module loaded, but no listeners defined - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imfile: no files configured to be monitored - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” [origin software=”rsyslogd” swVersion=”8.30.0” x-pid=”9529” x-info=”http://www.rsyslog.com”] start”,”Facility”:”syslog”,”Severity”:”info”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imudp: module loaded, but no listeners defined - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imtcp: module loaded, but no listeners defined - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imudp: module loaded, but no listeners defined - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” imfile: no files configured to be monitored - no input will be gathered [v8.30.0 try http://www.rsyslog.com/e/2212 ]”,”Facility”:”syslog”,”Severity”:”err”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952269”,”Message”:” [origin software=”rsyslogd” swVersion=”8.30.0” x-pid=”9529” x-info=”http://www.rsyslog.com”] start”,”Facility”:”syslog”,”Severity”:”info”,”Program Name”:”rsyslogd” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952270”,”Message”:” SELinux is preventing /usr/sbin/rsyslogd from name_bind access on the tcp_socket port 10558. For complete SELinux messages. run sealert -l 78d7d7ec-2c88-4947-acdf-c6fbe1beed08”,”Facility”:”user”,”Severity”:”err”,”Program Name”:”setroubleshoot” }

From Command 1. –> { “Source Machine”:”derek”,”Timestamp”:”1508952270”,”Message”:” SELinux is preventing /usr/sbin/rsyslogd from name_bind access on the tcp_socket port 10558. For complete SELinux messages. run sealert -l 78d7d7ec-2c88-4947-acdf-c6fbe1beed08”,”Facility”:”user”,”Severity”:”err”,”Program Name”:”setroubleshoot” }

From Command 2. –> { “Source Machine”:”derek”,”Timestamp”:”1508952313”,”Message”:” derek : TTY=pts/34 ; PWD=/home/derek/src/ca ; USER=root ; ENV=LD_LIBRARY_PATH=/opt/rh/devtoolset-6/root/usr/lib64:/opt/rh/devtoolset-6/root/usr/lib::/usr/local/lib:/usr/local/lib PATH=/usr/lib/ccache:/opt/rh/devtoolset-6/root/usr/bin:/usr/lib/ccache:/usr/lib64/qt-3.3/bin:/usr/lib64/ccache:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/derek/.local/bin:/home/derek/bin:/usr/local/go/bin:/usr/local/go/bin ; COMMAND=/bin/scl enable devtoolset-6 ‘bash’”,”Facility”:”authpriv”,”Severity”:”notice”,”Program Name”:”sudo” }

From Command 2. –> { “Source Machine”:”derek”,”Timestamp”:”1508952313”,”Message”:” derek : TTY=pts/34 ; PWD=/home/derek/src/ca ; USER=root ; ENV=LD_LIBRARY_PATH=/opt/rh/devtoolset-6/root/usr/lib64:/opt/rh/devtoolset-6/root/usr/lib::/usr/local/lib:/usr/local/lib PATH=/usr/lib/ccache:/opt/rh/devtoolset-6/root/usr/bin:/usr/lib/ccache:/usr/lib64/qt-3.3/bin:/usr/lib64/ccache:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/derek/.local/bin:/home/derek/bin:/usr/local/go/bin:/usr/local/go/bin ; COMMAND=/bin/scl enable devtoolset-6 ‘bash’”,”Facility”:”authpriv”,”Severity”:”notice”,”Program Name”:”sudo” }

From Command 3. –> { “Source Machine”:”derek”,”Timestamp”:”1508952361”,”Message”:” Called without required number of parameters”,”Facility”:”user”,”Severity”:”notice”,”Program Name”:”SomeProgram” }

From Command 3. –> { “Source Machine”:”derek”,”Timestamp”:”1508952361”,”Message”:” Called without required number of parameters”,”Facility”:”user”,”Severity”:”notice”,”Program Name”:”SomeProgram” }

From Command 4. –> { “Source Machine”:”derek”,”Timestamp”:”1508952408”,”Message”:” Tried to access some restricted resource”,”Facility”:”auth”,”Severity”:”warning”,”Program Name”:”UnAuthProgram” }

From Command 4. –> { “Source Machine”:”derek”,”Timestamp”:”1508952408”,”Message”:” Tried to access some restricted resource”,”Facility”:”auth”,”Severity”:”warning”,”Program Name”:”UnAuthProgram” }

Event Format Parameters

Some event formats have parameters.

The JSON event format can be told not to be strict about using the backslash character as an escape, allowing malformed JSON string such as “newhostuser” to be used. The default of the “escaping” parameter is true, which maeans strict escaping. By specifying false, you can relax the requierments from input strings:

"event_format": {
        "standard": "JSON",
        "escaping" : false
}

SonarLogstash

As a rule of thumb SonarGateway is around one order of magnitude faster and more scalable than Logstash so for new SonarG/C implementations we recommend using SonarGateway and rsyslog rather than Logstash. However, if you have already made an investment in Logstash infrastructure this section provides instructions on how to add SonarC as a target source to receive logs through Logstash.

You can integrate any of your Logstashes directly with SonarC or forward from your Logstash agents to a centralized Logstash process living on the SonarC machine or another machine. Follow the instructions below for any Logstash that will be writing directly to SonarW.

First, place the sonarw.gem module in your logstash modules directory. On redhat 7 system, with Ruby version 1.9, systems the path is /share/logstash/vendor/bundle/jruby/1.9.

The logstash config file will contain lines similar to these, where all log lines go to elasticsearch server on port 9200:

output {
  elasticsearch { hosts => ["localhost:9200"] }
}

Sometime different log lines go to different output modules. In the example below, apache logs are directed to ElasticSearch server on port 9200, while other logs are directed to Nagios:

output {
  if [type] != "apache" {
          nagios { }
    } else {
         elasticsearch { hosts => ["localhost:9200"] }
   }
  }
}

In order to redirect log messages to go to SonarC instead of (or in addition to ) ElasticSearch, replace (or add ) the lines defining the elasticsearch output with lines defining SonarC output. For example, the first definition above should read:

output {
  sonarw {
    collection => "apache_logs"
    database => "syslogs"
    uri => "mongodb://user:pass@localhost:27117/?authenticationSource=admin"
}

And the second example should be:

output {
  if [type] != "apache" {
          nagios { }
    } else {
         sonarw {
            collection => "apache_logs"
            database => "syslogs"
            uri => "mongodb://user:pass@localhost:27117/?authenticationSource=Admin"
        }
    }
  }
}

Note that you need to allow the SonarW component within SonarC to receive connections from your Logstash sources; consult your SonarC account manager on how to do this.

Configuration file Reference

Any event can be sent (traditionally, using syslog protocol) to a SonarC system to be stored and queried. However, events need to be transformed and directed to the desired database collection. You configure SonarGateway to both direct incoming events into the SonarC storage system and reshape them in terms of data types and filtering. You can filter whole messages or redact fields in the message. You can also calculate alternative fields values or add new fields, saving time when you query for the message.

Events can come in many formats. SonarGateway always convert events to a list of fields and their values, whatever the format is - JSON, LEEF, CSV, XML and others. You configure SonarGateway to examine specific fields and their values to decide how to store the data. Field values can by themselves be a list of field/value pairs or arrays, allowing a faithful representation of structured formats such as JSON and XML.

The SonarC system always runs the rsyslog daemon. The configuration files in /etc/rsyslog.d/sonargateway instruct it to run a SonarGateway service instance once events come in a given TCP connection, and feed any incoming data to that instance. The instance runs as long as the rsyslogd service runs. The SonarGateway instance, configured by JSON-formatted files in /etc/sonar/sonargateway, parses the events coming to it from rsyslogd, filters them, reshapes them, and send them to SonarW databases and collections.

In the text below, we use the word “Message” to denote an rsyslogd-sourced line of text, “Event” to denote a logical Event from an application, an OS or a network appliance, and “Field” to denote the components of the Event. Note that one event, and even one field, can be large enough to span multiple rsyslogd messages. The configuration files specify how to handle these “multiline” events scenario.

Command Line Parameters

sonargateway is normally invoked directly by rsyslogd that runs on SonarC system. It is not run by a user. It has the following command line parameters:

—config arg – configuration file (required). This is the only required parameter.

Other optional parameters are:

--help Produce help message
--version Display version number and exit
--verbose Display verbose debug information when running.
--validate Validate the configuration file, and exit.
--flush_interval arg
 How often to flush data to sonarW.
--stats_interval arg
 How many seconds between sending operating statistics to sonarW to database sonar_log , collection sonargateway.
--delay_depot_dir arg
 Directory to store delayed messages
--input_delimiter arg
 A string marking the end of rsyslogd-provided message

Configuration File

A sonargateway instance is configured with a configuration file in /etc/sonar/sonargateway. Rsyslogd runs the instances and provides the configuration file upon the arrival of the first event over the network. Under heavy load, rsyslog may start multiple instances to handle the same event source, and the same configuration file will be used.

General Structure

The configuration file is a JSON Objects Array. The following type of array members are present:

Global Setting - parameters regarding all of the events handled by the instance.

Global Fields - Instruction how to transform fields that are always generated in the database object, regardless of what the event was.

Common Fields - For convenience, if a field is present at many, but not all of the events, the field transformation definition can be specified in this section and used later when handling a specific event type.

Rule Set - This section can appear repeatedly. It describes how to handle an event - what fields to expect, how to tranform them and which Database and collection they should go into.

Field Translation

If a field appears in a message and you want it to be present as a string in the stored message in the database, you don’t need to declare any translation. By default, all fields of an event are written to the database. However, you may want to remove, rename, redact, concatenate, check, and do other transformation on the field. You may also want to generate a new field that does not exist in the event. The field transformation event allow you to do that.

The general format of a transformation is:

"Field-Name" : { "Attribute": "value", "Attribute": "value",  ...}

For example:

"ETime": { "type": 9, "Rename": "Event Time" }

This means that the the “ETime” field in the event should be stored in the Database as a date and named “Event Time” and not “Start Time”.

Here is a list of all possible field transformation attributes:

  1. type

Try to convert the field to the specified numeric BSON types. The list of supported BSON types:

  • Double Precision - 1
  • String - 2
  • Boolean - 8
  • Date - 9
  • Null - 10
  • 32-bit integer-16
  • 64-bit integer-18
  1. date_format

Specify the date format to be used to convert the field test into a date. Using this attributes automatically implies specifying “type: 9”. The format string is according to POSIX.1-2001 strptime() definition. For example:

"global_timestamp": {
  "date_format": "%Y-%m-%d %T"
}
  1. Rename

Save the field in the DB under a different name. For example:

“ETime”: {
     “type”: 9,
     “Rename”: “Event Time”
}

Means that although the event contains a field called “ETime”, in the SonarW DB the Event field will be called “Event Time”.

  1. generate

Sometimes you want to generate a new field in the Event, if it doesn’t appear. For example:

“Sender”: {
     “generate”: true,
     “default” :  “FireWall Monitor”
}

This means that if the field “Sender” does not appear in the event, a field will be generated with the value given.

Note the value type is boolean, so you should not use quotes around the true of false value. Specifying false does not have any effect on the final stored event.

  1. null

Set the string value that represent a null. For example, if you want the value of the field “Sender” to be stored as null if the events contains “N/A” , use:

“Sender”: {
     “null”:”N/A”
}

If an incoming event has “N/A” as an empty string, the stored event will contain the null DB type.

  1. default

Set the default value if the event contains an empty value. This will not have an effect if the field is completely missing. If you want the field to appear in all cases, add the “generate” attribute as shown above.

For example:

“Sender”: {
     “generate”: true,
     “default” :  “FireWall Monitor”
}

If an incoming event has “Sender” as an empty string, the stored event will contain “FireWall Monitor”.

  1. redact

Redact the field in the DB Message. For example:

“ETime”: {
     “redact”: true,
}

Means that although the event contains a field called “ETime”, it will not be stored in the DB.

  1. extract

Sometime a field can contain further fields. For example, in events generated by MS SQL server, the field “Message” contains further event fields. For example:

"Message" : {
    "redact": true ,
    "extract": "MSSQL"
},

If the Event contains a field called “Message”, the field itself will not be stored in the SonarW database (because of the redact attribute) but it will be parsed according the MS SQL format, and the contained fields will be added to the message.

In addition, some fields can contain a text that can imply a set of one or more key-value pairs, and you want that key to be a field in the message with the associated value. Specify a regular expression with an even number of capture groups to grab the field names and their values from the message. For example:

"Message" : {
    "redact": true ,
    "extract": ".*\\[(CLIENT): (.*)\\]"
},

If the Event contains a field called “Message”, with the value

“Login failed for user ‘WIN-K\Joe’. Connection made using authentication. [CLIENT: 73.5.4.12]”

A new field called “CLIENT” will be generated in the database with the value “73.5.4.12”. Note: Be careful specifying the field name capture group; a mistake can lead to a very large number of field names instead of the one you want. The best practice is to use a capture group containing the field name as in this example.

NOTE: Fields generated by this attributes are normal fields for all purposes and you can define a transformation for them.

  1. update

Sometimes you want to update a document in SonarW instead of creating a new one. To update a document, a key field must be specified by using the “update” attribute. For example:

"SessionID" : {
    "update": true
}

The SessionID field of the event is used as a key to select which document in the SonarW collection to update. The values of all the other fields in the Events will be used to update the respective values in the SonarW collection. If a an event with this ID does not exist, a new record will be created.

Note the collection that is the target of the Event must not be ingested one. See the “do_not_ingest” global parameter.

  1. set_on_insert

Continuing the case of “update” above, sometimes you would like a field value to remain constant even if the SonarW records gets updated. Use the set_on_insert to mark an event field to be stored once and never change. For example:

"Original Arrival Time" : {
    "set_on_insert": true,
    "eval" : "$eventTime"
}
  1. eval

Sometimes it is necessary to calculate new field values, or completely new fields. There are several functions you can call and their return values will be the value for the field. The list of evaluators and examples for them are defined in the next section. However here are some basic principles.

One way to use an evaluator is to generate a new field based on existing ones:

“Full Name” : {
  “generate”: true,
  “eval” : [“concat”,”$First Name”, “ ”,“$Last Name”]
}

The field “Full Name” will be in the stored event, and it will be a combination of the values of “First Name” and “Last Name”.

Another way is to modify a field value, if already exist; and if not, the field will not be in the message:

“Client Address” : {
  “eval” : [“IP_address”,”$Client Address”]
}

This will try to convert the value of the field Client Address into an IP address, if not already one.

The eval attribute value is either a string, which is the name of the function to call (e.g. “eval”: “now” ) or an array of strings if the function has parameters ( e.g. “eval” : [“hostname”, “$Client IP”] ).

Evaluators

  1. Copy

Copy the value of a field into another field:

“Client IP” : {
  “generate” : true,
  “eval” :“$Source IP”
}

The field “Client IP” will be in generated, and it will be have the same value and type of the “Source IP” field.

  1. rand

Generate a 64 bit random number. This is useful when a key in a collection needs to be unique. You can use the type attribute to force double precision or 32-bit number instead of 64 bit number. For example:

"Session ID": {
  "generate": true,
  "eval": “rand”
  ]
}
  1. “now”

Returns the current time. For example:

"Event Arrival Time": {
  "generate": true,
  "eval": "now"
  ]
}
  1. IP_address

Return an IP address as a string, given a list of event fields. The value of the first field that is either an IP address or a resolvable host name is returned. For example:

"Client IP Address" : {
  "generate": true,
  "eval": [ "$IP_address" , "$ClientHost" , "127.0.0.1" ]
}

Suppose IP_address field is suppose to contain an IP address. If this is the case, the “Client IP Address” field in the SonarW document will contain that address. If the field is missing or empty, sonargateway will try to resolve the value in ClientHost into an IP address. If that fails, the value used will be “127.0.0.1”.

  1. concat

Concatenate several fields values and constant values into one. For example:

"Full Name" : {
  "generate": true,
  "eval": [ "$First Name" , " " , "$Last Name" ]
}

The value of the field “Full Name” in the SonarW document will be the value of the event field “First Name”, a space, and the value of “Last Name”.

  1. ifeq

Choose a value to be stored based on comparing two values, connstants or event field values (or a mix). Form example:

"Operation Permitted": {
  "generate": true,
  "eval": [ "ifeq", "allowed", "true", "1", "0" ],
  "type": 18
}

The value of the field “Operation Permitted” in the SonarW document will be 1 , as a 64 bit number, if the value of the message field “allowed” is “true” , or 0 otherwise.

  1. ifempty

Choose a value to be stored based on checking if a field is empty or not. For exmaple:

"Succeeded": {
  "generate": true,
  "eval": [ "ifempty", "sql-error", "1", "0" ]
}

The value of the field “Succeeded” in the SonarW document will be 1 , as a 64 bit number, if the field “sql-error” is empty or there was no such field, or 0 otherwise.

  1. hostname

This is the reverse of the IP_address evaluator. Return an FQDN, given a list of event fields. The value of the first field that is a resolvable FQDN or an IP address that is resolvable to an FQDN is returned. For example:

"Client Host Name" : {
  "generate": true,
  "eval": [ "$ClientHost",  "$IP_address" , "localhost.localdomain" ]
}
  1. errno

Converts a numeric UNIX error code into a string with the error message. For example:

"Exception Description": {
  "generate": true,
  "eval": [ "errno",  "$status"]
  ]
}

The field “Exception Description” will be added to the event, and it will contain the error message for the error code in the “status” field of the message.

  1. oracle_error

Converts an Oracle error code into a string with the error message. For example:

"Database Error Text": {
  "generate": true,
  "eval": [ "oracle_error", "RETURNCODE" ]
},
  1. uniqueId

This feature is used to enable several events coming in over time to have a unique numerical ID common to them which depends on a set of fields that exists in all of them. This helps with issuing sonarW queries and report that use this ID as a foreign key. The evaluator can work in one of three modes: $set, $get and $erase.

Suppose the events coming in are TCP packet metadata, and there is in SonarW a “connection” table for connections and “packet” table to track packets.

Make a ruleset for the connection table , using a selector to select new connection only, and use:

"_id": {
  "generate": true,
  "eval": [ "uniqueId", "$set", "source-ip", "source-port",  "dest-ip", "dest-port" ],
},

And _id will be generated for the combination of “source-ip”, “source-port”, “dest-ip”, “dest-port” . All further calls to uniqueId in the same sonargateway instance will yield the same ID, until $erase or another $set with the same field value will be called.

Make another ruleset for the connection table , using a selector to select connection closing only, and use:

"_id": {
  "generate": true,
  "eval": [ "uniqueId", "$erase", "source-ip", "source-port",  "dest-ip", "dest-port" ],
}

$erase will still return the same ID, but further calls to $set or $get with the same field value combination will yeild a different ID. THis guarantee that if you miss a connect or disconnect events (or both), the IDs will be unique per TCP session.

Finally, make a ruleset for the packets collection, and use:

"_id": {
  "generate": true,
  "eval": [ "uniqueId", "$get", "source-ip", "source-port",  "dest-ip", "dest-port" ],
}
  1. regex

Extract a string from a given field value using a capture group in a regular expression. For example:

"Database": {
  "generate": true,
  "eval": [
    "regex",
    "schema",
    "(.*)/.*"
  ]
}

If the field “schema” contains a value of the form “<db-name>.<table name>”, the DB name will be returned.

  1. toString

Convert an array of values (including objects) into a string. Recall sonargateway can handle structured data; some time you need this information in a string (for example, to perform textual searches). Pass the name of the field to convert, the delimited to put between array members and the delimiter to put between object. You can also name the object field member you would like to see in the string. For example:

"Position": {
    "generate":true,
    "eval": ["toString", ";", " ", "country","province"]
 }

If the field “Position” was originally an array:

[ {“city”:”Vancouver”, “province”:”BC”,”country”:”Canada”}, {“city”:”Toronto”, “province”:”ON”,”country”:”Canada”}]

The its content will be replaced with the string Canada BC;Canada ON

Global Setting

The global setting object always looks like this:

{
  "global_settings": {
      <global_attribute>: <value>,
      <global_attribute>: <value>,
     ...
  }
}

The sections below explain the various global settings possible.

Multi line support settings

Sometime an event is split between messages. There are two ways to generate the entire event. One is to merge few messages together, examining each at a time. Another is to concatenate all the messages together and then splitting them.

This processing happens before sonargateway tries to parse the event format , find the fields and their alues, etc.

  1. Using the merge-messages method

Set the value of “merge_lines” to true, and specify how sonargateway recognizes how to merge messages together.

You have to specify at least one of:

  • start_event_regex - a regular expression, that if matches, marks that the current message is the first one in an event. All previous messages , if not processed yet, are merged and processed.
  • end_event_regex - a regular expression that, if matches, marks that the current message is the last one in the event. All messages not processed yet , including the current one, are merged and processed.

And you can also specify, if neccessary:

  • do_not_end_event - If a message matches the given regular expression, it will not be the event’s last message, regardless of anything else.
  • event_end_preventor - If a message matches this regular expression, the event will not end until a messages matching end_event_regex is received, event if a message matching end_event_regex is received.
  • max_lines_per_event - Maximum number of messages per event. After this many unprocessed messages, they are merged and processed.
  • max_message_size - Maximum number of bytes that can compose one event. If a message comes in that brings the total bytes in all unprocessed event over this number, the messages will be merged and processed.

For example:

{
  "global_settings": {
    ....,
    "multiline_settings" : {
      "merge_lines": true,
      "start_event_regex": "{\"SonarG Source\"",
      "end_event_regex": "\"End Column\": 1 }",
      "max_lines_per_event" : 20,
      "max_message_size" : 1000,
    }
 }
}

Messages will be collected, starting events matching the given start regex, until a message matching the end_event_regex arrives. If a message matching start_event_regex arrives before end_event_regex , a new eevent will start. If end_event_regex arrives, event without a message matching start_event_regex, a new event will be generated and processed.

  1. Using the concatanate-and-split method

Set the value of “merge_lines” to false, and specify a spliting regular expression. Whereever the expression matches, the accumulated message data will be split into a new event. Capture groups will be dropped as they indicate a delimiting string and not a part of any event. For example, this setup will merge all incoming messages into one long stream of data and split it into distict events whenever ” X ” appears:

{
  "global_settings": {
    ....,
    "multiline_settings" : {
      "merge_lines": false,
      "split_at"  : "(\\sX\\s)"
     }
  }
},

Events Ignore and Pre-processing setting

You can drop a whole message, or replace part of a message, using the /preprocessor/. The preprocessor array in the global_settings object lets you do the following:

  • Drop a whole message. Create a document with the field “drop” and a regular expression value. Every message macthing the regular expression will be completly ignored.
  • Replace a part of a message with an alternative text. Create a document with the fields “replace” and “with”. For “replace”, specify a regular expression with capture groups capturing the values to replace. For “with”, specify an array of strings with the replacement values. The length of list of replacements should be equal to the number of capture groups.
  • Drop a part of the message text. Create a document with the field “replace”, with a value of a regular expression with a capture groups capturing the values to remove.

For example:

{
  "global_settings": {
    ...,
    "preprocessor" : [
          {
              "drop" : "DB.*Name"
          },
          {
              "replace" : "(oldField)=(\d*)",
              "with" : ["newField","000"]
          },
          {
              "replace" : "(rep.*Field)Name"
          }
   ]
  }
}

This will drop all messages matching the regex “DB.*Name” , will remove from all message any text maching the regular expression “(rep.*Field)Name” , and will replace in all messages of the form “oldfield=<some number>” with “newField=000”.

Message Header Cleansing

Before rsyslogd messages are collected into an Event, you can scrub the message header to remove any transport-level artifacts. In general, the rsyslogd on SonarC system is configured to convert those artifacts into real event fields and remove them from the message text. However, sometime it is not possible for rsyslogd to recognize that the start of the message is a transport-related text to be removed. Therefore, you can configure a regular expression with one capture group as the message header cleanser. Everything captured by the group will be removed. For example.:

{
  "global_settings": {
      "multiline_settings": {
          "syslog_header_cleansers" : "regex:(PRI\\s\\d+)\\s+"
      }
  }
}

Will remove the header from message:

“PRI 2 LEEF:2.0|Jsonar Inc.|jsonar-leef-generator|1.0|23||dstPackets= src=fw.company.com”

And leave this well-defined LEEF to be further processed:

“LEEF:2.0|Jsonar Inc.|jsonar-leef-generator|1.0|23||dstPackets= src=fw.company.com”

Sonar DB Settings

sonar_URI - the MongoDB-style URI of the SonarW DB server to connect to target_db - Unless specified by a rule, the name of the SonarW database to write events to. do_not_ingest - An array of strings specifying collections that will be updated, and therefore batch-style inserts cannot be used.

For example, the following defines the sonarW DB to use, define a collection to be non-batched, and declares that the default DB to write to is “SIEM_DB”:

{
 "global_settings": {
         …
        "do_not_ingest": ["last_activity"],
        "sonar_URI": "mongodb://user:pass@127.0.0.1:27117/admin",
        "target_db": "SIEM_DB",
        …
    }
}

Global Fields

As explained above, the global_field_translations object specifies how to deal with field that are present in all events, regardless of which rule conatins it. for example:

"global_field_translations": {
    "msgtxt": {
        "rename": "Message Text"
    }
}

In all Events, in regardless which ruleset handles it, the field “msgtxt”, if present, will be renamed to “Message Text”.

Common Fields

The common_field_translations object specifies how to translate fields, but does not cause them to be translated unless another rule asks for that field. This is a useful shortcut if you have a field that appear in many event types, but not all, and has complex translation. Use the name of the common translation to apply the same to a field in a specific case. For example:

[
        "common_field_translations": {
            "msgsrc": {
                "set_on_insert": true,
                "host_name" : ["hostname", "source host", "source ip", "local host"],
                type: 2,
            }
        },
        {
           "output_connection": {
                  "default_collection": "instance",
                  "collection_selectors": { "select_by_key_value": { "cs2": "MYSQL" } },
            }
            "Severity": { "type": 18 },
            "Source": "msgsrc"
        },
        {
           "output_connection": {
                  "default_collection": "session",
                  "collection_selectors": { "select_by_key_value": { "cs2": "ODBC" } },
            }
            "Severity": { "type": 18 },
            "Source": "msgsrc"
        }
]

Without the use of common_field_translations , instead of the value “msgsrc”, you’d have to enter again the long definition given to it in the common section. For example, the last entry would have to be the longer:

{
   "output_connection": {
          "default_collection": "session",
          "collection_selectors": { "select_by_key_value": { "cs2": "ODBC" } } ,
    }
    "Severity": { "type": 18 },
    "Source": {
        "set_on_insert": true,
        "host_name" : ["hostname", "source host", "source ip", "local host"],
        type: 2,
    }
}

Rule Sets

Group and Unique label

As explained above, an event will be used by one rule of each group. For example, if you have a lot of datatabase sessions events coming in, and you have rules to sort them out to their respective database type, you can put all the rules in one group, and provide a catch-all rule with no selector to store any event not previously handled, like this:

{

“output_connection”: { …, “select_by_key_value”: { “Type”: “MSSQL” } “unique_label”: “mssql_session”, “group_label”: “sessions”, …, }, “output_connection”: { …,

“select_by_key_value”: { “Type”: “MariaDB” },

“unique_label”: “mdb_session”, “group_label”: “sessions”, …, }, “output_connection”: { // This will catch all other events …, “unique_label”: “mssql_session”, “group_label”: “sessions”, …, }

}

Note that each rule has to have a unique label, regardless of the group.

Selection

Using the selection part of the rule, you decide if the rule applies to the currently handled event. Only if the rule applies, the event will be written to SonarW.

The general format of the selection is:

{
        "output_connection": {
            ... ,
            "collection_selectors": {
                     <selector_type>: {
                             <selector_parameter> : <selector_value> ,
                             ...
                      }
                }
                },
        ... ,
}

If you specify more than one selector, once one matches the rule will be selected.

  1. select_by_key_value

Select the rule depending on specific field values. $or and $and operators can be used. The value should match the string exactly, or if the string is in the form “/REGEX/” , it should match the regular expression specified by REGEX. For example:

"collection_selectors": {
  "select_by_key_value": {
    "$or": [
      {
        "reply.code": "/2\\d\\d\/"
      },
      {
        "source": "company.com"
      }
    ]
  }
}

The rule will be selected if the field “prop.code” matches the regular expression, or if the field “fieldA” has the value “a” exactly. For example, the JSON event:

{
        "reply" : {
                "code" : 201,
                "message" : "HTTP Code 201 - Object Created"
        },
        "source: "supply.com"
}

will match, because the first condition is met.

Instead of “$or”, you can use “$and” , and then both fields should match. So the example JSON above would not match.

  1. select_by_key_not_value

This selector behaves exactly like select_by_key_value , but the final result is reversed.

  1. select_by_key

The output collection will be selected if the any of the strings in the array exists in the event. For example, if the event deals with SQL activity, the collection will be selected:

"select_by_key": [ "SQL Statement", "SQL Result" ]
  1. select_by_regex

Select the rule if the regular expression matches anywhere in the event. For example:

"collection_selectors": {
     "select_by_regex": ".*Data.*Requests.*"
}

If any field in the event matches the regular expression, the rule will be selected.

Event Formats

There are several event formats sonargateway can parse. You have to specify one event format, and its parameters, for each rule, like this:

      {
              "output_connection": {
              ... ,
                      "event_format": {
                              "standard": <event_format>
                  <event_format_parameter> : <event_format_value> ,
                      },
              ... ,
      }

For example::

      {
              "output_connection": {
              ... ,
              "event_format": {
                  "standard": "JSON",
                  "unwind" : "events"
              },
              ... ,
      }
  • CEF

Treat incoming messages as CEF events:

"event_format": {
    "standard": "CEF",
},

Note that custom field named (e.g. cs1) are saved in SonarW using their provided label (e.g. cs1Label). Field types are automaticallys set for pre-defined fields.

  • LEEF

Treat incoming messages as LEEF version 1 or 2 events:

"event_format": {
    "standard": "LEEF",
}

Field types are automatically set for pre-defined fields.

  • CSV

Treat incoming messages as CSV file lines. Each field must be surrounded by double-quotes and fields must be separated by a comma. Newlines are allowed in fields. The parameter specifies which fields are present and their names. For example:

"event_format": {
  "standard": "CSV",
  "headers": [
          "Timestamp",
          "Service",
          "Action Type",
          "DB User",
          "Collection Name",
          "Database Name",
          "Client IP",
          "Event",
          "Error"
  ]
}

Incoming messages will be treated as CSV lines with 9 fields, whose names are listed.

  • JSON

Treat incoming messages as JSON. The data must strictly adhere to RFC 7159, except for treating of escaping characters which can be relaxed. There are two parameters, “unwind” string parameter to extract the actual events from an array with the given name within the JSON, and “escaping” boolean parameter that, when set to false, relaxes the RFC 7159 requierments about escaping characters so that strings like “ADMINSERVER” can appear in the input. For example:

"event_format": {
    "standard": "JSON",
    "unwind": "events"
}

Will create many JSON events out of an arriving message. If the message contains the field “events”, as an object array, each of those objects will become one event.

  • ORACLE_SYSLOG

Treat incoming messages as Oracle’s audit message as they appear in syslog:

"event_format": {
    "standard": "ORACLE_SYSLOG"
}

Note that Oracle audit to XML is of a different format. Use the SonarGateway for Oracle if you configure XML based auditing.

  • POSITIONAL

Use field position to endow event fields with name. You can optionally define the delimiters that separate words from one another. By default, the separators are spaces and tabs. For example:

{

“event_format”: “positional” ,

“delimiter”: ” t,” ,

“fields”: { “Type” : 1, “Timestamp” : [2,3], “Thread Name” :4, Message:[5], Sender: [-2,-1] }

}

The events will contain 5 fields: The first word will be the event type, the second and third word together constitute a time stamp, the fourth word will have the value for the “Thread Name” field, and all words from the fifth and on will be saved together in the “Message” field. In addition, the last two words will be saved in the field “Sender”.