I need help

I have two input files and I'd like to generate a report based on the two.

filea:
hostname,account1,password
,account2,password
hostname,account1,password
hostname,account1,password
,account1,password
,account2,password

repeating hostnames are blank

fileb:
hosta
hostb
hostc

I need to generate a record of "hostname account password" for all hosts in fileb that occur in filea for a specific account. I.e. I want a list of root passwords for all hosts in fileb. I know the logic I want to use but don't have the scripting skills to implement it. I think the easiest way would be to use awk to fill in the blanks with the hostname of filea and then simply use a shell script to grep for each host in fileb of the new filea.

The other way is to run down filea for each hostname in fileb until either the account is matched or the hostname is different and print the result. If the account does not exist for the given hostname do nothing.

I'm trying to save manually doing this by scripting it thinking it would be faster but maybe I was wrong

Here is the framework I came up with so far for the harder way because it would be more elegant

#!/bin/sh
#
hosts=`cat hosts.txt`
hostname="x"
last="x"

for name in ${hosts}
do
#the awk logic would come here
done

you may give us an example of the result that you try to achieve and base on that we may come up with a solution.

You mean something like this?

sed -e 's/.*/^&,/' fileb | grep -f - filea

Thanks for the reply. My version does not support -f unfortunately. Looking at the code where would I specify the account name I'm looking for? Say if I want to list the host,account,password for all root accounts? Also would this work for records where the host name is blank?

To clarify the problem, filea has blanks for repeating hostnames and if say root is in a record where there is no hostname how do you derive the hostname for the record? The hostname I'm looking for is the prior non blank record.

example:

hosta,johndoe,abc123
,root,4009dlkj
hostb,janedoe,rrrrrr

if host a is in fileb the expected out put for root is

hosta,root,4009dlkj

I was trying to use awk to replace the blank with a hostname from the previous nonblank record but I kept getting errors. I couldn't even store $1 to a variable and print it as a test. I know sed is very powerful but I didn't think this could be done with one line.

I ended up doing it manually last night. At this point it's inquiring minds want to know and it might come in handy if I have to do it again.

TIA

True, but most that don't will support either egrep -f or fgrep.

Oh, I misunderstood your problem description. Here's something which hopefully is closer to what you wanted.

awk -F , -v acct=root 'NR==FNR { h[$1]++; next; }
{ if($1 == "") $1=host; host=$1; if (! h[$1]) next;
if ($2 == acct) print }' fileb filea

That's exactly what I wanted. My version of awk didn't support the passing of the variable but I do have nawk and it worked perfectly.

Based on awk documentation I was trying to use the begin block to read the file but couldn't figure out how to read from two files. Would you mind explaining how awk determines which file to read from? Is it the code in the first set of braces {} reads the first file supplied and the second set the second file?

Thank you. I learned a lot from this!