Parse diff output into very detailed & summary report


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parse diff output into very detailed & summary report
# 1  
Old 02-08-2012
Parse diff output into very detailed & summary report

Hello all;

I'll try an explain my dilemma as best I can. But first some background:

1- I am suppose to compare a database to itself before and after changes; basically generate audit trail report.
2- This database contains "RULES" (the id field) that we use for transmitting files.
3 - The table fields are: id, t_client, t_filelocation, t_remotescript, t_destination, t_enabled, t_search, t_compression

What I have so far script-wise:

#!/usr/bin/perl

# PERL MODULES USED
use strict;
use warnings;
use DBI;
use DBD::mysql;

# CONFIG VARIABLES
my $DB='ops_filetransfer';
my $HOST='pele';
my $user='dslops';
my $pass='dslops72';
my $table='lsof_conf';

# my VARIABLES
my $myfile="/tmp/fuss_lsof_conf.txt";
my $mydiff_file="/tmp/diff_fuss_file.txt";
my $myfuss_audit="/tmp/fuss_audit_rpt.txt";

# PERL DBI CONNECT
my $dbh=DBI->connect( "DBI:mysql:database=$DB;host=$HOST;", $user, $pass) or die "$!\n";

# PREPARE\CONSTRUCT THE QUERY
my $sth=$dbh->prepare("SELECT id, t_client, t_filelocation, t_remotescript, t_destination, t_enabled, t_search, t_compression FROM $table") or die "$!\n";

# EXECUTE THE QUERY
$sth->execute();

#OPEN OUTPUT FILE AND WRITE EACH FETCHED ROW FROM THE DATABASE INTO IT WITH A NEW LINE RETURN AFTER EACH RECORD
open(FH, ">$myfile") or die "$!\n";
while (my @row=$sth->fetchrow_array()) {
print FH join(",", @row)."\n";
}

#CLOSE OUTPUT FILE AND TERMINATE DB CONNECTION
close FH;
$sth->finish();
$dbh->disconnect();

#exit 0;
system("diff -y --suppress-common-lines --width=5000 $myfile /tmp/fuss_lsof_conf2.txt > $mydiff_file");

Restrictions:
- I must use Perl as per my manager; of course I can if required use the system call if I need to access UNIX commands; which I do to run "diff"
- We do not have any fancy Perl modules installed so I need to use whatever is native in a Perl install

Requirements:
I need to generate a detail audit report.

Files attached to this thread:
fuss_lsof_conf.txt - the DB in CSV form BEFORE any changes
fuss_lsof_conf2.txt - the DB in CSV form AFTER changes
diff_fuss_file.txt - the output from "diff" command as per my script

Here is an explanation of the changes that were made to fuss_lsof_conf2.txt by me for testing:
id (rule) 19 - t_search was altered the following text was added <where>newgjv<yesterday>
id (rule) 402 - DELETED
id (rule) 465 - t_search was altered the following text was removed <where>fpme<any> <where>fpmh<any>
id (rule) 792 - ADDED new rule (gjv,/ureports/exportwsl/adp,/home/dslmain/wsl/scripts/copylsoftosftpwithmove.sh,dslmain@faunus:export/adp/r2,EXPORT,<where>WSLSP<any>.xml,NO)
id (rule) 2029 - t_remotescript was altered with the following text /home/dslmain/rjlpprod/scripts/copylsoffromsftpwithmove.sh
t_enabled was altered with the following text IMPORTMOVE

So now here's where I am totally lost...I need to produce a detail & summary report (in a file) that looks like this or something similar:
-----------------------------------------------------------------------
FUSS RULE AUDIT FOR {date}{time}
-----------------------------------------------------------------------
DETAIL REPORT
---------------
DELETED RULES:
402,fid,/ureports/exportfid/yrend,/home/dslmain/fid/scripts/copylsoftosftp.sh,fidsftp@sftp1Smilierod/fid/cb/export/yrend,YES,<where><any><yesterday><any>,NO
ADDED RULES:
792,gjv,/ureports/exportwsl/adp,/home/dslmain/wsl/scripts/copylsoftosftpwithmove.sh,dslmain@faunus:export/adp/r2,EXPORT,<where>WSLSP<any>.xml,NO

CHANGED RULES"
BEFORE:
19,gmp,/ureports/exportgmp/extracts,/home/dslmain/gmp/scripts/copylsoftosftp.sh,gmpsftp@sftp1Smilierod/gmp/export/extracts,YES,<where>acaact<yesterday> <where>acafee<yesterday> <wher
e>acagrp<yesterday> <where>acpos<yesterday> <where>actran<yesterday> <where>bil<yesterday><any> <where>bk<yesterday> <where>bk1<yesterday> <where>clcd<yesterday> <where>clientnam
e<yesterday> <where>corpbil<yesterday><any> <where>cqpyee<yesterday> <where>tr<yesterday> <where>tf<yesterday> <where>tt<yesterday> <where>tpcont<yesterday> <where>tpcont1<yester
day> <where>tpcont2<yesterday> <where>oldbil<yesterday><any> <where>ol<yesterday> <where>oipos<yesterday> <where>dc<yesterday><any> <where>dc2<yesterday> <where>di<yesterday> <wh
ere>dierr<yesterday> <where>espos<any><yesterday> <where>fdxref<yesterday> <where>fx<yesterday> <where>gnexch<yesterday> <where>kyfldc<yesterday> <where>kyfldd<yesterday> <where
>mfaccn<yesterday> <where>mfbr<yesterday> <where>mfcl<yesterday> <where>mfclac<yesterday> <where>mfclac1<yesterday> <where>mfclac2<yesterday> <where>mfclad1<yesterday> <where>mfc
lad2<yesterday> <where>mfclis<yesterday> <where>mfcsip<yesterday> <where>mfdoc<yesterday> <where>mfendt<yesterday> <where>mffact<yesterday> <where>mfhs<yesterday> <where>mfhsac<
yesterday> <where>mfhshd<yesterday> <where>mfinrt<yesterday> <where>mfirs<yesterday> <where>mfobj<yesterday> <where>mfrlac<yesterday> <where>mfrr<yesterday> <where>mfsc<yesterday
> <where>mfsc1<yesterday> <where>mfsc2<yesterday> <where>mfsc3<yesterday> <where>mfscdates<yesterday> <where>mfscen<yesterday> <where>mfscmf1<yesterday> <where>mfscmf2<yesterday>
<where>mfscnm<yesterday> <where>mfsymb<yesterday> <where>mm<yesterday> <where>ncadj<yesterday> <where>ncrr<yesterday> <where>ncscl<yesterday> <where>ncsplt<yesterday> <where>omf
i<any><yesterday> <where>omhist1<yesterday> <where>omhist2<yesterday> <where>omordl<yesterday> <where>omordr<yesterday> <where>omordr1<yesterday> <where>omordr2<yesterday> <where
>sm<yesterday> <where>spc<yesterday> <where>sr<yesterday> <where>sr1<yesterday> <where>sr2<yesterday> <where>gnexpd<yesterday> <where>tpcdtl<yesterday> <where>tpkptl<yesterday> <
where>jejldc<yesterday> <where>nalog<yesterday> <where>cqhead<yesterday> <where>cqform<yesterday> <where>mfasst<yesterday> <where>mfrras<yesterday> <where>tpgb<yesterday> <where>
mfcdtl<yesterday> <where>achstm<yesterday> <where>mfacct<yesterday> <where>gncode<yesterday>,YES
AFTER:
19,gmp,/ureports/exportgmp/extracts,/home/dslmain/gmp/scripts/copylsoftosftp.sh,gmpsftp@sftp1Smilierod/gmp/export/extracts,YES,<where>newgjv<yesterday> <where>acaact<yesterday> <wher
e>acafee<yesterday> <where>acagrp<yesterday> <where>acpos<yesterday> <where>actran<yesterday> <where>bil<yesterday><any> <where>bk<yesterday> <where>bk1<yesterday> <where>clcd<ye
sterday> <where>clientname<yesterday> <where>corpbil<yesterday><any> <where>cqpyee<yesterday> <where>tr<yesterday> <where>tf<yesterday> <where>tt<yesterday> <where>tpcont<yesterd
ay> <where>tpcont1<yesterday> <where>tpcont2<yesterday> <where>oldbil<yesterday><any> <where>ol<yesterday> <where>oipos<yesterday> <where>dc<yesterday><any> <where>dc2<yesterday>
<where>di<yesterday> <where>dierr<yesterday> <where>espos<any><yesterday> <where>fdxref<yesterday> <where>fx<yesterday> <where>gnexch<yesterday> <where>kyfldc<yesterday> <where>
kyfldd<yesterday> <where>mfaccn<yesterday> <where>mfbr<yesterday> <where>mfcl<yesterday> <where>mfclac<yesterday> <where>mfclac1<yesterday> <where>mfclac2<yesterday> <where>mfcl
ad1<yesterday> <where>mfclad2<yesterday> <where>mfclis<yesterday> <where>mfcsip<yesterday> <where>mfdoc<yesterday> <where>mfendt<yesterday> <where>mffact<yesterday> <where>mfhs<
yesterday> <where>mfhsac<yesterday> <where>mfhshd<yesterday> <where>mfinrt<yesterday> <where>mfirs<yesterday> <where>mfobj<yesterday> <where>mfrlac<yesterday> <where>mfrr<yesterd
ay> <where>mfsc<yesterday> <where>mfsc1<yesterday> <where>mfsc2<yesterday> <where>mfsc3<yesterday> <where>mfscdates<yesterday> <where>mfscen<yesterday> <where>mfscmf1<yesterday>
<where>mfscmf2<yesterday> <where>mfscnm<yesterday> <where>mfsymb<yesterday> <where>mm<yesterday> <where>ncadj<yesterday> <where>ncrr<yesterday> <where>ncscl<yesterday> <where>ncs
plt<yesterday> <where>omfi<any><yesterday> <where>omhist1<yesterday> <where>omhist2<yesterday> <where>omordl<yesterday> <where>omordr<yesterday> <where>omordr1<yesterday> <where>
omordr2<yesterday> <where>sm<yesterday> <where>spc<yesterday> <where>sr<yesterday> <where>sr1<yesterday> <where>sr2<yesterday> <where>gnexpd<yesterday> <where>tpcdtl<yesterday> <
where>tpkptl<yesterday> <where>jejldc<yesterday> <where>nalog<yesterday> <where>cqhead<yesterday> <where>cqform<yesterday> <where>mfasst<yesterday> <where>mfrras<yesterday> <wher
e>tpgb<yesterday> <where>mfcdtl<yesterday> <where>achstm<yesterday> <where>mfacct<yesterday> <where>gncode<yesterday>,YES
SPECIFICS:
Added to t_search: <where>newgjv<yesterday>

BEFORE:
465,fid,/ureports/fussfid/stmt/sent/sent,/home/dslmain/fid/scripts/copylsoftosftpwithmoveandport.sh,-oport=10022 dataphle@149.83.42.203 .,YESPORT,<where>fide<any> <where>fidh<any
> <where>filelist<any> <where>gmce<any> <where>gmch<any> <where>itje<any> <where>itjh<any> <where>iwse<any> <where>iwsh<any> <where>jite<any> <where>jith<any> <where>otge<any> <w
here>otgh<any> <where>otae<any> <where>otah<any> <where>epce<any> <where>epch<any> <where>itge<any> <where>itgh<any> <where>mgde<any> <where>mgdh<any> <where>qtre<any> <where>qtr
h<any> <where>trae<any> <where>trah<any> <where>gale<any> <where>galh<any> <where>gsle<any> <where>gslh<any> <where>vere<any> <where>verh<any> <where>vese<any> <where>vesh<any> <
where>aese<any> <where>aesh<any> <where>dcme<any> <where>dcmh<any> <where>dsie<any> <where>dsih<any> <where>fpme<any> <where>fpmh<any> <where>ghre<any> <where>ghrh<any> <where>cf
le<any> <where>cflh<any> <where>sese<any> <where>sesh<any> <where>jote<any> <where>joth<any>,NO
AFTER:
465,fid,/ureports/fussfid/stmt/sent/sent,/home/dslmain/fid/scripts/copylsoftosftpwithmoveandport.sh,-oport=10022 dataphle@149.83.42.203 .,YESPORT,<where>fide<any> <where>fidh<any
> <where>filelist<any> <where>gmce<any> <where>gmch<any> <where>itje<any> <where>itjh<any> <where>iwse<any> <where>iwsh<any> <where>jite<any> <where>jith<any> <where>otge<any> <w
here>otgh<any> <where>otae<any> <where>otah<any> <where>epce<any> <where>epch<any> <where>itge<any> <where>itgh<any> <where>mgde<any> <where>mgdh<any> <where>qtre<any> <where>qtr
h<any> <where>trae<any> <where>trah<any> <where>gale<any> <where>galh<any> <where>gsle<any> <where>gslh<any> <where>vere<any> <where>verh<any> <where>vese<any> <where>vesh<any> <
where>aese<any> <where>aesh<any> <where>dcme<any> <where>dcmh<any> <where>dsie<any> <where>dsih<any> <where>ghre<any> <where>ghrh<any> <where>cfle<any> <where>cflh<any> <where>se
se<any> <where>sesh<any> <where>jote<any> <where>joth<any>,NO
SPECIFICS:
Removed from t_search: <where>fpme<any> <where>fpmh<any>

BEFORE:
2029,rjlpprod,dslmain@faunuspprod:import/svc,/home/dslmain/rjlpprod/scripts/copylsoffromsftpnomove.sh,/jreports/exportrjldemo/svc,IMPORTNOMOVE,<where>fndmut<any>.svc,NO
AFTER:
2029,rjlpprod,dslmain@faunuspprod:import/svc,/home/dslmain/rjlpprod/scripts/copylsoffromsftpwithmove.sh,/jreports/exportrjldemo/svc,IMPORTMOVE,<where>fndmut<any>.svc,NO
SPECIFICS:
Modified t_remotescript: /home/dslmain/rjlpprod/scripts/copylsoffromsftpwithmove.sh
Modified t_enabled: IMPORTMOVE
-----------------------------------------------------------------------
SUMMARY REPORT
-----------------
DELETED=1
RULE: 402(fid), xxx(client), xxx(client), xxx(client)

ADDED=1
RULE: 792(gjv), xxx(client), xxx(client)

CHANGED=3
RULE: 19(gmp), 465(fid), 2029(rjlpprod), xxx(client), xxx(client)
-----------------------------------------------------------------------

Report specifics:
1. You can see that for "CHANGED RULES" I need to show the entire old and new RULE and then in the SPECIFICS show exactly where the change took place (i.e. field).
2. In the Summary Report I need to show the "t_client" field after each "rule"

Hope this is all clear; if not please do ask questions.

Thanks in advance of any\all help.

Regards
Giuliano
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Generate a Summary report

Hi All, Script to meet my requirement might be simpler for UINIX experts.. :) I need to generate an summary report in .txt file using shell script I have Reject directory in Unix server which contains all reject files for three diff categories- Presentation, Chapter and Scene Following... (3 Replies)
Discussion started by: Sakthikalluri
3 Replies

2. Shell Programming and Scripting

Summary report csv file

Hello, I have 2 csv files with 4 columns each. file1.csv A, AA, AAA, AAAA B, BB, BBB, BBBB file2.csv C, CC, CCC, CCCC D, DD, DDD, DDDD I would like to use shell commands (sed, awk...) to copy the content of the 2 files (2x4 columns) into a final csv template file. Expected... (2 Replies)
Discussion started by: inMyZone35
2 Replies

3. Shell Programming and Scripting

Parsing diff output into report

Hello all; lat week I was able to get some assistance on creating a summary report from a file generated by a "comm" comparison of twp CSV files...turn out now that I am being asked for a detail report as well...this is beyond my knowledge of perl (and yes I have to use perl)..also please keep... (5 Replies)
Discussion started by: gvolpini
5 Replies

4. Shell Programming and Scripting

compare columns for equal values and output a summary

Hi all I am trying to scan a file that has 3 columns: red blue 123351 red blue 848655 red blue 126354 red blue 023158 black white 654896 red blue 650884 I want an output that sums the rows that have matching columns 1 and 2 :wall: red blue has 5 entries black white has 1 entry ... (4 Replies)
Discussion started by: reno
4 Replies

5. Shell Programming and Scripting

how to add up a total in a summary report?

Hi all i got a script up but i cant add up the summary report.. keep having synax error . mind helping me to take a look and tell me what went wrong.. i know is a bit long but i hope someone can hep me with it. thanks the error message come up when i try to run the sumary report.. i guess... (16 Replies)
Discussion started by: xiaojesus
16 Replies

6. Shell Programming and Scripting

Detailed disk usage versus age summary

Hi, I'm posting my question here as I fele that what I am about to try to do must have been done already, and I don't want to re-invent the wheel. I have recently become responsible for monitoring disk space usage for a large file system. I would like to geenrate reports that will summise... (8 Replies)
Discussion started by: littleIdiot
8 Replies

7. Solaris

Summary Explorer Output

Hi Guys... I wonder about explorer Output on Solaris, can we get all system information based on explorer to a single summary report. Is there any tools can generate explorer output to some summary report information ? thanks Renggta H Patria (3 Replies)
Discussion started by: gryffindor
3 Replies

8. Shell Programming and Scripting

diff 2 files; output diff's to 3rd file

Hello, I want to compare two files. All records in file 2 that are not in file 1 should be output to file 3. For example: file 1 123 1234 123456 file 2 123 2345 23456 file 3 should have 2345 23456 I have looked at diff, bdiff, cmp, comm, diff3 without any luck! (2 Replies)
Discussion started by: blt123
2 Replies
Login or Register to Ask a Question