Building a Concourse CI VM on Ubuntu

Recently, I’ve found myself needing a Concourse CI system. I struggled with the documentation on concourse.ci, couldn’t find any comprehensive build guides.  Knew for certain I wasn’t going to use VirtualBox.  So, having worked it out; thought I’d share what I went through to get to a working system.

Starting Position
Discovered that the CentOS version I was using previously did not have a compatible Linux kernel version.  CentOS 7.2 uses kernel 3.10, Concourse requires 3.19+.  So, I’m starting with a freshly-deployed Ubuntu Server 16.04 LTS this time.

Prep Ubuntu
Not a lot we have to do, but still pretty important:

  1. Make sure port for concourse is open

    sudo ufw allow 8080
    sudo ufw status

    sudo ufw disable

    I disabled the firewall on ubuntu because it was preventing the concourse worker and concourse web from communicating.

  2. Update and make sure wget is installed

    apt-get update
    apt-get install wget

Postgresql
Concourse expects to use a postgresql database, I don’t have one standing by, so let’s install it.

  1. Pretty straightforward on Ubuntu too:

    apt-get install postgresql postgresql-contrib

    Enter y to install the bits.  On Ubuntu, we don’t have to take extra steps to configure the service.

  2. Ok, now we have to create an account and a database for concourse. First, lets create the linux account. I’m calling mine “concourse” because I’m creative like that.

    adduser concourse
    passwd concourse

  3. Next, we create the account (aka “role” or “user”) in postgres via the createuser command. In order to do this, we have to switch to the postgres account, do that with sudo:

    sudo -i -u postgres

    Now, while in as postgres we can use the createuser command

    createuser –interactive

    You’ll enter the name of the account, and answer a couple of special permissions questions.

  4. While still logged in as postgres, run this command to create a new database for concourse. I’m naming my database “concourse” – my creativity is legendary. Actually, I think it makes life easier if the role and database are named the same

    createdb concourse

  5. Test by switching users to the concourse account and making sure it can run psql against the concourse databaseWhile in psql, use this command to set the password for the account in postgress

    ALTER ROLE concourse WITH PASSWORD 'changeme';

  6. Type \q to exit psql

Concourse
Ok, we have a running postgresql service and and account to be used for concourse. Let’s go.

  1. Create a folder for concourse. I used /concourse, but you can use /var/lib/whatever/concourse if you feel like it.
  2. Download the binary from concourse.ci/downloads.html into your /concourse folder using wget or transfer via scp.
  3. Create a symbolic link named “concourse” to the file you downloaded and make it executable

    ln -s ./concourse_linux_amd64 ./concourse
    chmod +x ./concourse_linux_amd64

  4. Create keys for concourse

    cd /concourse

    mkdir -p keys/web keys/worker

    ssh-keygen -t rsa -f ./keys/web/tsa_host_key -N ”
    ssh-keygen -t rsa -f ./keys/web/session_signing_key -N ”
    ssh-keygen -t rsa -f ./keys/worker/worker_key -N ”
    cp ./keys/worker/worker_key.pub ./keys/web/authorized_worker_keys
    cp ./keys/web/tsa_host_key.pub ./keys/worker

  5. Create start-up script for Concourse. Save this as /concourse/start.sh:

    /concourse/concourse web \
    –basic-auth-username myuser \
    –basic-auth-password mypass \
    –session-signing-key /concourse/keys/web/session_signing_key \
    –tsa-host-key /concourse/keys/web/tsa_host_key \
    –tsa-authorized-keys /concourse/keys/web/authorized_worker_keys \
    –external-url http://192.168.103.81:8080 \
    –postgres-data-source postgres://concourse:changeme@127.0.0.1/concourse?sslmode=disable

    /concourse/concourse worker \
    –work-dir /opt/concourse/worker \
    –tsa-host 127.0.0.1 \
    –tsa-public-key /concourse/keys/worker/tsa_host_key.pub \
    –tsa-worker-private-key /concourse/keys/worker/worker_key

    The items in red should definitely be changed for your environment. “external_url” uses the IP address of the VM its running on. and the username and password values in the postgres-data-source should reflect what you set up earlier. Save the file and be sure to set it as executable (chmod +x ./start.sh)

  6. Run the script “./start.sh”. You should see several lines go by concerning worker-collectors and builder-reapers.
    • If you instead see a message about authentication, you’ll want to make sure that 1) the credentials in the script are correct, 2) the account has not had it’s password set in linux or in postgres
    • If you instead see a message about the connection not accepting SSL, be sure that the connection string in the script includes “?sslmode=disable” after the database name
  7. Test by pointing a browser at the value you assigned to the external_url. You should see “no pipelines configured”.  You can login using the basic-auth username and password you specified in the startup script.

    Success!

  8. Back in your SSH session, you can kill it with <CRTL>+C

Finishing Up
Now we just have to make sure that concourse starts when the system reboots. I am certain that there are better/safer/more reliable ways to do this, but here’s what I did:
Use nano or your favorite text editor to add “/concourse/start.sh” to /etc/rc.local ABOVE the line that reads “exit 0”
Now, reboot your VM and retest the connectivity to the concourse page.

Thanks

EMC ECS Community Edition project for how to start the script on boot.

Mitchell Anicas’ very helpful post on setting up postgres on Ubuntu.

Concourse.ci for some wholly inadequate documentation

Alfredo Sánchez for bringing the issue with Concourse and CentOS to my attention

Building a Concourse CI VM on CentOS

Recently, I’ve found myself needing a Concourse CI system. I struggled with the documentation on concourse.ci, couldn’t find any comprehensive build guides.  Knew for certain I wasn’t going to use VirtualBox.  So, having worked it out; thought I’d share what I went through to get to a working system.

WARNING

It has been brought to my attention that CentOS does not have a compatible Linux kernel, so I’ve redone this post using Ubuntu instead.

Starting Position
I’m starting with a freshly-deployed CentOS 7 VM. I use Simon’s template build, so it comes up quickly and reliably.  Logged on as root.

Prep CentOS
Not a lot we have to do, but still pretty important:

  1. Open firewall post for concourse

    firewall-cmd --add-port=8080/tcp --permanent
    firewall-cmd --reload

    optionally, you can open 5432 for postgres if you feel like it

  2. Update and make sure wget is installed

    yum update
    yum install wget

Postgresql
Concourse expects to use a postgresql database, I don’t have one standing by, so let’s install it.

  1. Pretty straightforward on CentOS:

    yum install postgresql-server postgresql-contrib

    Enter y to install the bits.

  2. When that step is done, we’ll set it up with this command:

    sudo postgresql-setup initdb

  3. Next, we’ll update the postgresql config to allow passwords. Use your favorite editor to open /var/lib/pgsql/data/pg_hba.conf We need to update the value in the method column for IPv4 and IPv6 connections from “ident” to “md5” then save the file.

    Before

    After

  4. Now, let’s start postgresql and set it to run automatically

    sudo systemctl start postgresql
    sudo systemctl enable postgresql

  5. Ok, now we have to create an account and a database for concourse. First, lets create the linux account. I’m calling mine “concourse” because I’m creative like that.

    adduser concourse
    passwd concourse

  6. Next, we create the account (aka “role” or “user”) in postgres via the createuser command. In order to do this, we have to switch to the postgres account, do that with sudo:

    sudo -i -u postgres

    Now, while in as postgres we can use the createuser command

    createuser –interactive

    You’ll enter the name of the account, and answer a couple of special permissions questions.

  7. While still logged in as postgres, run this command to create a new database for concourse. I’m naming my database “concourse” – my creativity is legendary. Actually, I think it makes life easier if the role and database are named the same

    createdb concourse

  8. Test by switching users to the concourse account and making sure it can run psql against the concourse databaseWhile in psql, use this command to set the password for the account in postgress

    ALTER ROLE concourse WITH PASSWORD 'changeme';

  9. Type \q to exit psql

Concourse
Ok, we have a running postgresql service and and account to be used for concourse. Let’s go.

  1. Create a folder for concourse. I used /concourse, but you can use /var/lib/whatever/concourse if you feel like it.
  2. Download the binary from concourse.ci/downloads.html into your /concourse folder using wget or transfer via scp.
  3. Create a symbolic link named “concourse” to the file you downloaded and make it executable

    ln -s ./concourse_linux_amd64 ./concourse
    chmod +x ./concourse_linux_amd64

  4. Create keys for concourse

    cd /concourse

    mkdir -p keys/web keys/worker

    ssh-keygen -t rsa -f ./keys/web/tsa_host_key -N ”
    ssh-keygen -t rsa -f ./keys/web/session_signing_key -N ”
    ssh-keygen -t rsa -f ./keys/worker/worker_key -N ”
    cp ./keys/worker/worker_key.pub ./keys/web/authorized_worker_keys
    cp ./keys/web/tsa_host_key.pub ./keys/worker

  5. Create start-up script for Concourse. Save this as /concourse/start.sh:

    /concourse/concourse web \
    –basic-auth-username myuser \
    –basic-auth-password mypass \
    –session-signing-key /concourse/keys/web/session_signing_key \
    –tsa-host-key /concourse/keys/web/tsa_host_key \
    –tsa-authorized-keys /concourse/keys/web/authorized_worker_keys \
    –external-url http://192.168.103.81:8080 \
    –postgres-data-source postgres://concourse:changeme@127.0.0.1/concourse?sslmode=disable

    /concourse/concourse worker \
    –work-dir /opt/concourse/worker \
    –tsa-host 127.0.0.1 \
    –tsa-public-key /concourse/keys/worker/tsa_host_key.pub \
    –tsa-worker-private-key /concourse/keys/worker/worker_key

    The items in red should definitely be changed for your environment. “external_url” uses the IP address of the VM its running on. and the username and password values in the postgres-data-source should reflect what you set up earlier. Save the file and be sure to set it as executable (chmod +x ./start.sh)

  6. Run the script “./start.sh”. You should see several lines go by concerning worker-collectors and builder-reapers.
    • If you instead see a message about authentication, you’ll want to make sure that 1) the credentials in the script are correct, 2) the account has not had it’s password set in linux or in postgres and 3) the pg_hba.conf fie has been updated to use md5 instead of ident
    • If you instead see a message about the connection not accepting SSL, be sure that the connection string in the script includes “?sslmode=disable” after the database name
  7. Test by pointing a browser at the value you assigned to the external_url. You should see “no pipelines configured”

    Success!

  8. Back in your SSH session, you can kill it with <CRTL>+X

Finishing Up
Now we just have to make sure that concourse starts when the system reboots. I am certain that there are better/safer/more reliable ways to do this, but here’s what I did:

echo "/concourse/start.sh" >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

Now, reboot your VM and retest the connectivity to the concourse page.

Thanks

EMC ECS Community Edition project for how to start the script on boot.

Mitchell Anicas’ very helpful post on setting up postgres on CentOS.

Concourse.ci for some wholly inadequate documentation

A downside to VVols

I picked up a Dell Equallogic PS6000 for my homelab.  Updated it to the latest firmware and discovered it’s capable of VVols.  Yay!  I created a container and (eventually) migrated nearly everything to it.  Seriously, every VM except  Avamar VE.  Started creating and destroying VMs; DRS is happily moving VMs among the hosts.

UNTIL (dun dun dun)

The Equallogic VSM, running the VASA storage provider gets stuck during a vMotion.  Hmm, I notice that all of the powered-off VMs now have a status of “inaccessible”.  On the hosts, the VVol “datastore” is inaccessible.  

Ok, that’s bad.  Thank goodness for Cormac Hogan’s post about this issue.  It boils down to a chicken-and-egg problem.  vCenter relies on the VASA provider to supply information about the VVol.  If the VASA provider resides on the VVols, there’s no apparent way to recover it.  There’s no datastore to find the vmx and re-register, the connections to the VVols are based on the VM, so if it’s not running, there’s no connection to it.

To resolve, I had to create a new instance of the Equallogic VSM, re-register it with vCenter, re-register it as a VASA provider and add the Equallogic group.  Thankfully, the array itself is the source-of-truth for the VVol configuration, so the New VSM picked it up seamlessly.

So your options are apparently to place the VSM/VASA provider on a non-VVol or build a new one every time it shuts down.  Not cool.

 

Configuring NSX Load-Balancer for PCF

08/26/2016 Comments off

There’s not a lot of specific information out there for this configuration.  There’s some guidance from Pivotal and some how-tos from VMware, so with a little additional detail, we should be able to figure this out.

Edit – 2/1/17 – Updated with OpenSSL configuration detail
Edit – 3/20/17 – Updated SubjectAltNames in config

Preparation

  1. SSL Certificate. You’ll need the signed public cert for your URL (certnew.cer), the associated private key (pcf.key) and the public cert of the signing CA (root64.cer).
    1. Download and install OpenSSL
    2. Create a config file for your request – paste this into a text file:

      [ req ]
      default_bits = 2048
      default_keyfile = rui.key
      distinguished_name = req_distinguished_name
      encrypt_key = no
      prompt = no
      string_mask = nombstr
      req_extensions = v3_req

      [ v3_req ]
      basicConstraints = CA:FALSE
      keyUsage = digitalSignature, keyEncipherment
      extendedKeyUsage = serverAuth, clientAuth
      subjectAltName = DNS: *.pcf.domain.com, DNS:ServerShortName, IP:ServerIPAddress, DNS: *.system.pcf.domain.com, DNS: *.apps.pcf.domain.com, DNS:*.login.system.pcf.domain.com, DNS: *.uaa.system.pcf.domain.com

      [ req_distinguished_name ]
      countryName = US
      stateOrProvinceName = State
      localityName = City
      0.organizationName = Company Name
      organizationalUnitName = PCF
      commonName = *.pcf.domain.com

    3. Replace the values in red with those appropriate for your environment. Be sure to specify the server name and IP address as the Virtual IP and its associated DNS record. Save the file as pcf.cfg.  You’ll want to use the wildcard “base” name as the common name and the server name, as well as the *.system, *.apps, *.login.system and *.uaa.system Subject Alt Names.
    4. Use OpenSSL to create the Certificate Site Request (CSR) for the wildcard PCF domain.

      openssl req -new -newkey rsa:2048 -nodes -keyout pcf.key -out pcf.csr -config pcf.cfg

    5. Use OpenSSL to convert the key to RSA (required for NSX to accept it)

      openssl rsa -in pcf.key -out pcfrsa.key

    6. Submit the CSR (pcf.csr) to your CA (Microsoft Certificate Services in my case), retrieve the certificate (certnew.cer) and certificate chain (certnew.p7b) base-64 encoded.
    7. Double-click certnew.p7b to open certmgr. Export the CA certificate as 64-bit encoded x509 to a file (root64.cer is the file name I use)
  2. Networks.  You’ll need to know what layer 3 networks the PCF components will use.  In my case, I set up a logical switch in NSX and assigned the gateway address to the DLR. Probably should make this a 24-bit network, so there’s room to grow, but not reserving a ridiculous number of addresses. We’re going to carve up the address space a little, so make a note of the following:
    • Gateway and other addresses you typically reserve for network devices.  (eg:  first 9 addresses 1-9)
    • Address that will be assigned to the NSX load balancer.  Just need one (eg: 10)
    • Addresses that will be used by the PCF Routers.  At least two. These will be configured as members in the NSX Load Balancer Pool.
  3. DNS, IP addresses.  PCF will use “system” and “apps” subdomains, plus whatever names you give any apps deployed.  This takes some getting used to – not your typical application.  Based on the certificate we created earlier, I recommend just creating a “pcf” subdomain.  In my case, the network domain (using AD-DNS) is ragazzilab.com and I’ve created the following:
    • pcf.ragazzilab.com subdomain
    • *.pcf.ragazzilab.com A record for the IP address I’m going to assign to the NSX Load-Balancer

NSX

Assuming NSX is already installed and configured.  Create or identify an existing NSX Edge that has an interface on the network where PCF will be / is deployed.

  1. Assign the address we noted above to the inteface under Settings|Interfaces
  2. Under Settings|Certificates, add the our SSL certificates
    • Click the Green Plus and select “CA Certificate”.  Paste the content of the signing CA public certificate (base64.cer) into the Certificate Contents box.  Click OK.
    • Click the Green Plus and select “Certificate”.  Paste the content of the signed public cert (certnew.cer) into the Certificate Contents box and paste the content of the RSA private key (pcfrsa.key) into the Private Key box. Click OK.
  3. Under Load Balancer, create an Application Profile. We need to ensure that NSX inserts the x-forwarded-for HTTP headers.  To do that, we need to be able to decrypt the request and therefore must provide the certificate information.  I found that Pool Side SSL had to be enabled and using the same Service and CA Certificates.
    Router Application Profile

    Router Application Profile

     

  4. Create the Service Monitor.  What worked for me is a little different from what is described in the GoRouter project page. The key points are that we want to specify the useragent and look for a response of “ok” with a header of “200 OK”.

    Service Monitor for PCF Router

    Service Monitor for PCF Router

  5. Create the Pool.  Set it to ROUND-ROBIN using the Service Monitor you just created.  When adding the routers as members, be sure to set the port to 443, but the Monitor Port to 80.

    Router Pool

    Router Pool

  6. Create the Virtual Server.  Specify the Application Profile and default Pool we just created.  Obviously, specify the correct IP Address.
    Virtual Server Configuration

    Virtual Server Configuration


PCF – Ops Manager

Assuming you’ve already deployed the Ops Manager OVF, use the installation dashboard to edit the configuration for Ops Manager Director.  I’m just going to highlight the relevant areas of the configuration here:

Networks.  Under “Create Networks”, be sure that the Subnet specified has the correct values.  Pay special attention to the reserved IP ranges.  These should be the addresses of the network devices and the IP address assigned to the load-balancer.  Do not include the addresses we intend to use for the routers though.  Based on the example values above, we’ll reserve the first 10 addresses.

Ops Manager Network Config

Ops Manager Network Config

Ops Manager Director will probably use the first/lowest address in range that is not reserved.

PCF – Elastic Runtime

Next, we’ll install Elastic Runtime.  Again, I’ll highlight the relevant sections of the configuration.

  1. Domains.  In my case it’s System Domain = system.pcf.ragazzilab.com and Apps Domain = apps.pcf.ragazzilab.com
  2. Networking.
    • Set the Router IPs to the addresses (comma-separated) you noted and added to as members to the NSX load-balancer earlier.
    • Leave HAProxy IPs empty
    • Select the point-of-entry option for “external load balancer, and it can forward encrypted traffic”
    • Paste the content of the signed certificate (certnew.cer) into the Certificate PEM field.  Paste the content of the CA public certificate (root64.cer) into the same field, directly under the certificate content.
    • Paste the content of the private key (pcf.key) into the Private Key PEM field.
    • Check “Disable SSL Certificate verification for this environment”.
  3. Resource Config.  Be sure that the number of Routers is at least 2 and equal to the number of IP addresses you reserved for them.

 

Troubleshooting

Help! The Pool Status is down when the Service Monitor is enabled.

This could occur if your routers are behaving differently from mine.  Test the response by sending a request to one of the routers  through curl and specifying the user agent as HTTP-Monitor/1.1

curl -v -A “HTTP-Monitor/1.1” “http://{IP of router}”

 

Testing router with curl

Testing router with curl

The value in the yellow box should go into the “Expected” field of the Service Monitor and the value in the red box should go into the “Receive” field. Note that you should not get a 404 response, if you do, check that he user agent is set correctly.

 

Notes

This works for me and I hope it works for you.  If you have trouble or disagree, please let me know.

vRealize Automation DEM worker cannot connect to Orchestrator

08/23/2016 Comments off

In vRA 6.2, using vRO 6.0, you may find that the data collection and other vRO workflows fail with the error “You must have at least one properly configured vCenter Orchestrator endpoint that is reachable”.  The IaaS/Monitoring/Log will show which DEM worker threw the error.  When you check the DEM worker logs for that instance, if you find the message “Could not create SSL/TLS secure channel. —> System.Net.WebException: The request was aborted: Could not create SSL/TLS secure channel“, you have probably been affected by VMKB 2123455 and MS KB 3061588.

Although both articles seem to suggest that removing the offending patch will solve the problem, I think figuring out exactly which patch is rather awkward.  The easier fix is to apply a quick registry hack to your DEM workers (and wherever the vRA Designer runs).

  1. Logon as an account with admin rights (suggest the account your IaaS services run under)
  2. locate or add the key

    HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\
    KeyExchangeAlgorithms\Diffie-Hellman

  3. Add/update the DWORD value ClientMinKeyBitLength and set the value to 512 decimal (200 hex)
  4. Restart the DEM worker service

 

Notes:
The Microsoft patch sets the default minimum group size to 1024.  It appears that the vRO 6.0.x appliances use something less than that.  This registry hack indicates that SCHANNEL should accept keys as small as 512 bits.  I suggest only applying this to the necessary and affected machines since it does lower the bar for the DHE security requirements.

Thanks to Zach Milleson for reminding me that this workaround may not resolve everyone’s issue, depending on which MS patches are installed.  If this workaround doesn’t work for you, you may have to locate and remove the offending patch.  YMMV.

Pivotal Cloud Foundry vApp startup order workflow

08/07/2016 Comments off

After installing Pivotal Cloud Foundry (PCF) on vSphere, you’ll have a collection of at least 21 (probably closer to 60!) VMs with names that probably don’t match anyone’s convention.  Although, as noted in the PCF documentation, there is a correct order to starting up and shutting down the VMs in PCF, the installer does not configure a vApp so that we can control that order.  So, I dragged all the PCF VMs into a vApp and starting trying to determine which ones are in which role and quickly realized that it’s a pain.

Creating an AZ in Ops Manager on vSphere

Creating an AZ in Ops Manager on vSphere

As an aside, when you create your Availability Zone, you point it at a vSphere cluster and, optionally, a Resource Pool.  Unfortunately, if you specify a vApp Name instead of a Resource Pool name, BOSH will fail to deploy the VMs.  So, I’ve typically leave the Resource Pool field blank and then drag the VMs into a vApp post-deployment.

I put together a workflow that will help place the PCF VMs into correct startup/shutdown groups for you.

Example PCF VMNames

Example PCF VMNames

Instructions for Use

  1. Download the package from here
  2. Import the package into vRealize Orchestrator
  3. If you haven’t already, create a new vApp in your cluster and drag the Ops Manager, Ops Manager Director and all of the Elastic Runtime VMs into the vApp
  4. Run the “PCFvAppStartupOrder” workflow, select your new vApp as the input, click Submit
  5. If the PCF installation is scaled out to more VMs, just drag them to the vApp and rerun the workflow

How it works/What it does

  • The correct order is stored in a string array
  • The deployment, job and director custom fields are read for each VM in the vApp to get the VM’s assigned role
  • For the Ops Manager, the Notes field is read and if found, it is placed at the top of the startup sequnce
  • Unknown VMs are assigned a startup order higher than the last in the array.  This way, they start last and power-off first
  • Unknown VMs are those where the “deployment” field does not start with “cf”; with exceptions for Ops Manager (Notes field) and Ops Manager Director (“director” field value is “bosh-init”)

Additional suggestions and notes

  • Adjust the resources for the vApp based on VMware best practices and what makes sense for your environment
  • Use this at your own risk, there is no implied warranty

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

Startup Params in ConfigurationManager

  1. Logon to Log Insight as an administrator
  2. Check the Content Package area to be sure the “Microsoft – SQL Server” Content Pack is installed
  3. In the next steps, we’ll configure the agent from the server-side. It is possible to do from the client-side.
  4. In the Administration|Agents section of Log Insight select Microsoft – SQL Server from Available Templates drop-down list
  5. 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”.
  6. Now, the dropdown list should indicate that you are editing the new Agent Group
  7. In the Filter box, edit the parameters to create a filter that identifies only the desired server. For example “Hostname | starts with | SERVERNAME”
  8. 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

  1. 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

  1. Click Save New Group.
  2. On the SQL Server, restart the “VMware vRealize Log Insight Agent”
  3. 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