LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   How-To format output in CSV to match stdout? (https://www.linuxquestions.org/questions/programming-9/how-to-format-output-in-csv-to-match-stdout-905633/)

Linux_Kidd 09-29-2011 11:28 AM
How-To format output in CSV to match stdout?
 
small script that emails a login faliures report for all users of the system. i cant get the output to format nicely for excel. stdout displays ok, but when i open the csv i have the stdout spacing but its all in a single cell, making the csv look horrible. how do i write the output file so when it opens in excel the data is columnized?

Code:
#!/bin/bash
#
TODAY=`date +%F`
FULLDATE=`date`
HOST=`hostname`
SUBJECT="NO REPLY: Login Failures Report for $HOST"
FILENAME="$HOME/LoginFailuresReport.$HOST.$TODAY.csv"
ATTACHNAME="LoginFailuresReport.$HOST.$TODAY.csv"
#
USERS=`cat /etc/passwd |cut -d: -f1`
echo "This report created on: $FULLDATE" >> $FILENAME
echo "Hostname: $HOST" >> $FILENAME
echo "////////////////////////////////////////////////////////" >> $FILENAME
echo "Login          Failures  Latest failure              From" >> $FILENAME
echo ""
for i in $USERS
  do
    pam_tally2 -u $i |awk '0 ==NR % 2' >> $FILENAME
  done
uuencode $FILENAME $ATTACHNAME |mail -s "$SUBJECT" "someone@some_place"

colucix 09-29-2011 11:41 AM
Most likely you need to convert the CSV file in DOS format:
Code:
unix2dos $FILENAME
before attach it to the outgoing e-mail. The issue is that *nix systems use a line feed character as terminator, whereas windows uses a carriage return followed by line feed. A detailed explanation here: http://en.wikipedia.org/wiki/Newline

lithos 09-29-2011 11:48 AM
well, not quite
if data is not formatted in CSV then it won't be read into columns (CSV - comma separated values)

so the structure of data should be:
Code:
"Col 1 header","Col 2 header","Col 3 header"
"data first column","second column data","third goes here"
your
Code:
echo "Login          Failures  Latest failure              From" >> $FILENAME
does not produce anything like that

so you should modify it
and also what is the output of
Code:
pam_tally2 -u $i |awk '0 ==NR % 2' >> $FILENAME
it should also create lines with "data1","data2" etc.


good luck

Linux_Kidd 09-29-2011 11:59 AM
Quote:
Originally Posted by lithos (Post 4485681)
and also what is the output of
Code:
pam_tally2 -u $i |awk '0 ==NR % 2' >> $FILENAME
it should also create lines with "data1","data2" etc.
pam_tally2 output is two rows with 4 fields like this.

Login Failures Latest failure From
root 0 09/28/11 16:01:04 host.domain.com

so the awk prints every 2nd line as i dont need to repeat the 1st line in the report, etc. since pam_tally2 runs per "i" i changed the awk to be awk 'FNR ==2 {print}'

adding "," between fields works ok.

i changed it to be
pam_tally2 -u $i |awk 'FNR ==2 {print}' |awk '{ print $1, ",", $2, ",", $3, " ", $4, ",", $5}' >> $FILENAME

that works.

whats weird is, if user is uid=0 and gid=0 pam_tally2 reports the user as "root".
thnx

grail 09-29-2011 01:19 PM
Quote:
whats weird is, if user is uid=0 and gid=0 pam_tally2 reports the user as "root".
Why would this be weird? This is the standard on virtually all linux boxes that I am aware of (could be wrong but on the 20+ ditros I have tried this is the case).

Also, no need to get so heavy handed with awk:
Code:
pam_tally2 -u $i |awk 'FNR ==2 && $1 = $1' OFS=","

Linux_Kidd 09-29-2011 02:03 PM
Quote:
Originally Posted by grail (Post 4485745)
Why would this be weird? This is the standard on virtually all linux boxes that I am aware of (could be wrong but on the 20+ ditros I have tried this is the case).

Also, no need to get so heavy handed with awk:
Code:
pam_tally2 -u $i |awk 'FNR ==2 && $1 = $1' OFS=","
well, almost. notice the pam_tally2 output fields $3 and $4 belong together with the column heading of "Latest failure", so thats why i took the awk-newbie approach like i did.

so i simplified some and went with

awk 'FNR ==2 { print $1","$2","$3," "$4","$5}' >> $FILENAME

as for pam_tally2, seems silly to think uid=0 gid=0 is the root account. sure, an account with 0 0 is a "root" but that doesnt mean it is "root". why wouldnt it just report the usernames as seen in /etc/passwd ???

thnx all for the help.

grail 09-29-2011 02:37 PM
Quote:
why wouldnt it just report the usernames as seen in /etc/passwd ???
So by this you are saying that root is not 0:0?

Linux_Kidd 09-29-2011 03:22 PM
Quote:
Originally Posted by grail (Post 4485801)
So by this you are saying that root is not 0:0?
no, what i am saying is, i have a user in passwd "johndoe" with uid=0 gid=0 and when i run "pam_tally2 -u johndoe" it returns "root" as the username in its output. i was expecting it to return the same username i passed it as a argument with "-u" switch, etc.

certainly "root" and "johndoe" are different accounts on the system.


All times are GMT -5. The time now is 09:32 AM.