Configuring vRealize Log Insight Agent on MS SQL Server 2012

There’s a lot of information available about the SQL Server Content Pack for Log Insight, but actually  trying to use it reveals a lot of gaps.  So, here’s what I’ve done to get my SQL Server 2012 R2 instances to correctly report their information to vRealize Log Insight and show up in the Content Pack filters.

To ensure the correct log directories are used and allow reuse of filelog sections, I suggest creating an agent group for each SQL Server.  I’m also a fan of the server-side agent configuration, so if the agent gets totally removed and reinstalled, it’ll acquire the correct configuration again.

Steps:

1. Install the Log Insight Agent for Windows on the SQL Server machine, be sure to use the FQDN of the Log Insight VIP
2. While logged on to the SQL Server, locate the Logs folder for each SQL server instance. Note that this is not the database transaction logs folder – if it contains *.ldf files, it’s the wrong folder.   Specifically, you want the folder that contains the ERRORLOG file.  To be certain follow these steps:
   • Launch SQL Server Configuration Manager
   • Select “SQL Server Services” from the left pane
   • Right-click “SQL Server (INSTANCENAME)” from the right page, choose “Properties”.
   • Select the “Startup Parameters” tab
   • In the Existing Parameters box, make a note of the parameter that starts with “-e”
   • The filepath in that parameter (minus “ERRORLOG”) is the folder we need.
   • DO NOT CHANGE ANY VALUES, Cancel Properties, Close Configuration Manager

3. Logon to Log Insight as an administrator
4. Check the Content Package area to be sure the “Microsoft – SQL Server” Content Pack is installed
5. In the next steps, we’ll configure the agent from the server-side. It is possible to do from the client-side.
6. In the Administration|Agents section of Log Insight select Microsoft – SQL Server from Available Templates drop-down list
7. Click the “Copy Template” button. In the Copy Agent Group box that appears, change the name to “Microsoft – SQL Server – SERVERNAME”.  (replace SERVERNAME with the actual server’s name).  Click “Copy”.
8. Now, the dropdown list should indicate that you are editing the new Agent Group
9. In the Filter box, edit the parameters to create a filter that identifies only the desired server. For example “Hostname | starts with | SERVERNAME”
10. In the Agent Configuration section
    • Update the section header from “[filelog|MSSQL]” to [filelog|MSSQL-SERVERNAME-INSTANCENAME]” replace SERVERNAME and INSTANCENAME with the correct, actual values. Be sure to keep “MSSQL” as the prefix, since the dashboard elements key on that.
    • Update the directory value to where the ERRORLOG file was found
    • Update the exclude value to read *.trc;*.xel;*.mdmp
    • Example:

[filelog|MSSQL-COREDB1-MSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

11. If there are multiple instances of SQL server on the host server, copy the entire section and edit the section header with the Instance Name. Example of configuration for multiple instances:

[filelog|MSSQL-COREDB1-MSSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

[filelog|MSSQL-COREDB1-WORKLOAD]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.WORKLOAD\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

12. Click Save New Group.
13. On the SQL Server, restart the “VMware vRealize Log Insight Agent”
14. Navigate to %ALLUSERSPROFILE%\VMware\Log Insight Agent and open liagent-effective.ini with notepad. Check that the sections added above appear in this file.
    • If they do not, you may have to adjust the filters on the Agent Group

Advertisement

Automating syslog configuration on NSX

If you’re here, you’ve probably already found VMKB2092228 and been frustrated by the lack of an easy and consistent way to configure all the NSX components to send log data.  Me too.

I put together a vRO workflow to help configure (and reconfigure) syslog on NSX.

1. It prompts for the NSX manager connection info and Syslog server info
2. Creates a REST host for the NSX manager using the admin super-user account
3. Adds several REST operations to the NSX Manager REST host
4. Deletes and readds the syslog configuration on the NSX Manager
5. Optionally configures Activity Monitoring on the NSX Manager
6. Identifies each running Controller; deletes and readds the syslog config on each
7. Identifies each deployed and “Green” Edge (including DLRs); deletes and readds the syslog config on each
8. Removes the NSX Manager REST host created earlier

Notes

• It must use the NSX Manager admin account – it’s the only superuser account that can update teh manager config.
• If a Controller is not running when the workflow runs, its syslog config will not be updated
• If an Edge’ state is not “green” or is not fully deployed, its syslog config will not be updated
• REST-HTTP plugin for vRO is required
• Tested with vRO 6.0.2 and NSX 6.2.1

1. Get the package Download Here
   

   By downloading any code, package or file, you acknowledge that:There is no explicit or implied warranty or support for the code.  Neither Brian Ragazzi, his employer nor anyone else is responsible for any problems, errors, omissions, unexpected behavior, breakage, trauma, outage, fatigue, lost time, lost work or incontinence that may occur as a result of using the code or package.

2. Import package into vRO
3. Run the ConfigureSyslogNSX workflow

   

   Workflow Step 1

   

   Workflow Step 2

Deploy vCenter Log Insight Windows Agent using GPO

The VMware documentation covers this, but I thought I’d add my “insight” (get it? har har)

Prerequsities

1. Get Microsoft Orca.  It’s part of the SDK found here.
2. Get the vCenter Log Insight Windows Agent from my.vmware.com if you haven’t already
3. An appropriate network location for GPO-delivered installers containing the vCLI Agent MSI

Orca Steps

1. Launch Orca, choose file|open and select thevCLI Agent MSI (VMware-vCenter-Log-Insight-Agent-2.0.3-1879692_1.msi in this case)

   

   vCLI MSI open in Orca

2. Within Orca, click Transform|New Transform

   

   Create new transform

3. Click the “Property” table to load its rows

   

   Property Table

4. Right-click under the populated rows and choose “Add Row”
5. In the “Add Row” dialog, enter the property as “SERVERHOST” and the value as theFQDN of yourvCenter Log Insight server, click OK.  Notice thenew record has a green box around it?  That means it’ll be included in the transform file.

   

   Add SERVERHOST Property

6. On the menu, choose Transform|Generate Transform.  Put it in the same folder as the vCLI Agent MSI, give it a descriptive name.
7. Once the mst is saved, you can close Orca.

GPO Steps

1. Create a new or open an existing GPO in Group Policy Management Editor
2. Expand Computer Configuration|Policies|Software Settings, right-click Software installation and choose New|Package

   

   Add software installation package to GPO

3. Select thevCLI MSI on the network share, select the “Advanced” option on the Deploy Software dialog, click OK.

   

   Choose the Advanced Option

4. On the VMware vCenter Log Insight Agent Properties dialog, navigate to the Modifications tab.
5. Click the “Add” button and select themst you created earlier. Click OK.  Make other changes to the package as appropriate for your environment and click OK to save.

   

   Modifications Tab

6. Like all other GPOs, link it to the appropriate OU(s) containing the computers you want the agent deployed on.