3

I have two csv files that contain various user data; they share one common field (username).

file A:
username ; Fullname ; mail
Bob      ; Bob Hope ; bob@hope.org

file B:
username ; LastLogonTime  ; AccountStatus (locked=0 or unlocked=1)
Bob      ; 2018-10-01 etc.; 0

For audit purposes, I want to use Bash to loop over A, cross checking with B whether the account is locked in which case I can mail the user to the mail address in A.

awk -F";"

allows me to go over A; that's easy - but I'm at a loss when trying to do the cross-check loop over B.

6
  • Bash isn’t a text editor.
    – Jeff Schaller
    Oct 2, 2018 at 11:21
  • Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
    – DavDav
    Oct 2, 2018 at 11:40
  • then please provide the correct csv tables, or at least csv tables...
    – pLumo
    Oct 2, 2018 at 11:48
  • Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
    – DavDav
    Oct 2, 2018 at 11:53
  • but the solution is way easier.
    – pLumo
    Oct 2, 2018 at 11:55

4 Answers 4

5

Using awk, first read the usernames of users whose account are locked from the second file, then extract the email addresses of these from the first file (then hope that they don't need to log in to read their emails):

awk -F ';' 'NR == FNR && $NF == 0    { names[$1] }
            NR != FNR && $1 in names { print $NF }' B.csv A.csv

This assumes that each username has an equal amount of whitespace around them in both files. If that is not so, you may use -F ' *; *' to include any space characters in the delimiter that awk is using. It also assumes that there are no embedded ; characters in the data.

NR is the record (line) number of the current record overall, and FNR is the same number but within the current file. If NR == FNR, then we are reading from the first file given on the command line (B.csv). NF is the number of fields (columns) in the current record and $NF is the data in the last field (and $1 is the data in the first field).

The code above uses an associative array/hash, names, keyed on the usernames of locked-out users read from the first file (B.csv). The $1 in names will be true if $1 is a key in that array.

Putting this into a loop:

awk -F ';' 'NR == FNR && $NF == 0    { names[$1] }
            NR != FNR && $1 in names { print $NF }' B.csv A.csv |
while read addr; do
    printf 'Would send an email to "%s"\n' "$addr"
    #mail -s 'Account locked' "$addr" <template-email.txt
done

Or something along those lines. Reading the email addresses in this way in the loop would delete any whitespace around them. The loop above does not send emails but prints the addresses that needs sending to. Remove the # before mail (and write some form email in template-email.txt) to actually send an email (but you may want to do it differently).


Using csvkit:

csvjoin -d ';' -c 1 A.csv B.csv |
csvgrep -c 5 -m False |
csvcut -S -c 3 | sed 1d

CSVkit provides CSV parsing tools for working with CSV files. This would be needed if your CSV data is not "simple", i.e. if it uses CSV rules for quoting embedded ; characters etc. The pipeline above will

  1. Join the two files on the usernames (whitespaces are significant).
  2. Extract the data for the users that are locked out (the 0 will have been changed to False at this point in the pipeline).
  3. Extract the email addresses.
  4. Remove the CSV header (using the last sed command).
1
  • Awesome! Thanks for this indepth explanation - most helpful :)
    – DavDav
    Oct 2, 2018 at 13:10
-1

Use a specialized tool for doing tasks like this (a.k.a. a database):

# Remove spaces around the field separator
sed -i.fixed 's/ *\; */\;/g' a
sed -i.fixed 's/ *\; */\;/g' b

# Add to sqlite database
echo -e '.separator ";"\n.import a.fixed a' | sqlite3 db.sqlite
echo -e '.separator ";"\n.import b.fixed b' | sqlite3 db.sqlite

# Select whatever you need
echo -e 'select a.username,a.mail,b."AccountStatus (locked=0 or unlocked=1)" from a join b on a.username = b.username;' | sqlite3 db.sqlite

awk solution:

users=( $(awk -F";" 'NR>1{print $1";"$3}' a) )
for u in "${users[@]}"; do
    username=$(echo "$u" | cut -d';' -f1)
    mail=$(echo "$u" | cut -d';' -f2)
    awk -v "u=$username" -v "m=$mail" -F';' 'NR>1 { if ($3 == 0) print "User "u" ("m") is locked"; }' b
done
5
  • 1
    Not interested in sqlite solutions but thanks anyway :)
    – DavDav
    Oct 2, 2018 at 11:57
  • All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
    – DavDav
    Oct 2, 2018 at 12:03
  • strange application that keeps account information in csv files ... but okay, I added an awk solution. But still, what you want to do is easily possible with the sqlite solution.
    – pLumo
    Oct 2, 2018 at 12:20
  • Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
    – DavDav
    Oct 2, 2018 at 12:48
  • haha, yes you're right ... I need to reconsider my statement ;-)
    – pLumo
    Oct 2, 2018 at 12:54
-1
#!/bin/bash 

cat fileA.txt | sed 1d | while IFS=';' read -r line; do #read fileA.txt starting with line #2
name=$(echo $line | awk '{print $1}') #find names in each line/column 1 of the table 
lock_status=$(grep $name fileB.txt | awk '{print $5}') # find lock/unlock status in fileB.txt

    if [[ "$lock_status" -eq 0 ]];then 

    echo "Locked: To mail the user : replace echo by the command mail";

        else

    echo "unlocked";
     fi
done
1
  • uuoc and in general quite prone to errors.
    – pLumo
    Oct 2, 2018 at 12:31
-1

First, if you do have spaces around your delimiter, you need to remove them in your script, like @RoVo said. The sed commands will do that for you.

Second, you basically want to have a while loop reading in each line from the fixed fileA, and getting the user name and email address, and optionally the user's full name. You then want to check for the status of that user in the fixed fileB.

Something like the following little loop should get you started:

#!/bin/bash

# Remove spaces around delimiter
sed -i.fixed 's/[       ]*\;[   ]*/\;/g' fileA
sed -i.fixed 's/[       ]*\;[   ]*/\;/g' fileB

# Read in each line from the fixed fileA
while read l; do

  # Skip the header line
  [[ ${l} =~ ^username ]] && continue

  # Get the user from the line that was read in.
  u=$(echo ${l} | awk -F\; '{print $1}')

  # Get the lock status for that user from the fixed fileB
  l=$(awk -F\; -v u=${u} '{if ($1 == u) {print $3}}' fileB.fixed)

  # Echo out the 2 fields.
  echo ${u}=${l}

  # Other stuff can go here.
done <fileA.fixed

exit 0
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.