CA SDM ActivityNotifications-Events-Macros

The script was generated from chatGPT from an image showing SQL statement.

Initial Prompt used:

Use the last image to build a script that executes sql using sqlcmd allowing user to enter connection string details via cmdline argvs.
The script produces a raw.csv file along with a report style using mark down format. saving both files to –output argv with names that are date-time stamped

import os
import sys
import subprocess
import datetime
import csv
import argparse

def run_sql_query(server, port, mdb, user, password, output_dir):
    # Get the current date-time for file naming
    timestamp = datetime.datetime.now().strftime('%Y%m%d-%H%M%S')

    # Define the filenames for the output files
    raw_csv_file = os.path.join(output_dir, f"raw_{timestamp}.csv")
    markdown_report_file = os.path.join(output_dir, f"report_{timestamp}.md")

    # SQL command using sqlcmd
    sql_query = """
    SELECT splmac.persid AS 'splmac.persid',
           splmac.type AS 'splmac.type',
           splmac.fragment AS 'splmac.fragment',
           splmac.usr_string2 AS 'splmac.usr_string2',
           act_type.sym AS 'Called by evt.sym',
           act_type.sym AS 'Attached by act_type.sym',
           act_type.sym AS 'On Object Type evt.obj_type',
           evt.condition AS 'Has Condition evt.condition',
           (SELECT splmac.fragment FROM splmac WHERE splmac.persid=evt.condition) AS 'Macro Code evt.condition -> splmac.fragment',
           STUFF((SELECT ', ' + atomic_cond.cond_code FROM atomic_cond WHERE atomic_cond.owning_macro = splmac.persid 
                  ORDER BY atomic_cond.sequence FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS 'Conditions'
    FROM usp_lrel_aty_events evt
    INNER JOIN act_type ON usp_lrel_aty_events.aty = act_type.code
    INNER JOIN usp_lrel_true_action_act_t ON usp_lrel_aty_events.evt = usp_lrel_true_action_act_t.evt
    INNER JOIN splmac ON usp_lrel_true_action_act_t.macro = splmac.persid
    ORDER BY splmac.type, splmac.usr_string2, SUBSTRING(splmac.fragment, 0, 15);
    """

    # Build the sqlcmd command
    sqlcmd_command = f'sqlcmd -S tcp:{server},{port} -d {mdb} -U {user} -P {password} -Q "{sql_query}" -s "," -o "{raw_csv_file}" -W'

    # Run the command using subprocess
    try:
        subprocess.run(sqlcmd_command, check=True, shell=True)
        print(f"Query executed successfully. Raw data saved to {raw_csv_file}")
    except subprocess.CalledProcessError as e:
        print(f"Error executing query: {e}")
        sys.exit(1)

    # Generate the markdown report
    generate_markdown_report(raw_csv_file, markdown_report_file)

def generate_markdown_report(csv_file, markdown_file):
    with open(csv_file, newline='', encoding='utf-8') as csvfile:
        reader = csv.reader(csvfile)
        headers = next(reader)  # Get the headers from the CSV

        with open(markdown_file, 'w', encoding='utf-8') as mdfile:
            # Write report title
            mdfile.write(f"# SQL Query Report - {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
            
            # Write headers in Markdown table format
            mdfile.write('| ' + ' | '.join(headers) + ' |\n')
            mdfile.write('| ' + ' | '.join(['---'] * len(headers)) + ' |\n')
            
            # Write rows in the Markdown table format
            for row in reader:
                mdfile.write('| ' + ' | '.join(row) + ' |\n')

    print(f"Markdown report generated: {markdown_file}")

def main():
    # Define default values
    default_server = 'localhost'
    default_port = '1433'
    default_mdb = 'myDatabase'
    default_user = 'sa'
    default_password = 'password'
    default_output_dir = './output'

    # Setup argparse
    parser = argparse.ArgumentParser(description="SQL Report Generator")
    parser.add_argument('--server', default=default_server, help='SQL Server name or IP address')
    parser.add_argument('--port', default=default_port, help='SQL Server port')
    parser.add_argument('--mdb', default=default_mdb, help='Database name')
    parser.add_argument('--user', default=default_user, help='SQL Server username')
    parser.add_argument('--pass', default=default_password, help='SQL Server password')
    parser.add_argument('--output', default=default_output_dir, help='Output directory for CSV and report')

    # Parse arguments
    args = parser.parse_args()

    # Create output directory if it doesn't exist
    if not os.path.exists(args.output):
        os.makedirs(args.output)

    # Run the query
    run_sql_query(args.server, args.port, args.mdb, args.user, args.pass, args.output)

if __name__ == '__main__':
    main()

Conversation