Displaying only specific parts from a field

[NateBOIT]

I was in the MySQL forum asking about my problem and feherke told me to try and use AWK to do it. So here i am. And i have never used AWK before.

I have a program that logs various event logs into a database. I am using Navicat to generate reports from MySQL. Problem is, the entire event log is stored in one field in MySQL. I would like to extract a few non-consecutive lines and only have the query display those back. For example:

Logon Failure:
Reason:    Account logon time restriction violation
    User Name:    joebob
    Domain:        DOMAIN
    Logon Type:    10
    Logon Process:    User32  
    Authentication Package:    Negotiate
    Workstation Name:    JOEBOB_COMP
    Caller User Name:    JOEBOB_COMP
    Caller Domain:    DOMAIN
    Caller Logon ID:    (0x0,0x3E7)
    Caller Process ID:    5324
    Transited Services:    -
    Source Network Address:    192.168.1.14
    Source Port:    4639

For this one i would want only the Logon Failure:, Reason, User Name, and Workstation name. However, all events do not have the same "fields." The event is formatted with carriage return line feeds after each section, which could possibly be used to help divide up what i want.

Can i use awk just within a query to return the results of the script?

I would probably eventually set up if statements to determine what type of event each one was and then mine out whatever data i needed depending on the type of event.

If anyone could please help me set up a script or point me in the right direction for what I need, if it is even possible, then that would be great.

Thanks

 

[feherke]

Hi

Supposing that there is a splitlog table with the necessary three fields.

mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green] -s -e 'select [green][i]field[/i][/green] from [green][i]table[/i][/green]' | \
awk -vq="'" -F' *\\\\n *' '$1~/Logon Failure/{for(i in f)delete f[i];for(i=1;i<=NF;i++){split($i,a,/ *: */);f[a[1]]=a[2]};print "insert into splitlog values ("q f["Reason"] q","q f["User Name"] q","q f["Workstation Name"] q");"}' | \
mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green]

On not Unix/Unix-like systems you will have to put the [tt]awk[/tt] commands into a file :

BEGIN {
  FS=" *\\\\n *"
}
$1~/Logon Failure/ {
  for (i in f) delete f[i]
  for (i=1;i<=NF;i++) {
    split($i,a,/ *: */)
    f[a[1]]=a[2]
  }
  print "insert into splitlog values ('"f["Reason"]"','"f["User Name"]"','"f["Workstation Name"]"');"
}

Then execute the commands like this :

mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green] -s -e "select [green][i]field[/i][/green] from [green][i]table[/i][/green]" | \
awk -f [green][i]scriptfile[/i][/green] | \
mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green]

Tested with [tt]gawk[/tt], works with --traditional switch too, so should work with other [tt]awk[/tt] versions.

Feherke.

http://rootshell.be/~feherke/

 

[NateBOIT]

What should i use to execute awk scripts? Like i said, i am very new to awk. I am running Windows XP.

 

[feherke]

Hi

For Windows you can use [tt]awk95.exe[/tt] :

http://cm.bell-labs.com/cm/cs/who/bwk/awk95.exe

Note, that my code hardly depends on the [tt]mysql[/tt] command's output with the -s switch :

-s, --silent Be more silent. Print results with a tab as separator, each row on new line.

Also note that I forgot that Windows is unable to execute commands split on multiple lines. So you have to remove the backslashes ( \ ) and write all in one line :

mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green] -s -e "select [green][i]field[/i][/green] from [green][i]table[/i][/green]" | awk -f [green][i]scriptfile[/i][/green] | mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green]

Feherke.

http://rootshell.be/~feherke/

 

[NateBOIT]

Ok. I tried all that and it partly worked. I created a table called splitlog with the three fields. I then ran the script and it produced 13 empty rows. Weird. I looked and there are 13 fields that start out with "Logon Failure." So it seems like it is finding them but not splitting them properly or just not printing results in the fields.

 

[Annihilannic]

Try omitting the | mysql -D database -u user so you can check and debug the output of the awk script.

By the way, feherke, I think you can just delete f rather than iterating through the array.

Annihilannic.

 

[Annihilannic]

Actually, I see why you had to do it now, delete f deletes the array contents but not the indices, weird! I'd better watch out for that in future...

Annihilannic.

 

[vgersh99]

not all awk-s implement deleting the ENTIRE array with 'delete' call, e.g. with Solaris' "nawk' you can only iterate through an array one idex at the time.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[NateBOIT]

Removing the last mysql credentials like you said returns back 13 null queries, like so:

insert into splitlog values ('','','');

Maybe its not actually finding the individual strings that i want it to look for?

 

[Annihilannic]

Is there a blank line separating the records Nate?

If so, try this for the awk script:

[tt]BEGIN { FS=":" }
function readfields() {
for (i in f) delete f
while (getline && $0 != "") {
sub("^ *","",$1); # strip leading spaces
sub("^ *","",$2); # strip leading spaces
f[$1]=$2 ;
}
}
$1 ~ /Logon Failure/ {
readfields()
print "insert into splitlog values ('" f["Reason"] "','" f["User Name"] "','" f["Workstation Name"] "');"
}
$1 ~ /Some Other Error For Example/ {
readfields()
print "insert into splitlog values ('" f["Reason"] "','" f["Source Network Address"] "','" f["Source Port"] "');"
}[/tt]

Annihilannic.

 

[feherke]

Hi

Note, that my code hardly depends on the mysql command's output with the -s switch

This means, if you execute only the first command from the pipeline, I mean this :

mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green] -s -e "select [green][i]field[/i][/green] from [green][i]table[/i][/green]"

Your output will be one record per line, the newline characters displayed as [tt]\n[/tt] :

Logon Failure:\nReason:    Account logon time restriction violation\n    User Name:    joebob\n    Domain:        DOMAIN\n    Logon Type:    10\n    Logon Process:    User32  \n    Authentication Package:    Negotiate\n    Workstation Name:    JOEBOB_COMP\n    Caller User Name:    JOEBOB_COMP\n    Caller Domain:    DOMAIN\n    Caller Logon ID:    (0x0,0x3E7)\n    Caller Process ID:    5324\n    Transited Services:    -\n    Source Network Address:    192.168.1.14\n    Source Port:    4639

Please check/post you similar output.

Feherke.

http://rootshell.be/~feherke/

 

[NateBOIT]

C:\awk>mysql -D database -u user -s -e "select reply from hmlog"
reply
15 ms
Win32 Error.  Code: 87.\nThe parameter is incorrect
Win32 Error.  Code: 87.\nThe parameter is incorrect
\n\tSource Port:\t1319ress:\t192.168.1.14sword

The last line is what was reported back for the event field of a different "Logon Failed" event. The event reason this time was "Unkown user name or bad password." It seems like it is giving back the first line, then putting the 2nd to last line on top of it, then the last line on top of both of them. A three layered statement with the end of the longest line sticking out at the end. I have no idea what would make it do that. The event appears in MySQL in the same format as the one i put in my first post.

 

[feherke]

Hi

Did you run the [tt]mysql[/tt] command exactly as you quoted above ?

                    you changed the [highlight #eef]field[/highlight] and [highlight #eef]table[/highlight] name
                                         [blue]___        ___[/blue]
                                        [blue]/   \      /   \[/blue]
mysql -D [highlight #fee]database[/highlight] -u [highlight #fee]user[/highlight] -s -e "select [highlight #eef]reply[/highlight] from [highlight #eef]hmlog[/highlight]"
         [red]\______/    \__/[/red]
         
but not the [highlight #fee]database[/highlight] and [highlight #fee]user[/highlight] name

I suppose was just caution on posting to protect your system.

Sorry, I have no idea about what could be the problem there. I never saw such error message. And while the error is before running [tt]awk[/tt], we ( at least I ) can not help too much. :-(

Feherke.

http://rootshell.be/~feherke/