LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-29-2011, 11:28 AM   #1
Linux_Kidd
Member
 
Registered: Jan 2006
Location: USA
Posts: 539

Rep: Reputation: 51
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"
 
Old 09-29-2011, 11:41 AM   #2
colucix
Moderator
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957Reputation: 1957
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
 
Old 09-29-2011, 11:48 AM   #3
lithos
Senior Member
 
Registered: Jan 2010
Location: SI : 45.9531, 15.4894
Distribution: CentOS, OpenNA/Trustix, testing desktop openSuse 12.1 /Cinnamon/KDE4.8
Posts: 1,144

Rep: Reputation: 217Reputation: 217Reputation: 217
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
 
Old 09-29-2011, 11:59 AM   #4
Linux_Kidd
Member
 
Registered: Jan 2006
Location: USA
Posts: 539

Original Poster
Rep: Reputation: 51
Quote:
Originally Posted by lithos View Post
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

Last edited by Linux_Kidd; 09-29-2011 at 12:32 PM.
 
Old 09-29-2011, 01:19 PM   #5
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,564

Rep: Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939
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=","
 
Old 09-29-2011, 02:03 PM   #6
Linux_Kidd
Member
 
Registered: Jan 2006
Location: USA
Posts: 539

Original Poster
Rep: Reputation: 51
Quote:
Originally Posted by grail View Post
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.
 
Old 09-29-2011, 02:37 PM   #7
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,564

Rep: Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939Reputation: 1939
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?
 
Old 09-29-2011, 03:22 PM   #8
Linux_Kidd
Member
 
Registered: Jan 2006
Location: USA
Posts: 539

Original Poster
Rep: Reputation: 51
Quote:
Originally Posted by grail View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] howto: ls output to csv format limdel Linux - Newbie 12 09-19-2014 01:00 PM
So, who want's to help me format a .csv? It'll be fun, promise! pinecone Programming 8 08-09-2010 05:08 PM
Redirect output in .xls or csv format jeesun Programming 5 12-25-2009 05:56 PM
OpenOffice help , read csv-like text format ufmale Linux - Desktop 3 09-10-2008 01:26 PM
grep output on stdout and grep output to file don't match xnomad Linux - General 3 01-13-2007 04:56 AM


All times are GMT -5. The time now is 02:18 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration