Gray Log / Monitoring SQL Server

Graylog Central (peer support)
, , , , ,
1

Friends, good morning!

I need some help with monitoring in SQL SERVER.
I’m dumping the log on an example drive: d:\logs\graylog.

I installed the client (Filebeat) and pointed to the directory above the example and I can see the logs arriving in the graylog successfully but I can’t filter inside the “message” field

Problem: I need to create a filter with keyword (CREAT LOGIN) that appears in the field “Message” and I can’t, can you help?

Below is an example of the log in the “Message” field
message

    • .X Z Z �R�� ~VB�RI-�/��o_3, .^
      No. 4��� dbo      AmasterTEST      2021CREATE LOGIN TEST      2021 WITH PASSWORD=N’      *****’, DEFAULT_DATABASE= [master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

OR is there any way to improve the result of this log in the field? Has anyone done some kind of monitoring and SQL in some other way? I tried to use winlogbeat, I didn’t have much success. It only brings windows logs…

Operating system information

Oracle Linux Server release 8.4

Package versions

Graylog 4.1.3
elasticsearch-oss-7.10.2-1

2

Hello && Welcome

I would have to say yes depending on how you configured you setup.

Your message shown above could be a couple different reasons why its like that. It could be the type of INPUT your using compared to the type of log file format sent.

What kind of file format is your SQL log files in? By chance are they an XEL File (.xel), TRC File (.trc)? Or are you using just ERRORLOG file?
Are you using a beat input?

I noticed that your path to the log file is configured for Windows? is this correct?

In our environment the MYSQL server logs are in MS event viewer so that way we just use the standard Winlogbeat format for MS and Beats INPUT on graylog.

Example:

winlogbeat.event_logs:
  - name: Application
    ignore_older: 72h

  - name: System

  - name: Security

  - name: ForwardedEvents
    tags: [forwarded]

  - name: Windows PowerShell
    event_id: 400, 403, 600, 800

  - name: Microsoft-Windows-PowerShell/Operational
    event_id: 4103, 4104, 4105, 4106

I also enabled more audit logging on our SQL server as shown below and may I add, windows has a lot of ways to add more auditing so be carful what you enable.

image
image545×535 28.5 KB

image
image882×547 37.2 KB

Hope that helps

4

Hello Friend!!
Thanks for the help… very grateful

I need to monitor these issues in SQL below … so we have enabled more audit trails and are recording these actions in a “.sqlaudit” file in the d:** directory

login and logout
User creation
password reset
disable user
enable user
Assign permissions to roles.
Add users as members to roles (admin etc)
delete user

As the connector (Winlogbeat) monitors (Security, Application, System) I found it interesting to work with another connector (Filebeat) and point to directory d:** where the “.sqlaudit” file is.

It is parameterized in this way

filebeat

Needed for Graylog

fields_under_root: true
fields.collector_node_id: ${sidecar.nodeName}
fields.gl2_source_collector: ${sidecar.nodeId}

output.logstash:
hosts: ["***********:5044"]
path:
date: C:\Program Files\Graylog\sidecar\cache\filebeat\data
logs: C:\Program Files\Graylog\sidecar\logs
tags:

  • windows
    filebeat.inputs:
  • type: log
    enabled: true
    paths:
    • D:\Audit_Logs*************.sqlaudit

Doubt
do you monitor this kind of events through Winlogbeat?

Do you really know the difference between Nxlog and Filebeat?

5

Hello,

Yes

Yes, the example above I showed you is my Winlogbeat config and this is my Nxlog configuration file.

<QueryXML>
        <QueryList>
            <Query Id="0" Path="Application">
                <Select Path="Application">*</Select>
                <Select Path="Microsoft-Windows-Winsock-AFD/Operational">*</Select>
                <Select Path="Microsoft-Windows-Wired-AutoConfig/Operational">
                    *
                </Select>
                <Select Path="Microsoft-Windows-Wordpad/Admin">*</Select>
                <Select Path="Windows PowerShell">*</Select>
            </Query>
        </QueryList>

To help you further here is some more information.

6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.