SQLadmin

# my code 
from __future__ import print_function

import json
import sys
import os

print('Loading function')

SQLadmin

# Run any query on any RDS(MYSQL) while a cloudwatch alarm triggers.
# Version 1.0
# Author: SqlAdmin
# Twitter: https://twitter.com/SqlAdmin
# Blog: www.sqlgossip.com
#----------------------------------------------------------------------------------------------------------------
# Make a note:
# In this function we used to get the RDS instance Identifier from the SNS, It won't give the complete endpoint
# So in your account go and see the endpoint, it should like identifier+some-random-string+region+amazon.com
# The random string which is next to the Identifier is differ from other account. 

SQLadmin

-- Export the users
pg_dumpall -g > users.sql

-- Importing to another PostgresSQL
awk '/CREATE/' users.sql > migrate.sql

-- Import to RDS; It won't support super user and replication roles. But we can grant minimal superuser with rds_superuser
psql -h localserver -d postgres -t -c"select 'grant rds_superuser to '||rolname ||';' from pg_roles where rolsuper='t';" -P "footer=off" >> migrate.sql
sed -i -e's/NOSUPERUSER//g; s/SUPERUSER//g; s/NOREPLICATION//g; s//REPLICATION/g' migrate.sql
psql -h rds-endpoint -U adminuser -d postgres < migrate.sql

SQLadmin

# Lambda function to create partition for Cloudtrail log on daily basis.
# You need to schedule it in AWS Lambda.

'''
-------------------------------------------
AWS Athena Create Partitions Automatically
-------------------------------------------

Version 1.0
Author: SqlAdmin

SQLadmin

# Lambda function / Python to create athena partitions for Cloudtrail log between any given days.
# If you run this in AWS Lambda then it can't able to ceate all the partitions.
# Because lambda can run any functions up to 5mins. But create partition query will take avg 6 secs.
# I did a benchmark on lambda, it created upto 3 months of partitions on 16 regions. 

'''
-----------------------------------------------------------------
AWS Athena Create Partitions Automatically For Given Any TWO DAYS
-----------------------------------------------------------------
Version 1.0

SQLadmin

use sqladmin;

DROP PROCEDURE
IF EXISTS archive;
delimiter //
  CREATE PROCEDURE
    archive()
  begin
    DECLARE rows INT;
    DECLARE rows_deleted INT;

SQLadmin

use sqladmin;

DROP PROCEDURE
IF EXISTS sqladmin_archive;
delimiter //
  CREATE PROCEDURE
    sqladmin_archive(IN archive_dbname varchar(100), IN archive_table varchar(100), IN archive_column varchar(100), IN archive_date varchar(100))

  begin
    DECLARE rows INT;

SQLadmin

#!/bin/bash

# pass variables
archive_dbname=$1
archive_table=$2
archive_column=$3
days_to_archive=$4
archive_date="'"`date +'%Y-%m-%d' --date="-$days_to_archive day"`"'"
where_clause=$archive_column'<='$archive_date
dump_file=$archive_table_`date +'%Y-%m-%d' --date="-$days_to_archive day"`".sql"

SQLadmin

#!/bin/bash
# ----------------------------------------------------------
# RECREATE REDSHIFT CLUSTERS FROM RUNNING CLUSTER'S SNAPSHOT
# ----------------------------------------------------------

# Version: 1.0
# Created by: @SQLadmin

# Create IAM user with keys assign Redshift nessessary access 
# and SES send raw email access

SQLadmin

#Retrieve the AWS instance ID, keep trying until the metadata is available
$instanceID = "null"
while ($instanceID -NotLike "i-*") {
Start-Sleep -s 3
$instanceID = invoke-restmethod -uri http://169.254.169.254/latest/meta-data/instance-id
}
#Pass Domain Creds
$username = "sqladmin\Administrator"
$password = "mypassword" | ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object -typename System.Management.Automation.PSCredential($username, $password)