LogInsight Agent is Not sending SQL Data

[maneesh_ks]

Hi, I am trying to configure LogInsight agent to read SQL log files. Here are the INI files -

; VMware Log Insight Agent configuration. Please save as UTF-8 if you use non-ASCII names / values !

; Actual configuration is this file joined with settings from server to form liagent-effective.ini

; Note: It may be more efficient to configure from server's Agents page !

[server]

hostname=10.6.3.90

; Hostname or IP address of your Log Insight server / cluster load balancer. Default:

;hostname=LOGINSIGHT

; Protocol can be cfapi (Log Insight REST API), syslog. Default:

;proto=cfapi

; Log Insight server port to connect to. Default ports for protocols (all TCP):

; syslog: 514; syslog with ssl: 6514; cfapi: 9000; cfapi with ssl: 9543. Default:

;port=9000

; SSL usage. Default:

;ssl=no

; Example of configuration with trusted CA:

;ssl=yes

;ssl_ca_path=/etc/pki/tls/certs/ca.pem

; Time in minutes to force reconnection to the server.

; This option mitigates imbalances caused by long-lived TCP connections. Default:

;reconnect=30

[logging]

; Logging verbosity: 0 (no debug messages), 1 (essentials), 2 (verbose with more impact on performance).

; This option should always be 0 under normal operating conditions. Default:

debug_level=1

[storage]

; Max local storage usage limit (data + logs) in MBs. Valid range: 100-2000 MB.

;max_disk_buffer=200

; Uncomment the following sections to collect these channels.

; The recommended way is to enable Windows content pack from LI server.

;[winlog|Application]

;channel=Application

;[winlog|Security]

;channel=Security

;[winlog|System]

;channel=System

The Windows logs are flowing but SQL logs are not coming. What am I doing wrong?

Thanks

Maneesh

; VMware Log Insight Agent configuration. Please save as UTF-8 if you use non-ASCII names / values !

; Actual configuration is this file joined with settings from server to form liagent-effective.ini

; Note: It may be more efficient to configure from server's Agents page !

[server]

hostname=10.6.3.90

; Hostname or IP address of your Log Insight server / cluster load balancer. Default:

;hostname=LOGINSIGHT

; Protocol can be cfapi (Log Insight REST API), syslog. Default:

;proto=cfapi

; Log Insight server port to connect to. Default ports for protocols (all TCP):

; syslog: 514; syslog with ssl: 6514; cfapi: 9000; cfapi with ssl: 9543. Default:

;port=9000

; SSL usage. Default:

;ssl=no

; Example of configuration with trusted CA:

;ssl=yes

;ssl_ca_path=/etc/pki/tls/certs/ca.pem

; Time in minutes to force reconnection to the server.

; This option mitigates imbalances caused by long-lived TCP connections. Default:

;reconnect=30

[logging]

; Logging verbosity: 0 (no debug messages), 1 (essentials), 2 (verbose with more impact on performance).

; This option should always be 0 under normal operating conditions. Default:

debug_level=1

[storage]

; Max local storage usage limit (data + logs) in MBs. Valid range: 100-2000 MB.

;max_disk_buffer=200

; Uncomment the following sections to collect these channels.

; The recommended way is to enable Windows content pack from LI server.

;[winlog|Application]

;channel=Application

;[winlog|Security]

;channel=Security

;[winlog|System]

;channel=System

[maneesh_ks]

Liagent-effective.ini

; Dynamic file representing the effective configuration of VMware Log Insight Agent (merged server-side and client-side configuration)

;     DO NOT EDIT THIS FILE BY HAND -- YOUR CHANGES WILL BE OVERWRITTEN

; Creation time: 2016-05-17T12:10:27.764916

[server]

hostname=10.6.3.90

[logging]

debug_level=1

[filelog|com.microsoft.sql.MSSQL-default]

; IMPORTANT: Change the directory as per the environment

directory=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;

event_marker=^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}

[filelog|com.microsoft.sql.MSSQL-Instance1]

; IMPORTANT: Change the directory as per the environment

directory=E:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE01\MSSQL\Log

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;

event_marker=^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}

[winlog|com.microsoft.windows.Application]

channel=Application

[winlog|com.microsoft.windows.Security]

channel=Security

[winlog|com.microsoft.windows.System]

channel=System

[winlog|com.microsoft.windows.WindowsFirewall]

channel=Microsoft-Windows-Windows Firewall With Advanced Security/Firewall

[winlog|com.microsoft.windows.UAC]

channel=Microsoft-Windows-UAC/Operational

; Dynamic file representing the effective configuration of VMware Log Insight Agent (merged server-side and client-side configuration)

;     DO NOT EDIT THIS FILE BY HAND -- YOUR CHANGES WILL BE OVERWRITTEN

; Creation time: 2016-05-17T12:10:27.764916

[server]

hostname=10.6.3.90

[logging]

debug_level=1

[filelog|com.microsoft.sql.MSSQL-default]

; IMPORTANT: Change the directory as per the environment

directory=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;

event_marker=^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}

[filelog|com.microsoft.sql.MSSQL-Instance1]

; IMPORTANT: Change the directory as per the environment

directory=E:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE01\MSSQL\Log

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;

event_marker=^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}

[winlog|com.microsoft.windows.Application]

channel=Application

[winlog|com.microsoft.windows.Security]

channel=Security

[winlog|com.microsoft.windows.System]

channel=System

[winlog|com.microsoft.windows.WindowsFirewall]

channel=Microsoft-Windows-Windows Firewall With Advanced Security/Firewall

[winlog|com.microsoft.windows.UAC]

channel=Microsoft-Windows-UAC/Operational

[admin]

The SQL agent config says - ; IMPORTANT: Change the directory as per the environment  <> have you had a chance to verify if you SQL logs are present in the listed directory location?

[maneesh_ks]

Yes, there ERRORLOG files in that directory. 

[admin]

I see a line missing from your agent config - tags={"ms_product":"mssql"}

The complete agent config is -

[filelog|MSSQL]

; IMPORTANT: Change the directory as per the environment

directory=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

tags={"ms_product":"mssql"}

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;*.txt

This is version 3.1 of the Microsoft SQL server content pack on the in product marketplace

[maneesh_ks]

I have tried the tags also. Added into the config now. Still no data.

[admin]

Maybe a webex would work better ...please send me an email id and I'll send an invite.

[maneesh_ks]

maneesh.ks@gmail.com

[admin]

So the issue was not with content pack config but that there were no events coming in  since the agent config was applied. Once we got some events to log the content pack displayed the logs. Thanks for allowing me to troubleshoot your environment Maneesh.

[admin]

If this solves your problem, can you please mark this question as answered? Thanks.

[Stevennk]

Hi, I've edited the agent configs as shown above, However no log data is recieved from the sql server instances running in my environment. 

See below configs as they appear in my environment:-

[filelog|MSSQL]

directory=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

include=errorlog.*;sqlagent.*

charset=UTF-16LE

event_marker=^[^\s]

[filelog|CRMPROD]

directory=C:\Program Files\Microsoft SQL Server\MSSQL11.CRMPROD\MSSQL\Log

include=errorlog.*;sqlagent.*

charset=UTF-16LE

event_marker=^[^\s]

[Stevennk]

Here is my email id , should you wish to have a webex session

Stevennk@hollard.co.za

[admin]

Hi,

Can you try with this agent config as it is in the content pack?

[filelog|MSSQL]

; IMPORTANT: Change the directory as per the environment

directory=C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

tags={"ms_product":"mssql"}

charset=UTF-16LE

exclude=*.trc;*.xel;*.mdmp;*.txt

[Stevennk]

Hi, i removed the "include" parameter and logs started streaming in. Thanks for your prompt reply 🙂

[admin]

That's great! Could you please mark this question as answered if it resolves the issue?

[Stevennk]

Hi Again,

As alluded to earlier I was able to configure the SQL content pack, however some logs are not normalised as a result their are unreadable. Do I need to install anything to retrieve normalized data?