combine data of 2 files by variable

my first post ... please be gentle.
I have been working on a script to get info out of mysql. Its a support ticket system database OTRS. I can write the subject of open tickets to a text file with a unique user id.

I also have a text file with the unique user id, username and email adres. I gues it is possible to combine these files so i can mail the open support tickets to the responsible support engineer. I just cant figure out how to do so.

Example of the open support tickets text file:

id ticket subject
1 10000302 Server SYS volume out of disk space
1 10000646 User synchronisation aborted
1 10000684 GWGuardian fallback / defect powersupply
2 10000693 Set up a ftp server
5 10000719 Firewall port
5 10000750 Defect SAN controller

The user file looks like this:

id user email
1 Some User Some.user@local.nl
2 Other User Other.user@local.nl
3 xxx xxx xxx.xxx@local.nl
etc

I would like to send a list once a week with open support tickets to each engieer. Can anybody point me in the right direction or have a example for this?

many thanks in advance

Hi and welcome,

Can you provide an example of the desired output?
Do you want separated files per user id?

Regards

the desired output would be a per user email with a subject like 'open tickets' and as body the ticket id's with the subject. i would like to run it in cron so it can run once a week. if thats not possible separate files would do.

the engineers need to do there work and i'm responsible the work is done in time. they dont always have time to log in to the ticket system so when i can send the open tickets to them they at least know there is work to be done.

You can try this (not tested):

awk 'NR==FNR{a[$1]=$NF;next}
a[$1]{b[$1]=1;print $0 > $1}
END{for(i in a){if(b){system("uuencode " $1 " | mail "a)}}}
' users_file opentickets_file

If it works as expected you can place the command in a script and start it with a cronjob.

Use nawk or /usr/xpg4/bin/awk on Solaris.

Regards

Ok tnx. that's some code i wouldnt have come up with still lots to learn! i will try it tonight at home. i'll let you know if it works!

(i use SuSE Linux)

i just tested the script. it did work for a bit. When i run the script it kinda hangs. when i kill it with CRTL-C i got this message:

(Interrupt -- one more to kill letter)
No message, no subject; hope that's ok

i got this 3 times (5 tickets for 3 users) in the script directory i now do have 3 seperate files

-rw-r--r-- 1 root root  72 Feb 18 18:51 1
-rw-r--r-- 1 root root  71 Feb 18 18:51 2
-rw-r--r-- 1 root root  36 Feb 18 18:51 3
-rw-r--r-- 1 root root 179 Feb 18 18:37 opentickets.txt
-rwxr-xr-x 1 root root 193 Feb 18 18:42 script.sh
-rw-r--r-- 1 root root  73 Feb 18 18:36 users.txt

the files do contain the right info for the right engineer

OES2-SRV1:/data/test # cat 1
1     1000350     First ticket 
1     1000359     Second ticket

I just dont realy understand the awk options yet so that makes it a bit hard for me to troubleshoot. is there a way to see some logging output like in /var/log/messages ?

One correction on the code. I suppose uuencode works for you, otherwise you can alter the mail command (a [i]= mail address and id = i):

awk 'NR==FNR{a[$1]=$NF;next}
a[$1]{b[$1]=1;print $0 > $1}
END{for(i in a){if(b){system("uuencode " i " | mail "a)}}}
' users_file opentickets_file

Regards

tnx again! i did some more testing. i think there is a problem with the uuencode. i changed it to us-ascii (probably a stupid thing to do) and also tried mimencode. both are not working. i get the following error

sh: mimencode: command not found
No message, no subject; hope that's ok

but the script does run (so no need for CTRL-C) and sends the email to the right persons. only there is no subject and no body. So is there an other encode i can rather use than uuencode?

You can find some possibilties here:

How do I send email? - The UNIX and Linux Forums

Regards

ok many tnx! it already does 99% what i want. tnx for al the help. if i find what i need i'll post it here!

i finaly got the remaining 1% working. much easier than expected.

awk 'NR==FNR{a[$1]=$NF;next}
a[$1]{b[$1]=1;print $0 > $1}
END{for(i in a){if(b){system("cat " i " | mail -s OpenOTRStickets "a)}}}
' users.txt calls.txt 

I wanted to use a variable for the subject but when i do that i just get errors, and spaces between the subject words also got me errors.

I use this script for my Open Ticket Response System (OTRS) so if anybody also uses otrs and wants to use it please do. i'm not a verry good scripter so if you see something wrong in the script feel free to correct it and let me know. Its not high sience!

Short discription:
It wil query your otrs database for new, open en pending tickets and write them to a .txt file. All user accounts (agents) wil also be exported to a .txt file. the awk script then generates per user file with the open tickets per user and send it to there given e-mail adres. i added it to cron to run each monday at 8 am

#!/bin/bash

# VARIABLES

dyfw=/srv/www/htdocs/dyfw               #path to output
user="users.txt"
calls="calls.txt"

# MySQL credentials
u=<username>
p=<password>
d=<database>

# MySQL Query's
qry_usr="SELECT user_id,preferences_value FROM user_preferences WHERE 
preferences_key = 'UserEmail' ORDER BY user_id INTO OUTFILE '$dyfw/$user';"

qry_calls="SELECT user_id,tn,title FROM ticket WHERE ticket_state_id = '1' OR 
ticket_state_id = '4' OR ticket_state_id = '6' ORDER BY tn INTO OUTFILE '$dyfw/$calls';"

# Screen message
echo "MySQL Query running ..."
echo "Output open tickets to /srv/www/htdocs/$calls"
echo "Output users file to /srv/www/htdocs/$user"

# Execute SQL
mysql -u $u -p$p -D $d << eof
$qry_usr
$qry_calls
eof

mail -s "Open calls $nu" support@yourcompany.nl < $dyfw/$calls           #Complete list for the supportdesk

# Generate individual email with a list of open tickets per user
awk 'NR==FNR{a[$1]=$NF;next}
a[$1]{b[$1]=1;print $0 > $1}
END{for(i in a){if(b){system("cat " i " | mail -s OpenOTRStickets "a)}}}
' users.txt calls.txt

rm $dyfw/*.txt

a last addition to the script. i wanted to be able to delete al the open ticket files at once by a rm *.txt and Franklin52 again had the solutions:

awk 'NR==FNR{a[$1]=$NF;next}
a[$1]{b[$1]=1;print $0 > $1 ".txt"}
END{for(i in a){if(b){system("cat " i ".txt | mail -s OpenOTRStickets "a)}}}
' users.txt calls.txt