Archive | July, 2021

VMware App Volumes Database – Annual Clean-up (Pruning old data)

22 Jul

The VMware App Volumes database archival tables grow constantly, and it can quickly outgrow the database size. To prevent this growth, it is advisable to perform regular pruning of the database. In our scenario, we prefer to perform this activity annually as we have sufficient space to accommodate the growth. There are mainly three tables that need pruning. (log_records, snapvol_timeseries and system_messages)

VMware has a detailed knowledge base article decribing Pruning the VMware App Volumes SQL database (2132454). However, if you are trying to delete many records at one time, you will get a full transaction log error. The below procedure will help you overcome this challenge.

Query to identify the entries older than 60 days:

SELECT * FROM log_records WHERE created_at < DATEADD(day, -60, GETDATE());
SELECT * FROM snapvol_timeseries WHERE created_at < DATEADD(day, -60, GETDATE());
SELECT * FROM system_messages WHERE created_at < DATEADD(day, -60, GETDATE());

The output of the query will be as follows:

Select SQL Query on AV DB

The challenge is if you ran the Delete query mention in the VMware KB it would work 100%. However, while deleting all the rows, the transaction log will run out of space. If you follow my procedure, the transaction log will still run out of space, but the amount of transaction logs flush you need to perform is minimized to a great extend.

This query will delete the rows in the batches of 10,000, allowing some room to perform the clean-up and no need to flush the transaction logs at every run. (In our scenario for 8 million records, I had to flush the transaction log twice.)

DECLARE @continue INT
DECLARE @rowcount INT
   
SET @continue = 1
WHILE @continue = 1
BEGIN
    PRINT GETDATE()
    SET ROWCOUNT 10000
    BEGIN TRANSACTION
    DELETE FROM log_records WHERE created_at < DATEADD(day, -60, GETDATE());
    DELETE FROM snapvol_timeseries WHERE created_at < DATEADD(day, -60, GETDATE());
    DELETE FROM system_messages WHERE created_at < DATEADD(day, -60, GETDATE());
    SET @rowcount = @@rowcount
    COMMIT
    PRINT GETDATE()
    IF @rowcount = 0
    BEGIN
        SET @continue = 0
    END
END

The output after the clean-up:

Delete SQL Query – AV DB

I hope you will find this SQL query helpful to perform App Volumes Database clean-up in a jiffy. My request if you further enhance the query or make it more creative, I hope you can share it back with me?

You can also follow the procedure to clean-up Horizon Events Database – VMware Horizon Events Database – Annual Clean-up (purge old data) | AskAresh

Thanks,
Aresh Sarkari

Tags: , , , ,

Script to replace VMware Unified Access Gateway certificates (ADMIN and Internet)

9 Jul

Our certificates are coming close to expiry, and we use VMware Unified Access Gateway for Internal and External traffic tunneling. This brings us to perform the replacement of the expiring certificates on 12 UAG Appliances. Performing this activity from the GUI is straight forward. However, we need to perform this activity on 12 appliances.

Thanks to Mark Benson for the motivation, and I went ahead and created a script to perform this activity at further ease, sit back, relax and have a coffee!

Pre-requisites:

  • You need the CAchain pem and RSA private key certificate output in one line. Please make sure you run the following command to grab the output in a single line
    • Linux/Unix command – awk ‘NF {sub(/\r/, “”); printf “%s\n”,$0;}’ cert-name.pem
    • Linux/Unix command – awk ‘NF {sub(/\r/, “”); printf “%s\n”,$0;}’ cert-namersapriv.pem
    • I saved the certificate files on a Linux machine and then ran the above command. Pasted the output in Notepad++, which is in one line.
    • Doco reference
    • The CAChain pem certificate should include (MainCA content, Subordinate Certificate content and Root Certificate content without any spaces between them.)
  • There are seperate API calls for the certificate replacement for the ADMIN and Internet facing. You can comment or un-comment the block as per your requirement
    • /rest/v1/config/certs/ssl/ADMIN
    • /rest/v1/config/certs/ssl/END_USER
  • The IP address or Hostname of the UAG Appliance along with the admin password.
##############################################################################################################################################
# Replace the ADMIN and Internet Facing certificate on the UAG Appliance
# Uncomment if you dont plan to do both the interfaces (Internet/ADMIN)
# Get the certificate in one line following this documentation 
# https://docs.vmware.com/en/Unified-Access-Gateway/3.10/com.vmware.uag-310-deploy-config.doc/GUID-870AF51F-AB37-4D6C-B9F5-4BFEB18F11E9.html
# Author - Aresh Sarkari (Twitter - @askaresh)
##############################################################################################################################################

#UAGServer Name or IP
$UAGServer = "10.1.1.1"

#Ignore cert errors
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'


#API Call to make the intial connection to the UAG Appliance##
$Uri = "https://$UAGServer`:9443/rest/v1/config/adminusers/logAdminUserAction/LOGIN"

$Username = "admin"
$Password = "enteryouradminpassword"

$Headers = @{ Authorization = "Basic {0}" -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $Username,$Password))) }

Invoke-WebRequest -SessionVariable DaLogin -Uri $Uri -Headers $Headers

#The PEM Certificate + Private Key in RSA Format
#The certificate has to be in online using linux command - awk 'NF {sub(/\r/, ""); printf "%s\\n",$0;}' cert-name.pem 
$certificatersaContent = "-----BEGIN RSA PRIVATE KEY-----\nMIIEo... followed by a large block of text...\n-----END RSA PRIVATE KEY-----\n"
$certificateContent = "-----BEGIN CERTIFICATE-----\nMIIEo... followed by a large block of text...\n-----END CERTIFICATE-----\n"

#Body to replace the certificate
$body = @{
  privateKeyPem = $certificatersaContent
  certChainPem = $certificateContent
} 

#Converting the Json and line breaks in strings 
#https://communary.net/2018/03/30/quick-tip-convertto-json-and-line-breaks-in-strings/
$Jsonbody = ($body | ConvertTo-Json).Replace('\\n','\n')

#API to replace the Admin Certificate of the UAG Appliance
#Please note that the Backtick ` is required in order to escape the colon
$outputadmin = Invoke-WebRequest -WebSession $DaLogin -Method Put -Uri "https://$UAGServer`:9443/rest/v1/config/certs/ssl/ADMIN" -Body $Jsonbody -ContentType "application/json" -Verbose

#API to replace the Internet facing Certificate of the UAG Appliance
#Please note that the Backtick ` is required in order to escape the colon
$outputenduser = Invoke-WebRequest -WebSession $DaLogin -Method Put -Uri "https://$UAGServer`:9443/rest/v1/config/certs/ssl/END_USER" -Body $Jsonbody -ContentType "application/json" -Verbose

GitHub – scripts/vmwareuagcertreplace at master · askaresh/scripts (github.com)

Observations:

  • The array within the $body has further line breaks, which needs to adjust. I had to spend a considerable amount of time. Thanks to this blog post which came in hand. Powershell function ConvertTo-Json
  • The Powershell function Invoke-Webrequest and the -URI I had to add the Backtick ` in order to escape the colon
  • The key of the above script is the CAChain certificate and RSA Private Key certificate to be available online.

I hope you will find this script useful to replace or change the certificate on the VMware Unified Access Gateway appliances. A small request if you further enhance the script or make it more creative, I hope you can share it back with me?

Thanks,
Aresh Sarkari

Tags: , , , ,