VMWare – Certificate Status Trigger alarm Alert

Back in December i replaced my Certificate on my VMWare Appliance with a our new Wildcard certificate.
But here in January, i got an alarm saying “Certificate Alarm” and it trigger it as an Alert.

Searching around for a solution, on what certificate that expire soon, i found this command:

for i in $(/usr/lib/vmware-vmafd/bin/vecs-cli store list); do echo STORE $i; /usr/lib/vmware-vmafd/bin/vecs-cli entry list --store $i --text | egrep "Alias|Not After"; done

It showing me that a backup mackine certificate is expiring

STORE MACHINE_SSL_CERT
Alias : __MACHINE_CERT
            Not After : Jan 15 14:20:36 2022 GMT
STORE TRUSTED_ROOTS
Alias : b4a1550d09628bfe628d2685fe24f2b67afb9855
            Not After : Jan 31 10:55:48 2025 GMT
Alias : 7a1f6edf1e16498c4eff9190fa5ef6742bbdde0e
            Not After : Dec  4 14:03:36 2026 GMT
Alias : 909c4328a37dcff3d72de88c51df6f63600fc3f3
            Not After : Dec  4 14:03:36 2026 GMT
Alias : 98c6a8dc887963ba3cf9c2731cbdd3f7de05ac2d
            Not After : Nov  6 12:23:33 2027 GMT
Alias : 91c489ecb94b64e2eeffe438a0ad3fc1465959a2
            Not After : Jan 15 14:20:36 2022 GMT
Alias : 4c27431717565a3a07f3e6d0032c4258949cf9ec
            Not After : Feb 20 10:00:00 2024 GMT
Alias : b1bc968bd4f49d622aa89a81f2150152a41d829c
            Not After : Jan 28 12:00:00 2028 GMT
STORE TRUSTED_ROOT_CRLS
Alias : bcd1699f713eabf1dbd8a21618ea6b0487adbdc8
STORE STS_INTERNAL_SSL_CERT
Alias : __MACHINE_CERT
            Not After : Dec  4 14:03:36 2026 GMT
STORE machine
Alias : machine
            Not After : Jan 31 10:55:48 2025 GMT
STORE vsphere-webclient
Alias : vsphere-webclient
            Not After : Jan 31 10:55:48 2025 GMT
STORE vpxd
Alias : vpxd
            Not After : Jan 31 10:55:48 2025 GMT
STORE vpxd-extension
Alias : vpxd-extension
            Not After : Jan 31 10:55:48 2025 GMT
STORE SMS
Alias : sms_self_signed
            Not After : Feb  3 11:51:48 2025 GMT
STORE BACKUP_STORE
Alias : bkp___MACHINE_CERT
            Not After : Jan 20 12:00:00 2021 GMT
Alias : bkp_machine
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vsphere-webclient
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vpxd
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vpxd-extension
            Not After : Jan 31 10:55:48 2025 GMT

So i exported this Machine certificate to a CRT file, and then deleted it with these commands

mkdir /certificates

/usr/lib/vmware-vmafd/bin/vecs-cli entry getcert --store BACKUP_STORE --alias bkp___MACHINE_CERT --output /certificates/old_machine.crt

/usr/lib/vmware-vmafd/bin/vecs-cli entry delete --store BACKUP_STORE --alias bkp___MACHINE_CERT -y

It then tell me that the certificate have been deleted

Deleted entry with alias [bkp___MACHINE_CERT] in store [BACKUP_STORE] successfully

Also running this command again

for i in $(/usr/lib/vmware-vmafd/bin/vecs-cli store list); do echo STORE $i; /usr/lib/vmware-vmafd/bin/vecs-cli entry list --store $i --text | egrep "Alias|Not After"; done

Show that the certificate have now gone.

Intune Exchange Connector version is not supported

Microsoft have public this article: https://support.microsoft.com/da-dk/help/4464186/microsoft-intune-exchange-connector-version-not-supported, that tell you to just download a new version from Microsoft Intune Admin portal.

But this warning messages could also happen if you allready have a connector installed on another server, even if microsoft says that the new connector will just replace the old one.
So if you get this warning, just after you have downloaded the software, and you have an old connector, delete the old one first.
Remember to check notice of what User Notification and Unmanaged device access where set to before deleting the old one

List all shares folders and thier permissions

<#
            .SYNOPSIS
            This script will list all shares on a computer, and list all the share permissions for each share.

            .DESCRIPTION
            The script will take a list all shares on a local or remote computer.

            .PARAMETER Computer
            Specifies the computer or array of computers to process

            .INPUTS
            Get-SharePermissions accepts pipeline of computer name(s)

            .OUTPUTS
            Produces an array object for each share found.

            .EXAMPLE
            C:\PS> .\Get-SharePermissions # Operates against local computer.

            .EXAMPLE
            C:\PS> 'computerName' | .\Get-SharePermissions

            .EXAMPLE
            C:\PS> Get-Content 'computerlist.txt' | .\Get-SharePermissions | Out-File 'SharePermissions.txt'

            .EXAMPLE
            Get-Help .\Get-SharePermissions -Full
#>

# Written by BigTeddy November 15, 2011
# Last updated 9 September 2012
# Ver. 2.0
# Thanks to Michal Gajda for input with the ACE handling.
[cmdletbinding()]

param([Parameter(ValueFromPipeline=$True,
    ValueFromPipelineByPropertyName=$True)]$Computer = '.')

$shares = gwmi -Class win32_share -ComputerName $computer | select -ExpandProperty Name
foreach ($share in $shares) {
    $acl = $null
    Write-Host $share -ForegroundColor Green
    Write-Host $('-' * $share.Length) -ForegroundColor Green
    $objShareSec = Get-WMIObject -Class Win32_LogicalShareSecuritySetting -Filter "name='$Share'"  -ComputerName $computer
    try {
        $SD = $objShareSec.GetSecurityDescriptor().Descriptor
        foreach($ace in $SD.DACL){
            $UserName = $ace.Trustee.Name
            If ($ace.Trustee.Domain -ne $Null) {$UserName = "$($ace.Trustee.Domain)\$UserName"}
            If ($ace.Trustee.Name -eq $Null) {$UserName = $ace.Trustee.SIDString }
            [Array]$ACL += New-Object Security.AccessControl.FileSystemAccessRule($UserName, $ace.AccessMask, $ace.AceType)
            } #end foreach ACE
        } # end try
    catch
        { Write-Host "Unable to obtain permissions for $share" }
    $ACL
    Write-Host $('=' * 50)
} # end foreach $share

MSSQL SP_WHO2 order by db / databases

Order sp_who2, after database with this commands:

declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);

 INSERT INTO @tempTable
EXEC sp_who2 

select *
from @tempTable order by DBName

Test SMTP TLS with OpenSSL with Auth

On a linux box type these two commands

echo -ne '[email protected]' | base64
echo -ne 'password' | base64

The command will return with the username and password in base64 format.
Example:

root@Linux-homeserver:~# echo -ne '[email protected]' | base64
dXNlcm5hbWVAZG9tYWluLnByZWZpeA==
root@Linux-homeserver:~# echo -ne 'password' | base64
cGFzc3dvcmQ=

So now you need to telnet with OpenSSL to the mailserver:

openssl s_client -debug -starttls smtp -crlf -connect mailserver.kennethdalbjerg.dk:465

After you have connection, you need to present yourself
Example:

ehlo host.kennethdalbjerg.dk

Some mailserver, do check if the ehlo is you corrert PTR record, for the IP address you are coming from, but most do not.

The mail server will return with:
250 mailserver.kennethdalbjerg.dk Hello dell1 [10.10.10.10]

You will now try to authenticated you self, if you don’t need to AUTH, please go to last code example in this blog.

AUTH LOGIN

And the mailserver will return with:
VXNlcm5hbWU6

Here you type you username in base64 format

dXNlcm5hbWVAZG9tYWluLnByZWZpeA==

And the mailserver will return with:
UGFzc3dvcmQ6

Please now type you password in base64 format

cGFzc3dvcmQ=

The server should now return with:
235 2.7.0 Authentication successful

After this you type normal email request to send an email
Example: (You should not type the line starting with a >, it are what the mailserver return to you.

mail from: [email protected]
> 250 2.1.0 Sender OK
rcpt to: [email protected]
> 250 2.1.5 Recipient OK
data
> 354 Start mail input; end with <CRLF>.<CRLF>
test
.
> 250 2.6.0 <604863cb-3dcc-4cee-9c1a-77fb6d951d43@mailserver.kennethdalbjerg.dk> [InternalId=9753870729240, Hostname=mailserver.kennethdalbjerg.dk] 1203 bytes in 1.581, 0,743 KB/sec Queued mail for delivery
quit
>221 2.0.0 Service closing transmission channel

Match Veeam replicas folder with VM

If you need to see what VM, that match to a VEEAM Replica folder on the disk system, this can be the solution
The veeam replica folder looks like this:

Let say that you need to find the VM, behind the folder “1cd328dbde6b42569a18b156570cb589”
Run this command on you SCVMM Powershell

get-vm | where location -like "*1cd328dbde6b42569a18b156570cb589*" | select name

This will give you the name of the VM behind the folder “1cd328dbde6b42569a18b156570cb589”

SQL Agent Job history – T SQL

Here are a Script, that you can use to extract the SQL Agent Job History

select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

This will also tell you the job steps

select 
 j.name as 'JobName',
 s.step_id as 'Step',
 s.step_name as 'StepName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobsteps s 
 ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h 
 ON s.job_id = h.job_id 
 AND s.step_id = h.step_id 
 AND h.step_id <> 0
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

Thanks to Chad Churchwell  – https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/

Update Service with new SSL certificate

Some service SSL certificate is adminstrate though netsh

start netsh, though a CMD, with just type: netsh

If you then type: http show sslcert, you can see SSL certificate information:

netsh>http show sslcert
SSL Certificate bindings:
-------------------------
IP:port                      : 0.0.0.0:443
Certificate Hash             : 5f5bd1c99549b2fd7d772d32a60b8a2ba38bedd5
Application ID               : {4dc3e181-e14b-4a21-b022-59fc669b0914}
Certificate Store Name       : (null)
Verify Client Certificate Revocation : Enabled
Verify Revocation Using Cached Client Certificate Only : Disabled
Usage Check                  : Enabled
Revocation Freshness Time    : 0
URL Retrieval Timeout        : 0
Ctl Identifier               : (null)
Ctl Store Name               : (null)
DS Mapper Usage              : Disabled
Negotiate Client Certificate : Disabled

So to update this ssl certificate you type:

netsh>http add sslcert ipport=0.0.0.0:443 certhash=5f5bd1c99549b2fd7d772d32a60b8a2ba38bedd5 appid={4dc3e181-e14b-4a21-b022-59fc669b0914}

Where 5f5bd1c99549b2fd7d772d32a60b8a2ba38bedd5 is the thumbprint of the SSL certificate. The thumbprint of a certificate can be found by running this powershell commands:

Get-ChildItem -path cert:\LocalMachine\My