Device Configuration : MS SQL + Windows from same machine

This article explains the steps to configure logs from IIS application, MS SQL application and Windows OS Audit-Logs from the same machine.

Requirements

Admin access to the window machine running all these applications.
Admin access to the MS SQL applications.
Port - UDP 514 and 5154 allowed from the windows machine running all the applications , outwards to the CCE.

NxLog Configuration

Login on collector/AD computer.
Download the latest version of nxlog. It is easiest to choose the Windows msi file which includes an installer. Use the link : http://nxlog.org/products/nxlog-community-edition/download
Open the Nxlog configuration file at: C:\Program Files (x86)\nxlog\conf\nxlog.conf
Replace the entire configuration file by pasting the following Below – Note to replace the variable (IP Address of Seceon Collector) with the actual Seceon Server IP address:

## 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 C:\Program Files (x86)\nxlog
#define ROOT C:\Program Files (x86)\nxlog

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

<Extension _json>    
  Module xm_json
</Extension>

#Extension for MSSQL
<Extension mssql_csv>
    Module          xm_csv
    Fields          $Hostname, $SourceName, $Action_ID, $Result, $DataBase, $SV_Instace, $User, $Message
    FieldTypes      string, string, string, string, string, string, string, string
    Delimiter       ;
</Extension>

define aisiem                                                                 \
258, 259, 260, 261, 262, 531, 532, 533, 534, 535, 536, 537, 538, 539, 540,    \
551, 552, 675, 676, 677, 679, 680, 681, 682, 683, 4624, 4625, 4634, 4647,     \
4649, 4656, 4659, 4661, 4663, 4720, 4722, 4725, 4726, 4727, 4728, 4729, 4730, \
4731, 4732, 4733, 4734, 4735, 4737, 4738, 4740, 4741, 4742, 4743, 4744, 4745, \
4748, 4749, 4750, 4751, 4752, 4753, 4754, 4755, 4756, 4758, 4759, 4760, 4762, \
4763, 4764, 4771, 4772, 4773, 4775, 4777, 4778, 4779, 4782, 4785, 4786, 4787, \
4788, 4793, 4794, 4797, 5140, 5142, 5143, 5144, 5145

# Input for base OS/AD Audit Logs
<Input in>
      Module im_msvistalog
      Query <QueryList>\
                  <Query Id="0">\
                        <Select Path="Security">* </Select>\
                        <Select Path="Application">* </Select>\
                        <Select Path="Setup">* </Select>\
                        <Select Path="System">* </Select>\
                  </Query>\
            </QueryList>
            <Exec>
                  if ($EventID NOT IN (%aisiem%)) drop();
            </Exec>
</Input>
#Input for MSSQL
<Input in_mssql>
    Module          im_msvistalog
    SavePos         FALSE
    ReadFromLast    TRUE
    Exec   $Message = $raw_event;
    # Finding some values:
    Exec    if $raw_event =~ /action_id:(\S+)/ $Action_ID = $1;
    Exec    if $raw_event =~ /database_name:(\S+)/ $DataBase = $1;
    Exec    if $raw_event =~ /server_instance_name:(\S+)/ $SV_Instace = $1;
    Exec    if $raw_event =~ /session_server_principal_name:(\S+)/ $User = $1;
    Exec    if $raw_event =~ /AUDIT_SUCCESS/\
              {\
                    $Result = 'Success';\
              }\
              else\
                    $Result = 'Failure';
    # Replace white spaces
    Exec            $Message = replace($Message, "\t", " "); $Message = replace($Message, "\n", " "); $Message = replace($Message, "\r", " ");
</Input>

#Output for base OS/AD Audit Logs
<Output out>    
  Module om_udp    
  Host CCE_IP_ADDRESS  
  Port 5154    
  Exec to_json();
</Output>

#Output for MSSQL
<Output out_mssql>
    Module          om_udp
    Host            CCE_IP_ADDRESS
    Port            514
    # Ensure we send in the proper format:
    Exec           $Hostname = hostname_fqdn();
    Exec            mssql_csv->to_csv(); $raw_event = $Hostname + ' mssql_logs: ' + $raw_event;
</Output>

#Route for base OS/AD Audit Logs
<Route 1>    
  Path in => out
</Route>

#Route for MSSQL Logs
<Route mssql>
    Path            in_mssql => out_mssql
</Route>

Put CCE IP address on line 77 and 85 in the above script.

Enabling Audit Logs

This configuration will need you to enable audit logs of Base OS , MSSQL Application and IIS Application. Steps described in the following sections.

Enabling Audit Logs on Base OS

Login to the machine as Admin.
Follow the instructions as given in the link : https://seceon.atlassian.net/wiki/spaces/PP/pages/445612089

Enabling Audit Logs on MSSQL

Login to the machine as Admin.
Follow the instructions as given in the link : Microsoft SQL Server through NXLog
Enable Logging on Windows IIS server

Verifications

Login to the seceon GUI as an Administrator/User .
Go to the “ Logs/Flows Collection Status“ screen on the System Tab. Ensure it is showing up the last 15 minutes data.
Look for the IP / hostname of your window machine with the tag for MSSQL( IP - ms_windowsmssql )