|
|
Sponsored Content
Top Forums
Shell Programming and Scripting
Script to Gather data from logs and export to a CSV file
Post 302986195 by Yagami_Sama on Monday 21st of November 2016 05:42:41 PM
11-21-2016
Script to Gather data from logs and export to a CSV file
Greetings,
After a few hours of trial and error, I decide to ask for some help.
I am new to AWK and shell script, so please don't laugh
I made the below script, to gather data from some logs and have the output into a CSV file :
The output is like below :
The plan was to gather all data and have the amount of errors found from a given time in just one line like below, grouping by hours all errors in just one line :
And that is what I am not able to achieve with my knowledge, which is very limited by the way.
How could I make the script output the information in the desired format ? All errors from a time set, just in one line, instead of one line from each errors counted ?
Any sugestion to fix the script I did are welcome and any other script which accomplish that is more than welcome.
Thanks for you time in reading my doubt / request and thanks in advanced for all sugestions.
After a few hours of trial and error, I decide to ask for some help.
I am new to AWK and shell script, so please don't laugh
I made the below script, to gather data from some logs and have the output into a CSV file :
Code:
#!/bin/sh
#Script to collect Errors
file="/Path/Errors_Report_$(date +%Y%m%d).csv"
DAY=`date +%d -d "Yesterday"`
#Header of the CSV file
echo -e "User_ID,Host_name,Date,Time,Code_302,Code_304,Code_305,Code_1,Code_2,Code_3,Code_7,Code_10,Code_18,Code_101,Code_201,Code_300,Code_301,Code_303,Code_306,Code_14,Code_9999,Code_19,Code_9,Code_11" > /path/Error_Report_$(date +%Y%m%d).csv
grep 'Pattern' Path/File_$(date +%Y%m%d)* |awk -F "|" '{if ($28~/ Pattern /) print substr($1,74,10)","substr($1,85,2)","$28; else if($29~/ Pattern /) print substr($1,74,10)","substr($1,85,2)","$29; else if($30~/ Pattern /) print substr($1,74,10)","substr($1,85,2)","$30}' | cut -d, -f1,2,10 | awk -F "," '{if ($3~/ Error1/) print $1","$2","302; else if ($3~/ Error2/) print $1","$2","304; else if ($3~/ Error3/) print $1","$2","305; else if ($3~/ Error4/) print $1","$2","1; else if ($3~/ Error5/) print $1","$2","2; else if ($3~/ Error6./) print $1","$2","3; else if ($3~/ Error7./) print $1","$2","7; else if ($3~/ Error 8/) print $1","$2","10; else if ($3~/ Error./) print $1","$2","18; else if ($3~/ Error 9/) print $1","$2","101; else if ($3~/ Error 10/) print $1","$2","201; else if ($3~/ Error11/) print $1","$2","300; else if ($3~/ Error12/) print $1","$2","301; else if ($3~/ Error13/) print $1","$2","303; else if ($3~/ Error14/) print $1","$2","306; else if ($3~/ Error15/) print $1","$2","14; else if ($3~/ Error16/) print $1","$2","9999; else if ($3~/ Error17./) print $1","$2","19; else if ($3~/ Error18/) print $1","$2","9; else if ($3~/ Error19/) print $1","$2","11}' | sort | uniq -c | awk -F" " '{print $1","$2}' | awk -F "," '{if($4==302) print $2","$3","$1",,,,,,,,,,,,,,,,,,,"; else if($4==304) print $2","$3",,"$1",,,,,,,,,,,,,,,,,,"; else if($4==305) print $2","$3",,,"$1",,,,,,,,,,,,,,,,,"; else if($4==1) print $2","$3",,,,"$1",,,,,,,,,,,,,,,,"; else if($4==2) print $2","$3",,,,,"$1",,,,,,,,,,,,,,,"; else if($4==3) print $2","$3",,,,,,"$1",,,,,,,,,,,,,,"; else if($4==7) print $2","$3",,,,,,,"$1",,,,,,,,,,,,,"; else if($4==10) print $2","$3",,,,,,,,"$1",,,,,,,,,,,,"; else if($4==18) print $2","$3",,,,,,,,,"$1",,,,,,,,,,,"; else if($4==101) print $2","$3",,,,,,,,,,"$1",,,,,,,,,,"; else if($4==201) print $2","$3",,,,,,,,,,,"$1",,,,,,,,,"; else if($4==300) print $2","$3",,,,,,,,,,,,"$1",,,,,,,,"; else if($4==301) print $2","$3",,,,,,,,,,,,,"$1",,,,,,,"; else if($4==303) print $2","$3",,,,,,,,,,,,,,"$1",,,,,,"; else if($4==306) print $2","$3",,,,,,,,,,,,,,,"$1",,,,,"; else if($4==14) print $2","$3",,,,,,,,,,,,,,,,"$1",,,,"; else if($4==9999) print $2","$3",,,,,,,,,,,,,,,,,"$1",,,"; else if($4==19) print $2","$3",,,,,,,,,,,,,,,,,,"$1","; else if($4==9) print $2","$3",,,,,,,,,,,,,,,,,,,"$1","; else if($4==11) print $2","$3",,,,,,,,,,,,,,,,,,,,"$1}' | awk -F"," '{print "2001,Server1,"$1","$2":00:00,"$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14","$15","$16","$17","$18","$19","$20","$21","$22}' >> /Path/Error_Report_$(date +%Y%m%d).csvCode:
User_ID,Host_name,Date,Time,Code_302,Code_304,Code_305,Code_1,Code_2,Code_3,Code_7,Code_10,Code_18,Code_101,Code_201,Code_300,Code_301,Code_303,Code_306,Code_14,Code_9999,Code_19,Code_9,Code_11 2001,Server1,2016-11-18,00:00:00,,,,,,,,,,,,,,,,,,,,5 2001,Server1,2016-11-18,00:00:00,1,,,,,,,,,,,,,,,,,,, 2001,Server1,2016-11-18,00:00:00,,,,,,,,,,,,,,1,,,,,, 2001,Server1,2016-11-18,00:00:00,,,,,,,,,,,,,,,,,,,42, 2001,Server1,2016-11-18,01:00:00,,,,,,,,,,,,,,,,,,,,3 2001,Server1,2016-11-18,01:00:00,2,,,,,,,,,,,,,,,,,,, 2001,Server1,2016-11-18,01:00:00,,,,,,,,,,,,,,3,,,,,, 2001,Server1,2016-11-18,01:00:00,,,,,,,,,,,,,,,,,,,21,
Code:
User_ID,Host_name,Date,Time,Code_302,Code_304,Code_305,Code_1,Code_2,Code_3,Code_7,Code_10,Code_18,Code_101,Code_201,Code_300,Code_301,Code_303,Code_306,Code_14,Code_9999,Code_19,Code_9,Code_11 2001,smom01brb.rio.oi.ombr,2016-11-18,00:00:00,1,,,,,,,,,,,,1,,,,,,42,5
How could I make the script output the information in the desired format ? All errors from a time set, just in one line, instead of one line from each errors counted ?
Any sugestion to fix the script I did are welcome and any other script which accomplish that is more than welcome.
Thanks for you time in reading my doubt / request and thanks in advanced for all sugestions.
|
|
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hello people,
Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these... (3 Replies)
Discussion started by: vinayagan
3 Replies
2. Shell Programming and Scripting
Since i'm new to scripting i'm findind it difficult to code a script. The script has to be an executable with 2 paramters passed to it.The Parameters are
1. The Control file name(.ctl file)
2. The Data file name(.csv file)
Does anybody have an idea about it? :confused: (3 Replies)
Discussion started by: Csmani
3 Replies
3. Shell Programming and Scripting
Hi,
I am currently working on a FTP script which would ftp to extranet servers from intranet server and gather logs based on timestamps in archive logs and by using a wildcard character in the present logs. I have the following in place
AAA="Extranet Server 1:Instance Extranet Server... (2 Replies)
Discussion started by: openspark
2 Replies
4. Web Development
hello
i want a script to load the data line by line from a csv file into a mysql
table (3 Replies)
Discussion started by: srpa01red
3 Replies
5. Shell Programming and Scripting
Friends...This is the first time i am trying the report generation using shell script... any suggestions are welcome.
Is there a way to set the font size & color when i am exporting the data from unix shell script as a CSV file ?
The following sample data is saved as a .csv file in the... (2 Replies)
Discussion started by: appu2176
2 Replies
6. Shell Programming and Scripting
Hi,
I am trying to export the data to an .ixf file.
I have read the table names from a .dat file and those table name should be passed to the select * from schema.TABLENAME query .
I am trying the below loop
while read TABLE; do
db2 EXPORT TO ~/data_export/$TABLE.ixf OF IXF MESSAGES... (5 Replies)
Discussion started by: vikyalex4
5 Replies
7. UNIX for Dummies Questions & Answers
Hi Guys,
I am new to shell script.I need your help to write a shell script.
I need to write a shell script to extract data from a .csv file where columns are ',' separated.
The file has 7 columns having values say column 1,column 2.....column 7 as below along with their values.
Name, Address,... (7 Replies)
Discussion started by: Vivekit82
7 Replies
8. Shell Programming and Scripting
Hi everyone,
I have a csv file which has data with different heading and column names as below.
Static Data Ingested
,,,,,,,,,,,,Known Explained Rejections
Column_1,column_2,Column_3,Column_4,,Column_6,Column_7,,% Column_8,,Column_9 ,Column_10 ,... (14 Replies)
Discussion started by: Vivekit82
14 Replies
9. Shell Programming and Scripting
Hi, I am newbie in shell script.
I need your help to solve my problem.
Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file.
File1:
SourceFile,DateTimeOriginal
/home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies
10. Linux
Hi all,
Is there any out there have a brilliant idea on how to export html table data as .csv or write to txt file with separated comma and also get the filename of link from every table and put one line per rows each table.
Please see the attached html and PNG of what it looks like.
... (7 Replies)
Discussion started by: lxdorney
7 Replies