Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

parsing log files in to a spread sheet

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting parsing log files in to a spread sheet
# 1  
Old 10-15-2012
parsing log files in to a spread sheet
Hi Guyz,
I need to extract only the data/time, uid , and the message at the last " invalid password format", to a spread sheet.
this is one of the lines from a log file from which i need to extract them.
i need this message to be clearly formatted in the spread sheet.
can you please help me with this....ty

Code:
AuthReject agsosg98 [15/Oct/2012:10:45:54 -0500] "10.6.40.11 uid=e420839,ou=people,ou=some name,ou=organizations,dc=something,dc=com" "ag-agsosg84-wid2-dev1 GET /home" [] [7] {"code":706051103,"msg":"Invalid password format"} [] []

Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 10-15-2012 at 05:34 PM.. Reason: code tags, please!
# 2  
Old 10-15-2012
for starters:
Code:
awk -F'[][,=]' '{print $2,$4}' OFS=, myFile

What's the last part that you need? Could snap the exact thing you need from your sample input?
This User Gave Thanks to vgersh99 For This Post:
remo850
# 3  
Old 10-15-2012
15/Oct/2012:10:45:54 - date/time
uid=e420839 - users id
Invalid password format - reason
ag-agsosg84-wid2-dev1 - agent name

these are the four fields i need in the excel sheet.
# 4  
Old 10-18-2012
any more replies plsz....
need the exact form
only the fields mentioned.
with the command u gave me ..ui get some extra fields also
# 5  
Old 10-18-2012
if you are having the same pattern of error message, then you can use it like this..

Code:
$ awk -F"[][ ,\"]" '{print $4","$9","$(NF-9)" "$(NF-8)" "$(NF-7)","$18}' input.txt
15/Oct/2012:10:45:54,uid=e420839,Invalid password format,ag-agsosg84-wid2-dev1

# 6  
Old 10-18-2012
Code:
awk: trying to access field -3
 record number 1


am getting this message

---------- Post updated at 12:54 PM ---------- Previous update was at 12:50 PM ----------

the pattern of the message changes if the user DN is different and also the message at the last changes( invalid credentials, invalid password format, invalid password - null password etc)
in both the cases we will have differnet number of coulums(or fields)
but the format as a whole remains same

---------- Post updated at 01:27 PM ---------- Previous update was at 12:54 PM ----------

Code:
AuthReject agsosg96 [16/Oct/2012:13:12:55 -0500] "161.194.32.196 uid=E432834,ou=internal,ou=users,dc=mycompany,dc=com" "ag-a23423-myss-dev2 GET /login/start" [] [0] Invalid credentials [] []
AuthReject agsosg98 [15/Oct/2012:10:56:48 -0500] "10.6.40.11 uid=e420839,ou=people,ou=my company corporation,ou=organizations,dc=mycompany,dc=com" "ag-dfghj84-wid2-dev1 GET /home" [] [27] {"code":706051103,"msg":"Invalid password format"} [] []
AuthReject agsosg97 [18/Oct/2012:04:49:25 -0500] "10.248.68.219 uid=shayne,ou=Application Accounts,ou=my company Corporation,ou=Organizations,dc=mycompany,dc=com" "ag-hgujt34-csj-dev1 GET /TMM_Web/" [] [0] Invalid credentials [] []
AuthReject agsos136 [11/Feb/2011:03:42:21 -0500] "10.219.234.57 uid=p468126,ou=People,ou=my company Corporation,ou=Organizations,dc=mycompany,dc=com" "ag-poloi02-rds-dev1 GET /" [] [0] Invalid credentials - null password [] []
AuthReject agsos136 [11/Feb/2011:10:20:18 -0500] "10.136.162.17 uid=p457653,ou=People,ou=my company Corporation,ou=Organizations,dc=mycompany,dc=com" "ag-lkjhg02-rds-dev1 GET /" [] [0] Invalid credentials [] []
AuthReject agsos136 [11/Feb/2011:10:09:09 -0500] "158.134.21.197 uid=p105666,ou=People,ou=my company Corporation,ou=Organizations,dc=mycompany,dc=com" "ag-qwert12-myss-dev2 GET /SecureID/index.html" [] [0] Invalid credentials [] []
AuthReject agsos136 [07/Feb/2011:17:47:26 -0500] "10.136.162.82 uid=e420858,ou=People,ou=my company Corporation,ou=Organizations,dc=mycompany,dc=com" "ag-asmdr02-rds-dev1 GET /" [] [0] Invalid credentials [] []

these are five messages from which i want the following type of output
Code:
15/Oct/2012:10:45:54,uid=e420839,Invalid password format,ag-agsosg84-wid2-dev1

---------- Post updated at 02:10 PM ---------- Previous update was at 01:27 PM ----------

any more replies?
# 7  
Old 10-18-2012
something along these lines...

awk -F'[][,="]' -f remo.awk myLogFile
remo.awk:
Code:
BEGIN {
  OFS=","
}
{
  msg=$(NF-5)
  if (msg ~ /^[0-9][0-9]*/)
     msg=$(NF-4)

  print substr($2,1,index($2," ")-1),$5, msg,substr($17,1,index($17," ")-1)
}
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save log data in excel sheet

Hello, I have the following data format in a log file : a : x1 b : x2 c : x3 d : x4 -------- a : x5 b : x6 c : x7 d : x8 so the same fields ( a ,b ,c,d) repeated many times in the same log file but with different "x" values (x5,x6,x7,x8). I need a script to save this data in an... (6 Replies)
Discussion started by: mm00123
6 Replies

2. Shell Programming and Scripting

Attaching two text files in two different sheet in same excel

Hi, My requirement is to get attach two different text file contents to two different sheets in same excelsheet. Also, is there any way we can name the tabs as desired ? Kindly assist. (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

3. Programming

Need an c,c++,or java code for parsing the log files

need the code for c,c++,java for parsing the log file (5 Replies)
Discussion started by: raghuraipur
5 Replies

4. UNIX for Dummies Questions & Answers

how to fill the name of the spread sheet as a cell's data

In open office spreadsheet, i would like to fill a cell with the name of the spread sheet. That is if the file name of spread sheet is, team.ods, then i have to fill a specific cell with that name 'team'. How to do that ? I have more spread sheets to be created, so i want it to be done... (0 Replies)
Discussion started by: thegeek
0 Replies

5. Shell Programming and Scripting

parsing log files, removing spaces and replace with commas

Hello all i am working on a database to import log files from my systems, but i cannot seem to find the answer. I searched here for a good bit and couldnt peice together what i was looking for. I know you could do this with awk, i just dont know how. Any help would be greatly appreciated.... (6 Replies)
Discussion started by: caddyjoe77
6 Replies

6. Shell Programming and Scripting

Parsing log files, displaying logs between specific dates

Sorry, couldn't really think of a simple subject/title. So, I have a log file, and the dates are displayed like so: 2009-03-05 02:49:44 So the first and second field are the date/time. I can change them into a unix timestamp easily with: date -d "2009-03-05 02:49:44" +%s However,... (17 Replies)
Discussion started by: Rhije
17 Replies

7. Shell Programming and Scripting

Help needed to combine files to excel sheet

Hi, I have several hundreds of text files which I would like to combine them onto excel sheet. Does anyone know how to combine them into different excel sheet in one workbook using Bash script? Please advise. THanks. I forgot to mention my file is something like below:- A B ... (5 Replies)
Discussion started by: ahjiefreak
5 Replies

8. UNIX for Dummies Questions & Answers

How can import data files to XL sheet.

Hi, I have the file(F1.XL) in Unix Box. it's updating every 1hr. I would like to import f1.xl to Windows excel sheet, when i need see the reports. can any one clarify, is there any VB script for importing data from UNIX, like sql connection.... thanks (1 Reply)
Discussion started by: koti_rama
1 Replies

9. Programming

spawning multiple processes spread across two files

I want to spawn n child processes but have two different classes..with the foremost one forking/spawning the child process and the latter performing a function w/ the spawned processes. I can do this in one class with an if statement and the simple if((pid=fork())==0) //child process { ... (1 Reply)
Discussion started by: StrengthThaDon
1 Replies

10. UNIX for Advanced & Expert Users

Exporting files from unix to Excel sheet

How can we export a file in unix with certain number of columns to an excel sheet. It's very useful in time saving...... (3 Replies)
Discussion started by: bishweshwar
3 Replies
Login or Register to Ask a Question