joostvanveen

## Reindex catalogrule prices in Magento 2

## Navigate to Magento root, e.g.:
cd ~/magento2

## Reindex the catalogrule prices
php bin/magento indexer:reindex catalogrule_product

## For this indexer to correctely index the new productprices, the 
## catalogrules need to be indexed first. This usually happens 

joostvanveen

-- Orders with more than one shipment
SELECT orders.increment_id AS ordernummer, orders.created_at, count(*) as `Aantal verzendingen` FROM sales_shipment as shipments
LEFT JOIN sales_order AS orders ON orders.entity_id = shipments.order_id
GROUP BY shipments.order_id
HAVING `Aantal verzendingen` > 1
ORDER BY orders.created_at;

-- Orders with more than 1 invoice
SELECT orders.increment_id AS ordernummer, orders.created_at, count(*) as `Aantal facturen` FROM sales_invoice as invoices
LEFT JOIN sales_order AS orders ON orders.entity_id = invoices.order_id

joostvanveen

## eval() is used to executed shell commands through PHP
grep -ri "eval(" $PWD --include *.php

## exec() is used to executed shell commands through PHP
grep -ri "exec(" $PWD --include *.php

## Malicious code is often obfuscated through base64 encoding
grep -ri "base64" $PWD --include *.php

## Malicious code is often obfuscated through gzip

joostvanveen

-- Get all stores where orders, invoices, etc use the wrong prefix
-- E.g. the '2' in order number '200002345'

SELECT store_id, prefix, entity_type, sequence_table FROM sales_sequence_meta
JOIN sales_sequence_profile ON sales_sequence_profile.meta_id = sales_sequence_meta.meta_id
WHERE prefix <> store_id
ORDER BY store_id;

-- Get all stores that use the wrong prefix autoincrement table for orders, invoices, etc.
-- E.g. the '2345' in order number '200002345'

joostvanveen

-- Inspect the default charset and collation for your database
SELECT default_character_set_name FROM information_schema.schemata WHERE schema_name = 'MYDATABASE_NAME';

-- Alter the default charset and collation for your database
-- CAREFUL: when you change the charset for your database, make sure the data in your tables matches that character set. 
-- If it is not, follow these steps: http://www.alphadevx.com/a/420-Converting-a-MySQL-database-from-latin1-to-utf8
ALTER DATABASE MYDATABASE_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Alter the default charset and collation for your database
ALTER TABLE MYTABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

joostvanveen

# Loop though all rotated Directadmin access logs and get the top 20 list of User Agents
# This can be used to identify bots
zcat /home/USER/domains/DOMAIN/logs/LOGNAME.tar.gz* | awk -F\" '{print $6}' | sort | uniq -c | sort -nr | head -20

# Get top 20 IP addresses
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $1}' | sort | uniq -c | sort -nr | head -20

# Get top 20 most visited URLs
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $4}' | sort | uniq -c | sort -nr | head -20

joostvanveen

# Delete log files containing a certain string. 
# Handy to clean up Magento reports after committing a fix.
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -delete

# If you ant to chcek first:
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -find

joostvanveen

# Delete all cached images older than 365 days
find ~/public/media/catalog/product/cache/* -type f -atime +365 -exec rm {} \;

# Remove product images that are not present in the database
magerun media:images:removeorphans

joostvanveen

# Display the top 20 visitors by IP address from a single log file
awk '{print $2}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -20

# Display the top 40 visitors by IP address from all rotated gzipped log files
zcat /var/log/nginx/access.log* | awk '{print $2}' | sort | uniq -c | sort -nr | head -40

# Find IP adrdresses for all users that created a custoter account for Magento
zcat /var/log/nginx/access.log* | awk -v OFS='\t' '/\/customer\/account\/createpost?/ {print $2, $21}' | sort | uniq -c | sort -nr | head -40

joostvanveen

## Split large SQL files into separate files for each table if every tabel starts with a 'DROP TABLE IF EXISTS' statement
csplit -k $PWD/filename.sql  '/^DROP TABLE IF EXISTS .*/' '{900}'

## Split large SQL files into separate files for each table if every tabel starts with a 'CREATE TABLE' statement
csplit -k $PWD/filename.sql  '/^CREATE TABLE .*/' '{900}'