Aria Operations for Logs integration with VMware Data Services Manager v1.4

In my most recent post on VMware Data Services Manager (DSM), we saw how it was possible to integrate the PostgreSQL databases – which are deployed by VMware DSM – with Aria Operations. In this post, we will see how it is also possible to integrate the logs generated by these deployments into Aria Operations for Logs, formerly known as vRealize Log Insight. Let’s begin the post by once again sharing the versions of the various products that we will be using to do the setup.

  • VMware vRealize® Operations™, Version: 8.10.2 (21178503), Edition: Enterprise
  • vRealize True Visibility Management Pack for MySQL Version: 8.1.0.20220907.202819
  • VMware Data Services Manager Version 1.4
  • VMware vCenter Version 7.0.3 Build: 20990077
  • VMware vRealize® Log Insight Version: 8.10.2-21145187
  • VMware vRealize® Log Insight Agent, Version: 8.10.2
  • VMware vRealize® Log Insight Content Pack for MySQL Version: 1.1

However, before we get into that, I did leave one hanging thread in my previous post. I made the assumption that it would be possible to integrate MySQL databases – which are also deployed via VMware DSM – with Aria Operations. After finally getting an opportunity to test this with the vRealize True Visibility MP for MySQL, I can now confirm that this is definitely the case. To prove the point, I have added a few screenshots of a VMware DSM provisioned MySQL database which is being monitored by Aria Operations and the vRealize True Visibility MP for MySQL. This is from a newly created MySQL database with not a lot going on, but hopefully you get the idea. This is the MySQL Overview dashboard with information such as instances, databases and table spaces.

More specifically, we can get instance, database, and table usage information, although not much is happening in this particular instance.

Also, we can get information about database queries, the query text and how well they perform.

Nice to see that we have seamless integration for both PostgreSQL and MySQL in Aria Operations. One point to note about the MySQL implementation is that the user ‘dbaas‘ – which is the default user that we create for DSM provisioned databases – has sufficient privileges to be able to gather the necessary metrics required by the vRealize True Visibility pack, so there is no need to make the modifications to the database like we had to do for the PostgreSQL implementation. The requirements for User Privileges are defined here in the official docs.

Aria Operations for Logs (On-Prem)

Let’s now look at the steps involved in getting the DSM Provider and Agent, as well as a MySQL database to send logs to Aria Operations for Logs (On-Prem). There are two ways to do this. The first is a manual approach where you can do the basic steps to have the database VM ship logs to Aria Operations for Logs, The second is an automated approach where the Provider, Agent and DB VMs all ship their logs to Log Insight. We will look at each setups next.

Manual Setup

After deploying the Aria Operations for Logs (On-Prem) / Log Insight appliance, and doing the initial configuration, we can add the content pack for MySQL. In the Log Insight UI, navigate to Content Packs > Marketplace, and the Content Pack for MySQL should be visible. Simply install this Content Pack.

The next step is to download the agent from the Log Insight UI. The agent is then copied over to the VM where the MySQL database is running, where it needs to be installed. In the Log Insight UI, navigate to Log Sources > Agents. This shows the available agents and their formats. LI Agent is automatically selected as shown here.

In this example, I am using the Linux binary format of the agent, but other formats such as RPM are also available as you can see. The VMware DSM provisioned MySQL DB VM is running Photon OS, which is why I am using the Linux agent for Log Insight. This is what the install looks like (I’ve obfuscated IP addresses in various places) when it is run on the MySQL DB VM.

root@cjh-mysql-01 [ ~ ]]# chmod +x VMware-Log-Insight-Agent-8.10.2-21113573_10.27.51.101.bin

root@cjh-mysql-01 [ ~ ]# ./VMware-Log-Insight-Agent-8.10.2-21113573_10.27.51.101.bin
Synchronizing state of liagentd.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable liagentd
Removed /etc/systemd/system/multi-user.target.wants/liagentd.service.
Synchronizing state of liupdaterd.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable liupdaterd
Removed /etc/systemd/system/multi-user.target.wants/liupdaterd.service.
Got hostname from package name: XX.YY.ZZ.101
Synchronizing state of liagentd.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable liagentd
Created symlink /etc/systemd/system/multi-user.target.wants/liagentd.service → /lib/systemd/system/liagentd.service.
Synchronizing state of liupdaterd.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable liupdaterd
Created symlink /etc/systemd/system/multi-user.target.wants/liupdaterd.service → /lib/systemd/system/liupdaterd.service.

Installation completed.

ATTENTION: Please edit configuration file:
/etc/liagent.ini

For online documentation please visit:
https://docs.vmware.com/en/vRealize-Log-Insight/index.html

Note that the output above is asking you to edit the configuration file – /etc/liagent.ini. This is where we tell the agent which log files to monitor, and to ship back to vRealize Log Insight. The most interesting logs on the MySQL DB VM are to be found in /var/log/tdm. Here is a modified /etc/liagent.ini from my setup:

root@cjh-mysql-01 [ ~ ]# cat /etc/liagent.ini
; 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: Restarting the agent is not required after making a configuration change
; Note: It may be more efficient to configure from server's Agents page !

[server]
hostname=xx.yy.zz.101

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

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

; Log Insight server port to connect to. Default ports for protocols:
; syslog and syslog_udp: 514; syslog with ssl: 6514; cfapi: 9000; cfapi with ssl: 9543. Default:
port=9000

; SSL usage. Default:
;ssl=yes
; Example of configuration with trusted CA:
;ssl=yes
;ssl_ca_path=/etc/pki/tls/certs/ca.pem
ssl=no

; Time in minutes to force reconnection to the server.
; This option mitigates imbalances caused by long-lived TCP connections. Default:
;reconnect=30

; Allow the agent to receive central configuration from the server.
; If disabled, only agent-side configuration will be applied. Default:
;central_config=yes

; FIPS mode.
; Possible values are 1 or 0. Default:
;ssl_fips_mode=1

[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=0
debug_level=1

; Frequency to print agent dynamic information in minutes. Default:
;stats_period=15

; Allow the agent to automatically decrease dynamic information print period to 1 minute
; in case if agent abnormal performance is detected.
; If disabled, agent performance won't be monitored at all. Default:
;smart_stats=no

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


; Uncomment the appropriate section to collect system logs
; The recommended way is to enable the Linux content pack from LI server
[filelog|messages]
directory=/var/log
include=messages;messages.?
event_marker=^\d


[filelog|dsm]
directory=/var/log/tdm
include=*.log
event_marker=^\d


[filelog|dbagent]
directory=/var/log/tdm/dbagent
include=*.log
event_marker=^\d


[update]
; Do not change this parameter
package_type=bin


; Enable automatic update of the agent. If enabled:
; the agent will silently check for updates from the server and
; if available will automatically download and apply the update.
auto_update=yes

I have created three different log sections, each labeled [filelog|section name]. I added the directory where the log files reside, and the logs files which should have their contents shipped to Log Insight. I also added an event marker to help identify multi-line events by looking for a date stamp at the beginning of a line (^\d). More details on all of this can be found in the official Log Insight Agent documentation here.

My understanding is that you may need to do a force reload of the Log Insight agent running on the MySQL VM to get these changes to take effect. I am open to correction on this point, but here is how to do that.

root@cjh-mysql-01 [ ~ ]# systemctl stop liagentd

root@cjh-mysql-01 [ ~ ]# /etc/init.d/liagentd status
liagent is stopped

root@cjh-mysql-01 [ ~ ]# /etc/init.d/liagentd
Usage: liagentd {start | stop | restart | force-reload | status}

root@cjh-mysql-01 [ ~ ]# /etc/init.d/liagentd force-reload
 [ OK ]

root@cjh-mysql-01 [ ~ ]# /etc/init.d/liagentd start
Starting liagentd (via systemctl): [ OK ]

root@cjh-mysql-01 [ ~ ]# /etc/init.d/liagentd status
liagent (pid 5043) is running...

root@cjh-mysql-01 [ ~ ]# systemctl status liagentd
● liagentd.service - Log Insight Agent
 Loaded: loaded (/lib/systemd/system/liagentd.service; enabled; vendor preset: enabled)
 Active: active (running) since Fri 2023-05-05 08:32:28 UTC; 6s ago
 Docs: https://docs.vmware.com/en/vRealize-Log-Insight/index.html
 Process: 5013 ExecStart=/bin/bash -c /usr/lib/loginsight-agent/bin/liagent --daemon --user `/etc/init.d/liagentd user` `/etc/init.d/liagentd pa>
 Main PID: 5043 (liagent)
 Tasks: 18 (limit: 4915)
 Memory: 6.0M
 CGroup: /system.slice/liagentd.service
 └─5043 /usr/lib/loginsight-agent/bin/liagent --daemon --user root

May 05 08:32:28 cjh-mysql-01.rainpole.com systemd[1]: Starting Log Insight Agent...
May 05 08:32:28 cjh-mysql-01.rainpole.com systemd[1]: Started Log Insight Agent.

root@cjh-mysql-01 [ ~ ]#

Note that in the above configuration, I was lazy and did not use the SSL option. Instead I went for the cfapi port without SSL, which is 9000. In order for this to work with the Log Insight server, you will have to disable SSL for the API server. This is under the Configuration > SSL section of the Log Insight UI, as shown here.

Caution: While this is fine for a Proof of Concept, you would want to establish a trusted secure communication channel for production deployments.

Another step is to fine-tune the database options themselves so that they generate more interesting information. This can be done by making changes to the my-db-options.cfg file which is created for MySQL database advanced settings with VMware DSM. Here is the configuration file location and the advanced settings for my sample MySQL database:

root@cjh-mysql-01 [ /var/log ]# cat /opt/vmware/dbaas/data-mount/conf/my-db-options.cnf
[mysqld]
default-time-zone = UTC
max_connections = 100
log-queries-not-using-indexes = OFF
slow-query-log = ON
general-log = ON
general-log-file = /var/log/tdm/mysql-general.log
slow_query_log_file = /var/log/tdm/mysql-slow.log
long_query_time = 10

Note the addition of the slow-query-log set to the ON and a path set to a slow_query_log_file. The logs from this file are also getting shipped to Log Insight by the section [filelog|dsm] in the /etc/liagent.ini file.

[Update] Note also the general-log-file entry above which was missing from my original post. This is an important setting missing from the database configuration. My colleagues James Zabala and Amaury Garde caught this one. Once added, the `general-log-file` puts the SQL-level logs into the /var/log/tdm location which is then slurped up and shipped to the Log Insight Server. Good catch guys! However, you should probably not enable this unless you are troubleshooting a particular issue. I’ve been advised that it is far too heavy for normal use. It is normally an option which is turned on to debug something and then turned off again quickly.

Once again, I’m unsure if the database needs a restart for these changes to take effect. In my testing, I did restart it. This is quite simple to do. Since the database is deployed as docker containers using docker-compose, simply stopping the database container will make docker-compose restart it again immediately.

root@cjh-mysql-01 [ ~ ]# docker ps
CONTAINER ID  IMAGE                                COMMAND                  CREATED        STATUS        PORTS    NAMES
bc42f733f470  bitnami/percona-mysql:8.0.30          "/opt/vmware/dbaas/a…"  17 hours ago  Up 17 hours            mysql
3a0cbf2fe3de  bitnami-telegraf:1.22.3-photon-3-r5  "/bin/sh -c 'telegra…"  47 hours ago  Up 47 hours            telegraf

root@cjh-mysql-01 [ ~ ]# docker stop bc42f733f470
bc42f733f470

root@cjh-mysql-01 [ ~ ]# docker ps
CONTAINER ID  IMAGE                                COMMAND                  CREATED        STATUS        PORTS    NAMES
3a0cbf2fe3de  bitnami-telegraf:1.22.3-photon-3-r5  "/bin/sh -c 'telegra…"  47 hours ago  Up 47 hours            telegraf

root@cjh-mysql-01 [ ~ ]# docker ps
CONTAINER ID  IMAGE                                COMMAND                  CREATED        STATUS                  PORTS    NAMES
06ad3b781264  bitnami/percona-mysql:8.0.30          "/opt/vmware/dbaas/a…"  1 second ago  Up Less than a second            mysql
3a0cbf2fe3de  bitnami-telegraf:1.22.3-photon-3-r5  "/bin/sh -c 'telegra…"  47 hours ago  Up 47 hours                      telegraf

If there is an issue with the database restarting, check the configuration file options to make sure you didn’t make any typos. Examine the mysqld.log file in /var/log/tdm which may explain why the database is not able to restart.

Now return to the Log Insight UI, and select Management > Agents. In the section All Agents, there should be a MySQL entry. This should be visible since the content pack for MySQL has been added to the system. Select this entry, as shown here:

After selecting MySQL, the Agent Configuration is displayed. You are expected to click on the “Copy Template” button highlighted below to create an Agent Group for your MySQL agent.

You will be prompted to provide your own Agent Group Configuration with a unique name.

This will instantiate a new Agent Group for the agents that you wish to monitor. In our case, this will be the group of Agents that are monitoring MySQL VMs. Before you can save the new Agent Group, you need to tell it what it is monitoring. In which case, we simply add the IP address of the MySQL DB VM at this point.

After completing the above step, and without making any additional changes to the agent configuration, it should now be possible to see logs from the MySQL DB VM appearing in Log Insight. I immediately saw logs from the dbagent, telegraf and adapter log files in my setup.

Success! The MySQL database VM, which has been deployed by VMware Data Services Manager, is now successfully shipping logs to the Aria Operations for Logs appliance. I have not done anything in the way of log filtering or log parsing – I will leave that task up to the Log Insight experts to explore. There is a whole section on log parsing in the official documentation here. Let’s now look at the alternative method, which is to setup Log Forwarding in the DSM Provider, and configure a Syslog server to collect log files from the Provider VM, Agent VM, and provisioned databases.

Automated Setup

The Provider VM, Agent VM, and provisioned database VMs come with a Log Insight Agent pre-installed. Through the Settings > Log Forwarding configuration tab in the Provider UI, it is possible to set up a Syslog server to ship logs to your Aria Operations for Log / Log Insight server. Set the Log Server Type to Syslog, add the IP address of your Log Insight server, set the Port to the default port of 514, and select whether communication is over TCP or UPD. Once saved, the configuration is propagated to the /etc/liagent.ini configuration file on the Provider, Agent and DB VMs. Here is a screenshot of what a configured Log Forwarding Syslog looks like:

As you can see, this is a much more elegant approach when compared to the manual steps outlined previously. You should now see all of the DSM VMs appear in Log Insight under the Syslog Agents view.

We can now conclude that it is possible to send logs from the VMware DSM provisioned database VMs to Aria Operations for Logs, whilst also monitoring the finer details of the database via Aria Operations. Thus, it doesn’t matter if you are provisioning databases via VMware Data Services Manager for production or for your developers, the Aria Operations product stack can be leveraged for both monitoring and log analysis without needing to stand up a separate stack for this purpose. Thanks for reading this far, and I hope you found this useful.