Publish CRLs

1. Login to offline RootCA and create a new crl file:

    certutil –crl

2. Copy CRL file from C:\Windows\System32\Certsrv\CertEnroll\ to a USB

3. on Issuing servers upload crl file to C:\inetpub\wwwroot\pki and other locations that CRL should be uploaded to like share or AD.

Publish in AD with: certutil –dspublish -f C:\CRKRoot.crl

Some Kusto queries

 1. Find resource using TLS lower than 1.2:

resources

| where type in (

    'microsoft.web/sites/config',

    'microsoft.storage/storageaccounts',

    'microsoft.sql/servers',

    'microsoft.network/applicationgateways',

    'microsoft.cdn/profiles/endpoints',

    'microsoft.apimanagement/service',

    'microsoft.network/virtualnetworkgateways',

    'microsoft.signalrservice/signalr',

    'microsoft.servicebus/namespaces',

    'microsoft.containerservice/managedclusters'

)

| extend TlsVersion = case(

    type == 'microsoft.web/sites/config', properties.minTlsVersion,

    type == 'microsoft.storage/storageaccounts', properties.minimumTlsVersion,

    type == 'microsoft.sql/servers', properties.minimalTlsVersion,

    type == 'microsoft.network/applicationgateways', properties.sslPolicy.minProtocolVersion,

    type == 'microsoft.cdn/profiles/endpoints', properties.tlsSettings.protocolType,

    type == 'microsoft.apimanagement/service', tostring(properties.protocols),

    type == 'microsoft.network/virtualnetworkgateways', tostring(properties.vpnClientConfiguration.vpnClientProtocols),

    type == 'microsoft.signalrservice/signalr', properties.tls.minimalTlsVersion,

    type == 'microsoft.servicebus/namespaces', properties.minimumTlsVersion,

    type == 'microsoft.containerservice/managedclusters', 'TLS managed by individual deployments',

    'Unknown')

| where TlsVersion !contains "1.2" and TlsVersion != "Unknown" and TlsVersion != "TLS1_2"

| project ResourceType = type, 

          ResourceName = name, 

          Location = location, 

          TlsVersion

2.Find blocked queried in app gateway

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK"
| where Category == "ApplicationGatewayFirewallLog"
| where action_s == "Matched"
| project
    TimeGenerated,
    ClientIP = clientIp_s,
    RequestURI = requestUri_s,
    RuleId = ruleId_s,
    RuleSetType = ruleSetType_s,
    Action = action_s,
    Message,
    Hostname = hostname_s,
    TransactionId = transactionId_g
| sort by TimeGenerated desc
 

3. Find timeouts:

AzureDiagnostics

| where Category == "ApplicationGatewayAccessLog"

| where httpStatus_d in (408, 504, 502)  // Common timeout-related HTTP status codes

| where host_s == "ylukscaleprod.eu.yusen-logistics.com"

4. Statistics, success rate in every 5 minute slot:

AzureDiagnostics

| where ResourceType == "APPLICATIONGATEWAYS"

| where Category == "ApplicationGatewayFirewallLog" or Category == "ApplicationGatewayAccessLog"

| where TimeGenerated >= ago(30d) // Adjust timeframe as needed

| where listenerName_s == "https-ylukscaleprod-eu-yusen-logisitcs-com" // Filter for specific listener if needed

| extend ListenerName = listenerName_s

| extend ResponseCode = httpStatus_d

| extend IsHealthy = iff(ResponseCode >= 200 and ResponseCode < 400, true, false)

| summarize 

    TotalRequests = count(),

    FailedRequests = countif(not(IsHealthy)),

    SuccessRate = (count() - countif(not(IsHealthy))) * 100.0 / count()

    by bin(TimeGenerated, 5m), ListenerName, _ResourceId

| extend IsDown = iff(SuccessRate < 50, true, false) // Define downtime threshold

| order by TimeGenerated desc

5. Success rate in last 7 dates:

AzureDiagnostics

| where ResourceType == "APPLICATIONGATEWAYS"

| where Category == "ApplicationGatewayFirewallLog" or Category == "ApplicationGatewayAccessLog"

| where TimeGenerated >= ago(30d) // Adjust timeframe as needed

| where listenerName_s == "https-ylukscaleprod-eu-yusen-logisitcs-com" // Filter for specific listener if needed

| extend ListenerName = listenerName_s

| extend ResponseCode = httpStatus_d

| extend IsHealthy = iff(ResponseCode >= 200 and ResponseCode < 400, true, false)

| summarize 

    TotalRequests = count(),

    FailedRequests = countif(not(IsHealthy)),

    SuccessRate = (count() - countif(not(IsHealthy))) * 100.0 / count()

    by bin(TimeGenerated, 5m), ListenerName, _ResourceId

| extend IsDown = iff(SuccessRate < 50, true, false) // Define downtime threshold

| order by TimeGenerated desc

6. Statistics with error code failures and successes:

AzureDiagnostics

| where ResourceProvider == "MICROSOFT.NETWORK"

| where Category == "ApplicationGatewayFirewallLog"

| where action_s == "Matched"

| where hostname_s == "ylukscaleprod.eu.yusen-logistics.com"

| project

    TimeGenerated,

    ClientIP = clientIp_s,

    RequestURI = requestUri_s,

    RuleId = ruleId_s,

    RuleSetType = ruleSetType_s,

    Action = action_s,

    Message,

    Hostname = hostname_s,

    TransactionId = transactionId_g

| sort by TimeGenerated desc

Converting VM to generation 2 in Hyper-v

 1. Create a VM from vagrant, this is gen1.

2. Although disk is VHDX, export it, add more space in Hyper-V

3. Attach this drive to old VM and expand drive is disk management.

4. Convert to GPT using MBR2GPT.

    mbr2gpt.exe /validate /disk:1 /allowFullOS

    mbr2gpt.exe /convert /disk:1 /allowFullOS 

This will create a partition at the end.

5. Create a new VM (Gen2) and use exported drive.

openssl basics

 1. check web site expiration: 

 

echo test | openssl s_client -connect google.com:443 | openssl x509 -noout -dates

2. PFX to PEM convertion (PFX do not have any pass):

 

openssl pkcs12 -in cert-in.pfx -out cert-out.pem -nodes 

3. copying with scp:

scp -i C:\Users\name\OneDrive\ssh\MyPrv.pem .\file.pem remote-host.com:\tmp

4. Logging to remote host with local port forward

az ssh vm --resource-group rg-poc --name ubuntu1 -- -L 1433:localhost:1433

5. Add a password to PFX file:

openssl pkcs12 -in  kv-msr-acme-letw.pfx -out c:\temp\temp1.pem -nodes

Press Enter.

.\openssl pkcs12 -export -in c:\temp\temp1.pem -out c:\temp\new_protected1.pfx -passout pass:strongpass

6. Convert cer file into pem;

openssl x509 -inform der -in .\RootCA.cer -out .\RootCA.pem

solving issues The following signatures were invalid: EXPKEYSIG ABF5BD827BD9BF62

 how to solve this :

The following signatures were invalid: EXPKEYSIG ABF5BD827BD9BF62 nginx signing key <signing-key@nginx.com>

1. vi /etc/apt/sources.list.d/nginx.list

Bold is a key used to check, it must be updated

deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg] http://nginx.org/packages/ubuntu/ focal nginx
# deb-src http://nginx.org/packages/ubuntu/ focal nginx

Update key:

curl -s https://nginx.org/keys/nginx_signing.key | gpg --dearmor > /usr/share/keyrings/nginx-keyring.gpg

clean up content from Azure devops output in notepad++

 use regular expression and this "\033\[[0-9;]*[mGK]"

mysql replication

 generally follow this procedure, it's working fine: https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-data-in-replication

install mydumper on a MySQL source server:

apt-get install mydumper

Turn on binary logging. In the mysqld section, add following line:

log-bin=mysql-bin.log

Restart the server

Set your DB to read only mode:

mysql -uUserName -pPassWord -DDatabaseName <<<"FLUSH TABLES WITH READ LOCK;"

mysql -uUserName -pPassWord -DDatabaseName <<<"SET GLOBAL read_only = 1;"

 

Check your master status, run this at the beginnig of the running the backup:

mysql -uUserName -pPassWord -DDatabaseName <<<"show master status;"

mysql: [Warning] Using a password on the command line interface can be insecure.

File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set

mysql-bin.000084        522687808

Dump required databases:

mydumper --regex='^(?!(backup|percona|mysql|sys|information_schema|performance_schema))'  --host=localhost --user=UserName --password=PassWord --outputdir=backup --rows=500000 --compress --build-empty-files --threads=16 --compress-protocol --kill-long-queries --lock-all-tables -L mydumper-logs.txt

Check again after running the backup:

mysql -uUserName -pPassWord -DDatabaseName <<<"show master status;"

This should have a same value as previous.

When backup is finished unlock tables:

mysql -uUserName -pPassWord -DDatabaseName <<<"SET GLOBAL read_only = OFF;"

mysql -uUserName -pPassWord -DDatabaseName <<<"UNLOCK TABLES;"

Restore databases: 

myloader -h 'mysql.mysql.database.azure.com' --user=UserName --password=PassWord --directory=/var/lib/mysql/backup --queries-per-transaction=500 --threads=16 --compress-protocol --verbose=3 -e 2>myloader-logs.txt

Create a user on source server:

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';

GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';

Setup synchronization on destination machine:

CALL mysql.az_replication_change_master('yourVmName.uksouth.cloudapp.azure.com', 'syncuser', 'yourpassword', 3306, 'mysql-bin.000084', 522687808, '');

Check status: 

show slave status;

Start synchronization:

CALL mysql.az_replication_start;

Troubleshooting:

Example error: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000084, end_log_pos 526850979; Error executing row event: 'Table 'Table1' doesn't exist'

Solution: filter this Table1 on Azure portal:

Check for errors: 

select * from performance_schema.replication_applier_status_by_worker;