Skip to content

Instantly share code, notes, and snippets.

Joost van Veen joostvanveen

View GitHub Profile
@joostvanveen
joostvanveen / top-20-user-agents-and-ips-from-directadmin-logs.sh
Created Feb 17, 2019
Loop though all rotated access logs and get the top 20 list of User AgentsThis can be used to identify bots
View top-20-user-agents-and-ips-from-directadmin-logs.sh
# 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
joostvanveen / delete_files_continaing_a_string.sh
Last active Jun 26, 2018
Delete log files containing a certain string. Handy to clean up Magento reports after committing a fix.
View delete_files_continaing_a_string.sh
# 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
joostvanveen / clean_magento_product_images.sh
Created Apr 25, 2018
Clean old product images cache and orphaned product images for Magento 1
View clean_magento_product_images.sh
# 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
joostvanveen / count_visitors_by_ip.sh
Last active Mar 22, 2018
Display the top X visitors by IP address from a single or all nginx access log files
View count_visitors_by_ip.sh
# 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
joostvanveen / csplit.sh
Created Mar 9, 2018
Split large SQL files into separate files for each table
View csplit.sh
## 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}'
View remove-table-prefixes.sql
-- STEP 1: Set @database, @old_prefix and @new_prefix
-- STEP 2: Execute the query
-- STEP 3: Copy the string that was genereated by the query
-- STEP 4: Run the generated query to rename all tables
-- STEP 5: Use you Party Gun to make it rain!
SET SESSION group_concat_max_len = 999999999;
SET @database = "databasename";
SET @old_prefix = "ma2_";
SET @new_prefix = "";
@joostvanveen
joostvanveen / linux_find_size_modified_date_and_remove_files.sh
Last active Jan 30, 2019
Linux commands to find all files modified older than a certain date or time, remove, find largest files or folder in a human readable way.
View linux_find_size_modified_date_and_remove_files.sh
## NOTE: when finding file by time, use one of the following flags:
# ctime : the time a file was changed, in hours. On most systems this timestamp cannot be altered by any user, so it is fairly reliable
# atime : the time a file was last accessed, in hours. This is also set by the system.
# mtime : the modified time of a file, in hours. This can be set by any user. So if you are looking for mailcious files, never use this flag.
# cmin : same as mtime, but in minutes.
# amin : same as atime, but in minutes.
# mmin : same as mtime, but in minutes.
# Find all files in current directory changed more than 8 hours ago (480 minutes)
find $PWD -mindepth 1 -type f -cmin +480
@joostvanveen
joostvanveen / rsync.sh
Created Dec 13, 2017
Crude bash script to Rsync source folder to destination folder, only certain folders and files
View rsync.sh
#!/usr/bin/env bash
# Magento 2 Deployment Script
# Author: Joost van Veen
usage="$(basename "$0") [-h] source-directory destination-directory folders-to-sync files-to-sync
Rsync folders and files from one folder to another
where:
-h show this help text
@joostvanveen
joostvanveen / magento_2_unrequire_firstname_lastname_etc.sql
Created Nov 3, 2017
Set firstname, lastname, email, telephone as unrequired for Magento 2
View magento_2_unrequire_firstname_lastname_etc.sql
-- DEFAULT MAGENTO: REQUIRE FIRSTNAME, LASTNAME, EMAIL, TELEPHONE
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'firstname';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'lastname';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'email';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'telephone';
-- DO NOT REQUIRE FIRSTNAME, LASTNAME, EMAIL, TELEPHONE
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'firstname';
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'lastname';
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'email';
@joostvanveen
joostvanveen / truncate_orders_from_magento_2.sql
Created Nov 3, 2017
Truncate all orders from Magento 2
View truncate_orders_from_magento_2.sql
SET FOREIGN_KEY_CHECKS = 0;
-- TRUNCATE ORDERS
TRUNCATE TABLE `PREFIX_gift_message`;
TRUNCATE TABLE `PREFIX_quote`;
TRUNCATE TABLE `PREFIX_quote_address`;
TRUNCATE TABLE `PREFIX_quote_address_item`;
TRUNCATE TABLE `PREFIX_quote_id_mask`;
TRUNCATE TABLE `PREFIX_quote_item`;
TRUNCATE TABLE `PREFIX_quote_item_option`;
You can’t perform that action at this time.