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;

copy SSRS report

 1. Download https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/features/reporting-services/ssrs-migration-rss/ssrs_migration.rss

2. execute C:\rss>"C:\Program Files\Microsoft SQL Server Reporting Services\Shared Tools\RS.exe" -i ssrs_migration.rss -e Mgmt2010 -s https://source/reportserver -v ts="http://destination/Reportserver"

tips for securing environment

 1. Pass the hash mitigation:

All windows services have their own SIDs, that can be used to provide access to internal resources

Access tokens are viewed with "whoami /all", this consists of SID, group membership and priviliged (not matter if those are in disabled state)

Security descriptor is a lock (as opposed to access tokens), those define who has access to which resource, it cotains DACL (actual permision list) and SACL (auditing info) and ownership info.

DACL is managed by someone who have a "Full Controll" permissions, SACL can be managed only by someone who has a user privilige (usually means belonging to local admin group)

Rule sets are read from top down and inherted have lower priority than local:

Deny always wins if this set by a group as opposed to inherited permissions.

Priviliges always beat permissions.

Even if a domain admin set a DENY permission on "take ownership" permission for a local admin, because of this privilige "Take ownership of files and other objects"

Tip for connecting to a user session that is disconnected: start taskmgr as SYSTEM and connect, you will not be asked for a password.
 

app locker

 

https://msendpointmgr.com/2021/08/13/log-analytics-applocker-better-together/

Use GPO and inheritance of GPO. Create two AD groups:

* dl_AppLocker_Enforced

* dl_AppLocker_Audit

Add the same rules to those, where one group would Enforce and second one does Audit only. Rules:

 - App Locker Rules, Hardening settings

 - Main Switch - this applies to GPO inheritance, see below, the last (nested) GPO (Server, Helpdesk,Test Computers) is a main switch and it decides if all rules are enforced or audited only 

GPO: Contoso.local

 |   Enforcement: Not configured (rules are enforced) X rules

 |   |

 |   GPO: Servers 

 |   Enforcement: Not configured (rules are enforced) Y rules, total X+Y rules are enforced

 |

 |  GPO: Helpdesk

 |   Enforcement: audit only, Z rules, total X +Z rules are audited, but not enforced

 |   

 |  GPO Test computers

    Enforcement:enforce rules, 0 rules, X rules are enforced 

Concept: "AppLocker Hardining" GPO is setup in top level, the only place

GPO name                                         Location         Purpose

AppLocker Default Rules                       Top                 Those are default rules, that allow Windows to                                                                                             start. DO NOT EDIT

AppLocker Hardening                         Top                 only deny rules, add all LOLBins there

"AppLocker Rules Servers

AppLocker Rules PAW Computers" each OU         allow rules, notepad++ for example

AppLocker Enforced                         each OU         This is Main Switch

AppLocker Audit Only                         single OU

To change a mode for a problematic computer, you move it from one OU to another OU (AuditOnly)

Rule types:

* Path rules (examples: C:\test\*, *\lolbin.exe,c:\users\*\appdata\local\adobe\*,\\dc1\SYSVOL\*,\\dc2\SYSVOL\*,\\server\share1,\\server1.contoso.local\share1\*,\\10.0.0.1\share1\*,K:\*)

* Hash rules

* Publisher rules

Publisher rules - best option after path rules

cert must be trusted abd valid, timestamp exists and valid this can be a paid or an internal CA

stick to company-level (publisher) instead of cetrain filename or versions

BTW: Everyone in Windows is everyone except unathenticaged users
BTW2. This blocks 95% of malware from running

hash rules are ONLY used when path or publisher rules cannot be applied, avoid it.

General rules:

* look for folders not files,

* look for publishers instead of hashes

* audit installation with AccessChk

* no admin rights

UnInstallation of obsolete .net

 Start-BitsTransfer "https://github.com/dotnet/cli-lab/releases/download/1.6.0/dotnet-core-uninstall-1.6.0.msi" -Destination c:\temp\dotnet-core-uninstall-1.6.0.msi

.\dotnet-core-uninstall-1.6.0.msi

 cd 'C:\Program Files (x86)\dotnet-core-uninstall\'

.\dotnet-core-uninstall list

  28 .\dotnet-core-uninstall dry-run

  30 .\dotnet-core-uninstall dry-run  --hosting-bundle 3.1.26

  31 .\dotnet-core-uninstall remove  --hosting-bundle 3.1.26

  32 .\dotnet-core-uninstall list

  33 .\dotnet-core-uninstall dry-run  --runtime 3.1.26

  34 .\dotnet-core-uninstall remove  --runtime 3.1.26

Get-WmiObject -Class Win32_Product | Format-Table

Analysis of storage analytics logs

 https://learn.microsoft.com/en-us/azure/storage/common/storage-analytics

1. Setup Azure Log analytics, see above

2. Download blobs from $log container, use storage explorer.

3. Build a single csv using this file. 

 .\XLog2CSV.ps1 -inputFolder "C:\temp\orthanc\blob\2023\11\" -outputFile c:\temp\november.csv

4. Import CSV into sql.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nov2023]') AND type in (N'U'))

DROP TABLE [dbo].[nov2023]

GO

CREATE TABLE [dbo].[nov2023](

    [Log_Version] [float] NOT NULL,

    [Transaction_Start_Time] [varchar](50) NOT NULL,

    [REST_Operation_Type] [varchar](50) NOT NULL,

    [Request_Status] [varchar](50) NOT NULL,

    [HTTP_Status_Code] [smallint] NOT NULL,

    [E2E_Latency] [smallint] NOT NULL,

    [Server_Latency] [smallint] NOT NULL,

    [Authentication_type] [varchar](50) NOT NULL,

    [Requestor_Account_Name] [varchar](50) NULL,

    [Owner_Account_Name] [varchar](50) NULL,

    [Service_Type] [varchar](50) NULL,

    [Request_URL] [varchar](1000) NULL,

    [Object_Key] [varchar](150) NULL,

    [Request_ID] [varchar](50) NULL,

    [Operation_Number] [tinyint] NULL,

    [Client_IP] [varchar](50) NULL,

    [Request_Version] [date] NULL,

    [Request_Header_Size] [smallint] NULL,

    [Request_Packet_Size] [int] NULL,

    [Response_Header_Size] [smallint] NULL,

    [Response_Packet_Size] [int] NULL,

    [Request_Content_Length] [int] NULL,

    [Request_MD5] [varchar](50) NULL,

    [Server_MD5] [varchar](50) NULL,

    [ETag] [varchar](50) NULL,

    [Last_Modified_Time] [varchar](50) NULL,

    [ConditionsUsed] [varchar](50) NULL,

    [User_Agent] [varchar](150) NULL,

    [Referrer] [varchar](1) NULL,

    [Client_Request_ID] [varchar](200) NULL,

    [User_Object_ID] [varchar](50) NULL,

    [Tenant_ID] [varchar](50) NULL,

    [Application_ID] [varchar](50) NULL,

    [Audience] [varchar](50) NULL,

    [Issuer] [varchar](100) NULL,

    [User_Principal_Name] [varchar](100) NULL,

    [Unused_Field] [varchar](1) NULL,

    [Authorization_Detail] [varchar](1200) NULL

) ON [PRIMARY]

GO

bulk insert [dbo].[nov2023] from "z:\november.csv" with ( FORMAT = 'CSV');

5.Analysis

--SELECT TOP (1000) * FROM [orthanc].[dbo].[output1]

--  select COUNT(*) from output1

--  select

-- Get IP address count

--select  CASE charindex(':',Client_IP)

--     WHEN 0 THEN Client_IP

--    else left(Client_ip,charindex(':',Client_IP)-1)

--END as Client_IP, count(*) as cnt

--from output1

--group by CASE charindex(':',Client_IP)

--     WHEN 0 THEN Client_IP

--    else left(Client_ip,charindex(':',Client_IP)-1)

--END

--order by 2 desc

select  CASE charindex(':',Client_IP)

     WHEN 0 THEN Client_IP

    else left(Client_ip,charindex(':',Client_IP)-1)

END as Client_IP

from output1