Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

AWK with regular Expressions Maybe 1

Status
Not open for further replies.

SweetyXOXO

Programmer
Dec 22, 2017
10
DE
Hi!

I'm an AWK Beginner and I have no idea how to deal with my problem.

when i do a Ldap request for user "ur002" i get an output like this (example):

username='ur002'
TAG='0',
VENDOR='2352',
ATTRIBUTE='4',
VALUE_STR='local'},

The numbers behind the Parameters Vendor an Attribute are the values, which you can find in 2 tables AAADICTIONARYVENDOR and AAADICTIONARYATTR

The programm i need to build with awk should first search "2352" in AAADICTIONARYVENDOR. The Table looks like this:

0|MARKONE|6728
1|IKINCI|5
2|UEC|9
3|FEREI|61
4|OCOCU|166
5|ALPER|2352

it has to copy the Value "ALPER" and paste it into the first output next to VENDOR

in the second step we have to deal with the table AAADICTIONARYATTR. This one is a little bit more difficult. it looks like this (also an excerpt)

845|MARKONE-ALLOW-ALL-VR-ACCESS|19|1|6728
846|MARKONE-ALT-CLI-ACCESS-LEVEL|20|2|6728
847|MARKONE-ALT-CLI-VROUTER-NAME|21|2|6728
848|MARKONE-SA-VALIDATE|22|1|6728
849|MARKONE-IGMP-ENABLE|23|1|6728
850|UEC-USER-PRIV|1|1|9
851|UEC-SERVICE-PROFILE|2|2|9
852|UEC-VPN-NAME|3|2|9
853|UEC-ISOS-VERSION|4|1|9
854|UEC-L2TP-TUNSET|5|1|9
855|ALPER-CLIENT-DNS-PRI|1|2|2352
856|ALPER-CLIENT-DNS-SEC|2|2|2352
857|ALPER-DHCP-MAX-LEASES|3|1|2352
858|ALPER-CONTEXT-NAME|4|2|2352
859|ALPER-BRIDGE-GROUP|5|2|2352
860|ALPER-BG-AGING-TIME|6|2|2352

In the first Output we had the attribute value 4, and the vendor 2352. So we have to find in this table the value 4|X|2352 and copy the full name (858|ALPER-CONTEXT-NAME|4|2|2352) also in first output.


So if i do a request for user 'ur002' i need to get this output:


username='ur002'

TAG='0',
VENDOR='2352', ALPER
ATTRIBUTE='4', 858|ALPER-CONTEXT-NAME|4|2|2352
VALUE_STR='local'},

I will be grateful for any help!

Blessed Christmas Time!
 
Hello SweetyXOXO,

From data you posted I created these 3 files:
sx_user.txt
Code:
username='ur002'
TAG='0',
VENDOR='2352',
ATTRIBUTE='4',
VALUE_STR='local'},

sx_vendor.txt
Code:
0|MARKONE|6728
1|IKINCI|5
2|UEC|9
3|FEREI|61
4|OCOCU|166
5|ALPER|2352

sx_attrib.txt
Code:
845|MARKONE-ALLOW-ALL-VR-ACCESS|19|1|6728
846|MARKONE-ALT-CLI-ACCESS-LEVEL|20|2|6728
847|MARKONE-ALT-CLI-VROUTER-NAME|21|2|6728
848|MARKONE-SA-VALIDATE|22|1|6728
849|MARKONE-IGMP-ENABLE|23|1|6728
850|UEC-USER-PRIV|1|1|9
851|UEC-SERVICE-PROFILE|2|2|9
852|UEC-VPN-NAME|3|2|9
853|UEC-ISOS-VERSION|4|1|9
854|UEC-L2TP-TUNSET|5|1|9
855|ALPER-CLIENT-DNS-PRI|1|2|2352
856|ALPER-CLIENT-DNS-SEC|2|2|2352
857|ALPER-DHCP-MAX-LEASES|3|1|2352
858|ALPER-CONTEXT-NAME|4|2|2352
859|ALPER-BRIDGE-GROUP|5|2|2352
860|ALPER-BG-AGING-TIME|6|2|2352

then I wrote following awk script, which processes all 3 files:
sx_ldap_user.awk
Code:
[COLOR=#0000ff]# Run:[/color]
[COLOR=#0000ff]#   awk -f sx_ldap_user.awk sx_user.txt sx_vendor.txt sx_attrib.txt[/color]
[COLOR=#6a5acd]BEGIN[/color] {
  [COLOR=#6a5acd]FS[/color] = [COLOR=#ff00ff]"|"[/color]
  idx=[COLOR=#ff00ff]0[/color]
}

[COLOR=#0000ff]# Line rules[/color]
{
  [COLOR=#0000ff]# chomp current line[/color]
  chomp_line()
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]1[/color]] {
    [COLOR=#0000ff]# replace "=" with separator "|"[/color]
    [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/=/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]"|"[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
    key = trim([COLOR=#6a5acd]$1[/color])
    val = trim([COLOR=#6a5acd]$2[/color])
    keys[[COLOR=#6a5acd]idx++[/color]]=key
    user[[COLOR=#6a5acd]key[/color]]=val
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]2[/color]] {
  [COLOR=#a52a2a][b]if[/b][/color] (user[[COLOR=#6a5acd]"VENDOR"[/color]] ~ [COLOR=#6a5acd]$3[/color])  {
    vendor_val = [COLOR=#6a5acd]$2[/color]
  }
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]3[/color]] {
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]$2[/color] ~ vendor_val && user[[COLOR=#6a5acd]"ATTRIBUTE"[/color]] ~ [COLOR=#6a5acd]$3[/color])  {
    attrib_val = [COLOR=#6a5acd]$0[/color]
  }
}

[COLOR=#6a5acd]END[/color] {
  [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"username"[/color] [COLOR=#ff00ff]"="[/color] user[[COLOR=#6a5acd]"username"[/color]]
  [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"TAG"[/color] [COLOR=#ff00ff]"="[/color] user[[COLOR=#6a5acd]"TAG"[/color]]
  [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"VENDOR"[/color] [COLOR=#ff00ff]"="[/color] user[[COLOR=#6a5acd]"VENDOR"[/color]] vendor_val
  [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"ATTRIBUTE"[/color] [COLOR=#ff00ff]"="[/color] user[[COLOR=#6a5acd]"ATTRIBUTE"[/color]] attrib_val
  [COLOR=#a52a2a][b]print[/b][/color] [COLOR=#ff00ff]"VALUE_STR"[/color] [COLOR=#ff00ff]"="[/color] user[[COLOR=#6a5acd]"VALUE_STR"[/color]]
}

[COLOR=#0000ff]# -- fuctions --[/color]
[COLOR=#a52a2a][b]function[/b][/color] chomp_line() {
  [COLOR=#0000ff]# strip out the carriage return or line feed at the end of current line[/color]
  [COLOR=#0000ff]# the function modifies global variable $0 (current line)[/color]
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\r[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\n[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
}

[COLOR=#a52a2a][b]function[/b][/color] strip() {
  [COLOR=#0000ff]# remove blanks from begin and end of line[/color]
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd] [/color][COLOR=#a52a2a][b]\t[/b][/color][COLOR=#ff00ff]]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[[/color][COLOR=#6a5acd] [/color][COLOR=#a52a2a][b]\t[/b][/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
}

[COLOR=#a52a2a][b]function[/b][/color] trim(fld) {
  [COLOR=#0000ff]# remove leading and trailing spaces from field[/color]
  [COLOR=#0000ff]#printf "*   '%s'\n", fld[/color]
  [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd] [/color][COLOR=#a52a2a][b]\t[/b][/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color][COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color]fld)
  [COLOR=#0000ff]#printf "**   '%s'\n", fld[/color]
  [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[[/color][COLOR=#6a5acd] [/color][COLOR=#a52a2a][b]\t[/b][/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color][COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color]fld)
  [COLOR=#0000ff]#printf "**** '%s'\n", fld[/color]
  [COLOR=#a52a2a][b]return[/b][/color] fld
}

Running the script delivers this result:
Code:
$ awk -f sx_ldap_user.awk sx_user.txt sx_vendor.txt sx_attrib.txt 
username='ur002'
TAG='0',
VENDOR='2352',ALPER
ATTRIBUTE='4',858|ALPER-CONTEXT-NAME|4|2|2352
VALUE_STR='local'},

I wish you nice Christmas.


 
Hello mikrom,

thank you for your help, it is running well! (Y)

But there is a point i could not change.

In some user files there are some more Attributes or vendors. What can I do to catch all of them? btw the number of attributes and vendors is irregular.

I hope you can help me
thanks and regards
 
Hi SweetyXOXO,

First step would be to gather these information.
In the example above you had for the user one vendor and one attribute
Code:
VENDOR='2352',
ATTRIBUTE='4',
I saved them in this array
Code:
user[VENDOR] = '2352'
user[ATTRIBUTE] = '4'
If there would be more vendors and attributes e.g. like this
Code:
VENDOR='2352','2353'
ATTRIBUTE='4','5'
or like this
Code:
VENDOR='2352',
ATTRIBUTE='4',
VENDOR='2353',
ATTRIBUTE='5'
I would save them in the array with following values (which are again arrays)
Code:
user[VENDOR] = '2352,2353'
user[ATTRIBUTE] = '4,5'
then in the further processing I could split the array value string in an array and process every element of this array.
Further processing would be dependent on the output you want to achieve.
(you didn't post any examples of the input files and the desired output)
 
Hello mikrom,

thank you for your answer.
The programm should be able to handle input textfiles with any number of attributes and vendors.

The input file can look like this:

VENDOR='2352',
ATTRIBUTE='4',

and also look like this:

VENDOR='2352',
ATTRIBUTE='4',
VALUE_STR='local'},

uitem={
UITEMID='2',
VENDOR='2352',
ATTRIBUTE='5',
VALUE_STR='RESIDENTIAL'},


VENDOR='6728',
ATTRIBUTE='19',
VALUE_STR='local'},


the programm should be able to create an output file for the 2nd example like this:


VENDOR='2352', ALPER
ATTRIBUTE='4', 858|ALPER-CONTEXT-NAME|4|2|2352
VALUE_STR='local'},

uitem={
UITEMID='2',
VENDOR='2352', ALPER
ATTRIBUTE='5', 859|ALPER-BRIDGE-GROUP|5|2|2352
VALUE_STR='RESIDENTIAL'},


VENDOR='6728', MARKONE
ATTRIBUTE='19', 845|MARKONE-ALLOW-ALL-VR-ACCESS|19|1|6728
VALUE_STR='local'},




Im an absolute noob what awk is about and have no idea how to solve this. Therefore im very thankful for your help

thanks and regards


 
Hi

now I had to upgrade from ldap to sql.

The tables with the dictionarys are the same.
The sx_user.txt looks like that know
Code:
USER_ID USER_NAME       USER_DIST       USER_ITEM_ID    TYPE    VALUE_SN        TAG     VENDOR  ATTRIBUTE
10000000123  ur002   1000    30000000028     1       1       0       2352    4
10000000123  ur002   1000    30000000038     1       2       0       2352    5
10000000123  ur002   1000    30000000048     1       3       0       2352    6
unfortunately the columns are displaced, but im trying to solve this.

How can I change the awk script to get the wished result?
for example:
Code:
USER_ID         USER_NAME     USER_DIST      USER_ITEM_ID     VENDOR  ATTRIBUTE   ATTR_VALUE
10000000123     ur002         1000           30000000028      2352    4           858|ALPER-CONTEXT-NAME|4|2|2352
10000000123     ur002         1000           30000000038      2352    5           859|ALPER-BRIDGE-GROUP|5|2|2352
10000000123     ur002         1000           30000000048      2352    6           860|ALPER-BG-AGING-TIME|6|2|2352


Can you help me?
 
You say, that sx_user.txt has changed from
Code:
username='ur002'
TAG='0',
VENDOR='2352',
ATTRIBUTE='4',
VALUE_STR='local'},
to
Code:
USER_ID      USER_NAME  USER_DIST   USER_ITEM_ID    TYPE VALUE_SN     TAG     VENDOR   ATTRIBUTE
10000000123  ur002      1000         30000000028       1        1       0       2352     4
10000000123  ur002      1000         30000000038       1        2       0       2352     5
10000000123  ur002      1000         30000000048       1        3       0       2352     6
But what about the other files?
Didn't they changed ? Are they still as before, i.e. like this ?
sx_vendor.txt
Code:
0|MARKONE|6728
1|IKINCI|5
2|UEC|9
3|FEREI|61
4|OCOCU|166
5|ALPER|2352

sx_attrib.txt
Code:
845|MARKONE-ALLOW-ALL-VR-ACCESS|19|1|6728
846|MARKONE-ALT-CLI-ACCESS-LEVEL|20|2|6728
847|MARKONE-ALT-CLI-VROUTER-NAME|21|2|6728
848|MARKONE-SA-VALIDATE|22|1|6728
849|MARKONE-IGMP-ENABLE|23|1|6728
850|UEC-USER-PRIV|1|1|9
851|UEC-SERVICE-PROFILE|2|2|9
852|UEC-VPN-NAME|3|2|9
853|UEC-ISOS-VERSION|4|1|9
854|UEC-L2TP-TUNSET|5|1|9
855|ALPER-CLIENT-DNS-PRI|1|2|2352
856|ALPER-CLIENT-DNS-SEC|2|2|2352
857|ALPER-DHCP-MAX-LEASES|3|1|2352
858|ALPER-CONTEXT-NAME|4|2|2352
859|ALPER-BRIDGE-GROUP|5|2|2352
860|ALPER-BG-AGING-TIME|6|2|2352
 
exactly, but i just managed to get the table in the right format so far.
now all the files are looking like your examples. but i have no idea how to change the awk script
 
Hi,

Because the master file changed the structure essentially, the awk script need to be reworked.

So, I named the file with the new structure sx_user2.txt:
Code:
USER_ID      USER_NAME  USER_DIST   USER_ITEM_ID    TYPE VALUE_SN     TAG     VENDOR   ATTRIBUTE
10000000123  ur002      1000         30000000028       1        1       0       2352     4
10000000123  ur002      1000         30000000038       1        2       0       2352     5
10000000123  ur002      1000         30000000048       1        3       0       2352     6
The other 2 files sx_vendor.txt and sx_attrib.txt remain the same.

Then I tried this:
sx_ldap_user2.awk
Code:
[COLOR=#0000ff]# Run:[/color]
[COLOR=#0000ff]#   awk -f sx_ldap_user2.awk sx_user2.txt sx_vendor.txt sx_attrib.txt[/color]
[COLOR=#6a5acd]BEGIN[/color] {
  [COLOR=#6a5acd]FS[/color] = [COLOR=#ff00ff]"|"[/color]
  [COLOR=#0000ff]# line number of the master file[/color]
  line_nr = [COLOR=#ff00ff]0[/color]
}

[COLOR=#0000ff]# Line rules[/color]

[COLOR=#6a5acd]$0[/color] ~ [COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd] [/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]*$[/color][COLOR=#ff00ff]/[/color] {
  [COLOR=#0000ff]# skip empty line[/color]
  [COLOR=#a52a2a][b]next[/b][/color]
}

{
  [COLOR=#0000ff]# chomp current line[/color]
  chomp_line()
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]1[/color]] {
  [COLOR=#0000ff]# store header[/color]
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]NR[/color] == [COLOR=#ff00ff]1[/color]) {
    header = [COLOR=#6a5acd]$0[/color]
    header = header [COLOR=#ff00ff]" ATTR_VALUE"[/color]
  }
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]NR[/color] > [COLOR=#ff00ff]1[/color] ){
    [COLOR=#0000ff]# store data lines into array[/color]
    data[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$0[/color]
    [COLOR=#0000ff]# replace spaces with separator "|"[/color]
    [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[[/color][COLOR=#6a5acd] [/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]"|"[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
    [COLOR=#0000ff]# store VENDOR and ATTRIBUTE into arrays[/color]
    vendor[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$8[/color]
    attribute[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$9[/color]
    [COLOR=#0000ff]# [/color]
    line_nr++
  }    
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]2[/color]] {
  [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
    vendor_nr = vendor[[COLOR=#6a5acd]j[/color]]
    [COLOR=#a52a2a][b]if[/b][/color] (vendor_nr == [COLOR=#6a5acd]$3[/color]) {
       vendor_name[[COLOR=#6a5acd]vendor_nr[/color]] = [COLOR=#6a5acd]$2[/color]
    }
  }
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]3[/color]] {
  [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
    [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]$2[/color] ~ vendor_name[[COLOR=#6a5acd]vendor[/color][[COLOR=#6a5acd]j[/color]]] && attribute[[COLOR=#6a5acd]j[/color]] == [COLOR=#6a5acd]$3[/color]) {
      [COLOR=#0000ff]# add attribute value to data line[/color]
      data[[COLOR=#6a5acd]j[/color]] = data[[COLOR=#6a5acd]j[/color]] [COLOR=#ff00ff]"       "[/color] [COLOR=#6a5acd]$0[/color]
    }
  }
}


[COLOR=#6a5acd]END[/color] {
  [COLOR=#0000ff]# print result[/color]
  [COLOR=#a52a2a][b]print[/b][/color] header
  [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
    [COLOR=#a52a2a][b]print[/b][/color] data[[COLOR=#6a5acd]j[/color]] 
  }
}

[COLOR=#0000ff]# -- fuctions --[/color]
[COLOR=#a52a2a][b]function[/b][/color] chomp_line() {
  [COLOR=#0000ff]# strip out the carriage return or line feed at the end of current line[/color]
  [COLOR=#0000ff]# the function modifies global variable $0 (current line)[/color]
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\r[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\n[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
}

Results
Code:
$ awk -f sx_ldap_user2.awk sx_user2.txt sx_vendor.txt sx_attrib.txt
USER_ID      USER_NAME  USER_DIST   USER_ITEM_ID    TYPE VALUE_SN     TAG     VENDOR   ATTRIBUTE ATTR_VALUE
10000000123  ur002      1000         30000000028       1        1       0       2352     4       858|ALPER-CONTEXT-NAME|4|2|2352
10000000123  ur002      1000         30000000038       1        2       0       2352     5       859|ALPER-BRIDGE-GROUP|5|2|2352
10000000123  ur002      1000         30000000048       1        3       0       2352     6       860|ALPER-BG-AGING-TIME|6|2|2352

or you can redirect the output in a file sx_out.txt like this:
Code:
$ awk -f sx_ldap_user2.awk sx_user2.txt sx_vendor.txt sx_attrib.txt > sx_out.txt
 
Hello Mikrom,

wow, the code is looking pretty well, thank you very much!

But unfortunately it doesnt work :/

I checked all the files, they all look the same. The only difference is that i have column names above the columns in the attribute and vendor tables.
my result is looking like this

Code:
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+ ATTR_VALUE
| USER_ID        | USER_NAME         | USER_DIST | USER_ITEM_ID | TYPE | VALUE_SN | TAG  | VENDOR | ATTRIBUTE |
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+
| 10000000123  | ur002 |      1000 |  30000000048 |    1 |        1 |    0 |   2352 |        4|
| 10000000123  | uur02 |      1000 |  30000000048 |    1 |        2 |    0 |   2352 |        5|
| 10000000123  | uur02 |      1000 |  30000000048 |    1 |        3 |    0 |   2352 |        6|
| 10000000123  | ur002 |      1000 |  30000000048 |    1 |        4 |    0 |   2352 |        7|
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+       +---------------------+-----------------------------------------+-------------------+-----------+--------+
 
oh okay i just noticed that the attr and vendor table files looks like this:

Code:
+---------------------+-----------------------------------------+-------------------+-----------+--------+
| AAADICTIONARYATTRID | ATTRIBUTENAME                           | ATTRIBUTEENCODING | VALUETYPE | VENDOR |
+---------------------+-----------------------------------------+-------------------+-----------+--------+
|                  845| MARKONE-ALLOW-ALL-VR-ACCESS             |                19 |         1 |   6728 |
|                  846| MARKONE-ALT-CLI-ACCESS-LEVEL            |                20 |         2 |   6728 |
|                  847| MARKONE-ALT-CLI-VROUTER-NAME            |                21 |         2 |   6728 |
|                  848| MARKONE-SA-VALIDATE                     |                22 |         1 |   6728 |
|                  849| MARKONE-IGMP-ENABLE                     |                23 |         1 |   6728 |
|                  850| UEC-USER-PRIV                           |                 1 |         1 |      9 |
|                  851| UEC-SERVICE-PROFILE                     |                 2 |         2 |      9 |
|                  852| UEC-VPN-NAME                            |                 3 |         2 |      9 | 
|                  853| UEC-ISOS-VERSION                        |                 4 |         1 |      9 |
|                  854| UEC-L2TP-TUNSET                         |                 5 |         1 |      9 |
|                  855| ALPER-CLIENT-DNS-PRI                    |                 1 |         2 |   2352 |
|                  856| ALPER-CLIENT-DNS-SEC                    |                 2 |         2 |   2352 |
|                  857| ALPER-DHCP-MAX-LEASES                   |                 3 |         1 |   2352 |
|                  858| ALPER-CONTEXT-NAME                      |                 4 |         2 |   2352 |
|                  859| ALPER-BRIDGE-GROUP                      |                 5 |         2 |   2352 |
|                  860| ALPER-BG-AGING-TIME                     |                 6 |         2 |   2352 |

the vendor file like this

Code:
+-----------------------+------------+-------------------+
| AAADICTIONARYVENDORID | VENDORNAME | VENDORENCODINGINT |
+-----------------------+------------+-------------------+
|                     0 | MARKONE    |              6728 |
|                     1 | IKINCI     |                 5 |
|                     2 | UEC        |                 9 |
|                     3 | FEREI      |                61 |
|                     4 | OCOCU      |               166 |
|                     5 | ALPER      |              2352 |

the user file like this

Code:
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+
| USER_ID        | USER_NAME         | USER_DIST | USER_ITEM_ID | TYPE | VALUE_SN | TAG  | VENDOR | ATTRIBUTE |
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+
| 10000000123    | ur002             |      1000 |  30000000048 |    1 |        1 |    0 |   2352 |        4|
| 10000000123    | uur02             |      1000 |  30000000048 |    1 |        2 |    0 |   2352 |        5|
| 10000000123    | uur02             |      1000 |  30000000048 |    1 |        3 |    0 |   2352 |        6|
| 10000000123    | ur002             |      1000 |  30000000048 |    1 |        4 |    0 |   2352 |        7|
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+
Im sorry, i thought it would not have any influence in awk :(
 
Hi SweetyXOXO,

i thought it would not have any influence in awk

Naturally, changing the structure of the tables has influence on the awk-script :)
For example you have in all your newest files headers with frames which take first three lines - I had header only in the first file and this took only one line.
Then I had a file sx_user2.txt, where the columns were separated only by spaces, in your file they are separated by "|"
... all these little things have a big influence on the processing script.

You have 2 choices:
1. You need to transform your tables so they match the structure I used and then the awk script should work.
2. You have to slightly modify the script so it matches the structure of your tables.
IMO the second choice would be better for you - so you will learn a little bit about awk.
 
Hello Mikrom,

you're right i decided for the second one.

I thought it would be enough if when I find out the right field numbers, I expectet at least a little change in the output

Code:
# Run:
#  awk -f sx_ldap_user2.awk sx_user2.txt sx_vendor.txt sx_attrib.txt
BEGIN {
  FS = "|"
  # line number of the master file
  line_nr = 0
}

# Line rules

$0 ~ /^[ ]*$/ {
  # skip empty line
  next
}

{
  # chomp current line
  chomp_line()
}

FILENAME == ARGV[1] {
  # store header
  if (NR == 1) {
[COLOR=#EF2929]#$20 because I thought it is the next empty field in the table with user. Also tried $0 $1 and $2[/color]
    header = $20
    header = header " ATTR_VALUE"
  }
  if (NR > 1 ){
    # store data lines into array
    data[line_nr] = $0
    # replace spaces with separator "|"
[COLOR=#EF2929]#i don't get why we are doing this |[/color]
    gsub(/[ ]+/, "|", $0)
    # store VENDOR and ATTRIBUTE into arrays
[COLOR=#EF2929]#the new field numbers[/color]
    vendor[line_nr] = $16
    attribute[line_nr] = $18
    # 
    line_nr++
  }    
}

FILENAME == ARGV[2] {
  for (j = 0; j < line_nr; j++) {
    vendor_nr = vendor[j]
    if (vendor_nr == $6) {
       vendor_name[vendor_nr] = $4
    }
  }
}

FILENAME == ARGV[3] {
  for (j = 0; j < line_nr; j++) {
    if ($4 ~ vendor_name[vendor[j]] && attribute[j] == $6) {
      # add attribute value to data line
      data[j] = data[j] "       " $0
    }
  }
}


END {
  # print result
  print header
  for (j = 0; j < line_nr; j++) {
    print data[j] 
  }
}

# -- fuctions --
function chomp_line() {
  # strip out the carriage return or line feed at the end of current line
  # the function modifies global variable $0 (current line)
[COLOR=#EF2929]# maybe i have to change something here?[/color]
  sub(/\r$/, "", $0)
  sub(/\n$/, "", $0)
}

but there is nothing happening in the output .. what should I also change?
 
I see, you misunderstood the essential things about awk:
Please google and read something about awk - there are lots of tutorials and examples free available.
Please note, that without a little effort, there is no success.

For an gentle introduction:
awk processes a file line by line
So, $0 means the whole line.
i.e., if you have set the field separator FS equal to "|"
and you have a file with a lines like
Code:
foo | bar | baz | spam | eggs | foobar
then $0 means the whole line (above)
$1 means the 1. column of the line i.e. "foo"
and $5 means the 5. column which contains "eggs"

IMO it's not complicated but simple, can you undestood this ?

About your other questions:
the functions sub() means substitute (only one) and gsub() global substitute, (i.e everything)
 
Hi,

so i tried to fix the problem but i am an absolute looser in awk and i was never a good programmer and i really have to solve this problem.
Can you help me please?


regards
 
SweetyXOXO said:
Can you help me please?

Hi SweetyXOXO,
Of course I can :)

What about this ?

sx_ldap_user3.awk
Code:
[COLOR=#0000ff]# Run:[/color]
[COLOR=#0000ff]#   awk -f sx_ldap_user3.awk sx_user3.txt sx_vendor3.txt sx_attrib3.txt[/color]
[COLOR=#6a5acd]BEGIN[/color] {
  [COLOR=#6a5acd]FS[/color] = [COLOR=#ff00ff]"|"[/color]
  [COLOR=#0000ff]# line number of the master file[/color]
  line_nr = [COLOR=#ff00ff]0[/color]
}

[COLOR=#0000ff]# Line rules[/color]
{ 
  [COLOR=#0000ff]# chomp current line[/color]
  chomp_line()
}

{
  [COLOR=#0000ff]# remove spaces[/color]
  [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[[/color][COLOR=#6a5acd] [/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#0000ff]# remove "|" characters from the beginning and the end of line[/color]
  [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#6a5acd]\|[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\|[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
}

[COLOR=#6a5acd]$0[/color] ~ [COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd] [/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]*$[/color][COLOR=#ff00ff]/[/color] {
  [COLOR=#0000ff]# skip empty line[/color]
  [COLOR=#a52a2a][b]next[/b][/color]
}

[COLOR=#6a5acd]$0[/color] ~ [COLOR=#ff00ff]/[/color][COLOR=#6a5acd]^[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]*[/color][COLOR=#ff00ff][[/color][COLOR=#6a5acd]-[/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]/[/color] {
  [COLOR=#0000ff]# skip frame line[/color]
  [COLOR=#a52a2a][b]next[/b][/color]
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]1[/color]] {
  [COLOR=#0000ff]# store header[/color]
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]FNR[/color] == [COLOR=#ff00ff]2[/color]) {
    header = [COLOR=#6a5acd]$0[/color]  
    header = header [COLOR=#ff00ff]"|ATTR_VALUE"[/color]
  }
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]FNR[/color] > [COLOR=#ff00ff]3[/color] ){
    [COLOR=#0000ff]# store VENDOR and ATTRIBUTE into arrays[/color]
    vendor[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$8[/color]
    attribute[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$9[/color]
    [COLOR=#0000ff]# replace "|" with space and store data line into array[/color]
    [COLOR=#008b8b]gsub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\|[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]" "[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
    data[[COLOR=#6a5acd]line_nr[/color]] = [COLOR=#6a5acd]$0[/color]
    [COLOR=#0000ff]# [/color]
    line_nr++
  }    
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]2[/color]] {
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]FNR[/color] > [COLOR=#ff00ff]3[/color] ){
    [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
      vendor_nr = vendor[[COLOR=#6a5acd]j[/color]]
      [COLOR=#a52a2a][b]if[/b][/color] (vendor_nr == [COLOR=#6a5acd]$3[/color]) {
        vendor_name[[COLOR=#6a5acd]vendor_nr[/color]] = [COLOR=#6a5acd]$2[/color]
      }
    }
  }
}

[COLOR=#6a5acd]FILENAME[/color] == [COLOR=#6a5acd]ARGV[/color][[COLOR=#6a5acd]3[/color]] {
  [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]FNR[/color] > [COLOR=#ff00ff]3[/color] ){
    [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
      [COLOR=#a52a2a][b]if[/b][/color] ([COLOR=#6a5acd]$2[/color] ~ vendor_name[[COLOR=#6a5acd]vendor[/color][[COLOR=#6a5acd]j[/color]]] && attribute[[COLOR=#6a5acd]j[/color]] == [COLOR=#6a5acd]$3[/color]) {
        [COLOR=#0000ff]# add attribute value to data line[/color]
        data[[COLOR=#6a5acd]j[/color]] = data[[COLOR=#6a5acd]j[/color]] [COLOR=#ff00ff]"       "[/color] [COLOR=#6a5acd]$0[/color]
      }
    }  
  }
}

[COLOR=#6a5acd]END[/color] {
  [COLOR=#0000ff]# *** print result ***[/color]
  [COLOR=#0000ff]# define format string[/color]
  format_string = [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]%-12s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%-9s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%-9s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%-12s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%-6s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%-10s[/color][COLOR=#ff00ff]  [/color][COLOR=#6a5acd]%s[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color]

  [COLOR=#0000ff]# header[/color]
  [COLOR=#0000ff]# split header into array[/color]
  [COLOR=#008b8b]split[/color](header[COLOR=#6a5acd],[/color] h[COLOR=#6a5acd],[/color] [COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\|[/color][COLOR=#ff00ff]/[/color])
  [COLOR=#0000ff]# print only columns which are needed[/color]
  [COLOR=#a52a2a][b]printf[/b][/color] format_string[COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]1[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]2[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]3[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]4[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]8[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]9[/color]][COLOR=#6a5acd],[/color] h[[COLOR=#6a5acd]10[/color]]

  [COLOR=#0000ff]# data[/color]
  [COLOR=#a52a2a][b]for[/b][/color] (j = [COLOR=#ff00ff]0[/color][COLOR=#6a5acd];[/color] j < line_nr[COLOR=#6a5acd];[/color] j++) {
    [COLOR=#0000ff]# split data into array[/color]
    [COLOR=#0000ff]#print data[j]  [/color]
    [COLOR=#008b8b]split[/color](data[[COLOR=#6a5acd]j[/color]][COLOR=#6a5acd],[/color] d[COLOR=#6a5acd],[/color] [COLOR=#ff00ff]/[[/color][COLOR=#6a5acd] [/color][COLOR=#ff00ff]][/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]/[/color])
    [COLOR=#0000ff]# print only columns which are needed[/color]
    [COLOR=#a52a2a][b]printf[/b][/color] format_string[COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]1[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]2[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]3[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]4[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]8[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]9[/color]][COLOR=#6a5acd],[/color] d[[COLOR=#6a5acd]10[/color]] 
  }
}

[COLOR=#0000ff]# -- fuctions --[/color]
[COLOR=#a52a2a][b]function[/b][/color] chomp_line() {
  [COLOR=#0000ff]# strip out the carriage return or line feed at the end of current line[/color]
  [COLOR=#0000ff]# the function modifies global variable $0 (current line)[/color]
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\r[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
  [COLOR=#008b8b]sub[/color]([COLOR=#ff00ff]/[/color][COLOR=#6a5acd]\n[/color][COLOR=#6a5acd]$[/color][COLOR=#ff00ff]/[/color][COLOR=#6a5acd],[/color] [COLOR=#ff00ff]""[/color][COLOR=#6a5acd],[/color] [COLOR=#6a5acd]$0[/color])
}

if I try it with your input files
sx_user3.txt
Code:
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+
| USER_ID        | USER_NAME         | USER_DIST | USER_ITEM_ID | TYPE | VALUE_SN | TAG  | VENDOR | ATTRIBUTE |
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+
| 10000000123    | ur002             |      1000 |  30000000048 |    1 |        1 |    0 |   2352 |          4|
| 10000000123    | uur02             |      1000 |  30000000048 |    1 |        2 |    0 |   2352 |          5|
| 10000000123    | uur02             |      1000 |  30000000048 |    1 |        3 |    0 |   2352 |          6|
| 10000000123    | ur002             |      1000 |  30000000048 |    1 |        4 |    0 |   2352 |          7|
+----------------+-------------------+-----------+--------------+------+----------+------+--------+-----------+

sx_vendor3.txt
Code:
+-----------------------+------------+-------------------+
| AAADICTIONARYVENDORID | VENDORNAME | VENDORENCODINGINT |
+-----------------------+------------+-------------------+
|                     0 | MARKONE    |              6728 |
|                     1 | IKINCI     |                 5 |
|                     2 | UEC        |                 9 |
|                     3 | FEREI      |                61 |
|                     4 | OCOCU      |               166 |
|                     5 | ALPER      |              2352 |

sx_attrib3.txt
Code:
+---------------------+-----------------------------------------+-------------------+-----------+--------+
| AAADICTIONARYATTRID | ATTRIBUTENAME                           | ATTRIBUTEENCODING | VALUETYPE | VENDOR |
+---------------------+-----------------------------------------+-------------------+-----------+--------+
|                  845| MARKONE-ALLOW-ALL-VR-ACCESS             |                19 |         1 |   6728 |
|                  846| MARKONE-ALT-CLI-ACCESS-LEVEL            |                20 |         2 |   6728 |
|                  847| MARKONE-ALT-CLI-VROUTER-NAME            |                21 |         2 |   6728 |
|                  848| MARKONE-SA-VALIDATE                     |                22 |         1 |   6728 |
|                  849| MARKONE-IGMP-ENABLE                     |                23 |         1 |   6728 |
|                  850| UEC-USER-PRIV                           |                 1 |         1 |      9 |
|                  851| UEC-SERVICE-PROFILE                     |                 2 |         2 |      9 |
|                  852| UEC-VPN-NAME                            |                 3 |         2 |      9 |
|                  853| UEC-ISOS-VERSION                        |                 4 |         1 |      9 |
|                  854| UEC-L2TP-TUNSET                         |                 5 |         1 |      9 |
|                  855| ALPER-CLIENT-DNS-PRI                    |                 1 |         2 |   2352 |
|                  856| ALPER-CLIENT-DNS-SEC                    |                 2 |         2 |   2352 |
|                  857| ALPER-DHCP-MAX-LEASES                   |                 3 |         1 |   2352 |
|                  858| ALPER-CONTEXT-NAME                      |                 4 |         2 |   2352 |
|                  859| ALPER-BRIDGE-GROUP                      |                 5 |         2 |   2352 |
|                  860| ALPER-BG-AGING-TIME                     |                 6 |         2 |   2352 |
|                  860| ALPER-SWEETY-XOXO                       |                 7 |         1 |   2352 |

I get this output
Code:
$ awk -f sx_ldap_user3.awk sx_user3.txt sx_vendor3.txt sx_attrib3.txt
USER_ID       USER_NAME  USER_DIST  USER_ITEM_ID  VENDOR  ATTRIBUTE   ATTR_VALUE
10000000123   ur002      1000       30000000048   2352    4           858|ALPER-CONTEXT-NAME|4|2|2352
10000000123   uur02      1000       30000000048   2352    5           859|ALPER-BRIDGE-GROUP|5|2|2352
10000000123   uur02      1000       30000000048   2352    6           860|ALPER-BG-AGING-TIME|6|2|2352
10000000123   ur002      1000       30000000048   2352    7           860|ALPER-SWEETY-XOXO|7|1|2352

Say, is this what you need ?
 
wow! you are a magician mikrom! *-*

thank you very much!
now i will try to get the output in a table form, and then it will be perfect!

xx
 
Hi SweetyXOXO,
You are welcome.
I hope it helped you a little bit to understand what about is awk.
Analyze the code, experiment with it, so you would be able to write a next little script self.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top