Report mail alert

#!/bin/bash


# Define database credentials

DB_USER="your_user"

DB_PASS="your_password"

DB_SID="your_db"


# Email Configuration

EMAIL_TO="recipient@example.com"

EMAIL_SUBJECT="OEM Target Report - $(date +%Y-%m-%d)"

REPORT_FILE="oem_target_report_$(date +%Y%m%d).txt"


# Function to execute SQL query and format output

run_query() {

    SECTION_TITLE="$1"

    QUERY="$2"


    echo "=============================================" >> "$REPORT_FILE"

    echo "SECTION: $SECTION_TITLE" >> "$REPORT_FILE"

    echo "=============================================" >> "$REPORT_FILE"


    sqlplus -s "$DB_USER/$DB_PASS@$DB_SID" <<EOF >> "$REPORT_FILE"

SET PAGESIZE 500

SET LINESIZE 200

SET HEADING ON

SET FEEDBACK OFF

SET TRIMSPOOL ON

COLUMN "TARGET NAME" FORMAT A30

COLUMN "TARGET TYPE" FORMAT A20

COLUMN "PROPERTY NAME" FORMAT A25

COLUMN "PROPERTY VALUE" FORMAT A40

COLUMN "HOST NAME" FORMAT A30


$QUERY

EOF


    echo "" >> "$REPORT_FILE"

}


# Clear old report file

> "$REPORT_FILE"


# Define Queries (aligned OEM Management Target Report)

QUERY1="

SELECT target_name AS \"TARGET NAME\",

       target_type AS \"TARGET TYPE\",

       property_name AS \"PROPERTY NAME\",

       property_value AS \"PROPERTY VALUE\",

       host_name AS \"HOST NAME\"

FROM oem_targets

WHERE target_type = 'Database';"


QUERY2="

SELECT target_name AS \"TARGET NAME\",

       target_type AS \"TARGET TYPE\",

       property_name AS \"PROPERTY NAME\",

       property_value AS \"PROPERTY VALUE\",

       host_name AS \"HOST NAME\"

FROM oem_targets

WHERE target_type = 'Host';"


QUERY3="

SELECT target_name AS \"TARGET NAME\",

       target_type AS \"TARGET TYPE\",

       property_name AS \"PROPERTY NAME\",

       property_value AS \"PROPERTY VALUE\",

       host_name AS \"HOST NAME\"

FROM oem_targets

WHERE target_type = 'Listener';"


QUERY4="

SELECT target_name AS \"TARGET NAME\",

       target_type AS \"TARGET TYPE\",

       property_name AS \"PROPERTY NAME\",

       property_value AS \"PROPERTY VALUE\",

       host_name AS \"HOST NAME\"

FROM oem_targets

WHERE target_type = 'WebLogicServer';"


# Run queries and generate report

run_query "Database Targets" "$QUERY1"

run_query "Host Targets" "$QUERY2"

run_query "Listener Targets" "$QUERY3"

run_query "WebLogic Targets" "$QUERY4"


# Send report via email

mail -s "$EMAIL_SUBJECT" "$EMAIL_TO" < "$REPORT_FILE"


echo "OEM Target Report sent to $EMAIL_TO"



*******


Here’s a professionally worded, high-level summary you can share with your manager to project the automation work you've done:



---


Subject: OEM Target Property Validation Automation – Completed


Summary of Work Completed:


Automated OEM Property Validation:

Developed a shell script integrated with SQL*Plus to automatically extract and validate key OEM target properties (orcl_gtp_lifecycle_status, udtp_2, orcl_gtp_contact).


Multi-Query Consolidation into Unified Report:

Combined four distinct queries into a single, well-formatted report, categorizing outputs under meaningful sections (e.g., Missing Lifecycle Status, Missing Support Group, Missing Contact Info).


Structured Output with Alignment:

Implemented column formatting for consistent alignment of critical fields: Target Name, Target Type, Property Name, Property Value, and Host Name — making the report readable and manager-friendly.


Automated Daily Execution and Email Alerts:

Scheduled the script via cron to run daily, automatically emailing the report to stakeholders, reducing manual effort and ensuring timely visibility.


Improved Compliance & Data Accuracy:

This automation ensures property completeness across OEM targets, helping with CMDB alignment, audit readiness, and support coverage visibility.




---


Let me know if you'd like this adapted into an email draft or slid

e point for a presentation.


Comments

Post a Comment

Popular posts from this blog

Database growth

#!/bin/bash # Log file LOGFILE="db_growth_check.log" echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE echo "---------------------------------" >> $LOGFILE # Find running Oracle instances (excluding ASM and APX) ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID do     if [[ -n "$ORACLE_SID" ]]; then         echo "Processing database: $ORACLE_SID"         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check yearly database growth         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SELECT '$ORACLE_SID' AS DATABASE_NAME,  ...
Read more

P2

#!/bin/bash # Ensure ORACLE_SID is passed as an argument if [[ -z "$1" ]]; then     echo "Usage: $0 <ORACLE_SID>"     exit 1 fi export ORACLE_SID=$1 export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba" # Standard password (static) STANDARD_PWD="xyxyxyxyxytsts" # Fetch cloakware password dynamically NEWPSWD=$(/path/to/pwEcho.exe $ORACLE_SID WALLET) # Function to execute SQL and get database info get_db_info() {     SQL_FILE="/tmp/sql_output.txt"     $SQLPLUS <<EOF > $SQL_FILE SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 500 TRIMSPOOL ON WHENEVER SQLERROR EXIT SQL.SQLCODE SELECT name FROM v\$database; SELECT status FROM v\$encryption_wallet; SELECT NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') FROM dual; SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' E...
Read more

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more