I could use some help with making a script

Shell Programming and Scripting
1

I run a small instrument lab. We track our user's time on the instruments with a very manual process of 'last wtmp.1' then cut/paste data into spreadsheets. My boss makes the initial spreadsheets then I convert and format them for uploading into our billing software (COReS). Cores is looking for a very specific set of data for each 'charge' in a csv file.

Primary Comments,Customer Account Number,Transaction Date,Service Description,Quantity,Unit,Price,Service Category,PI's Name,Purchaser's Last Name,Short Contributing Center Name,Resource Name,Line Item Assistant,Line Item Comments

And each "order" is like this-

600 NMR,201895**********,07/31/13,600 MHz,34.97,Hour,6,600 MHz,Ash,Ash,"""""",600 NMR,"""""",""""""

Currently I get instrument usage data from a small perl script (@ perl-wtmp-script - Pastebin.com ) and wtmp. All of our workstations are either Red Hat or CentOS. It's output is-

  dronnow 40
  dwright3 272
  esoto1 52
  ewhite5 36
  gliu3 327
  gshaw1 382

I'm looking for something that will take our users time on the instrument, add their supervisor (the ASH entry's above) and account number (201895**********) then output all that data into a csv file suitable for uploading into Cores. Any clue where to start?

2

Don't understand. Pls show input and desired output and explain the logics to get from one to the other.

3

Thanks for the fast reply!

Input is from the various workstations that users log into, via that perl script. It outputs the username and time in minutes,

  dronnow 40
  dwright3 272
  esoto1 52
  ewhite5 36
  gliu3 327
  gshaw1 382

I will make another csv file with their details having

username,supervisor,account_number
dwright3,Creary,327531**********
gliu3,Miller,330198**********

I would like something that takes the user login time and adds each individuals account_number and supervisor, plus what instrument/workstation the time was on (different rates for different instruments). This can probably be accomplished by setting the first variable ($1=Bruker_600) or something.

Output must be like this with a header then usage charges:

Primary Comments,Customer Account Number,Transaction Date,Service Description,Quantity,Unit,Price,Service Category,PI's Name,Purchaser's Last Name,Short Contributing Center Name,Resource Name,Line Item Assistant,Line Item Comments
600 NMR,201895**********,07/31/13,600 MHz,34.97,Hour,6,600 MHz,Ash,Ash,"""""",600 NMR,"""""",""""""
600 NMR,327531**********,07/31/13,600 MHz,25.25,Hour,6,600 MHz,Creary,Creary,"""""",600 NMR,"""""",""""""
600 NMR,261485**********,07/31/13,600 MHz,33.55,Hour,6,600 MHz,Helquist,Helquist,"""""",600 NMR,"""""",""""""
600 NMR,330198**********,07/31/13,600 MHz,11.48,Hour,6,600 MHz,Miller,Miller,"""""",600 NMR,"""""",""""""

---------- Post updated at 12:57 PM ---------- Previous update was at 12:55 PM ----------

I should add, this only needs to be done once a month. The perl script will run at 0001 on the 1st of the month for last months login data, eg wtmp.1

4

I still can't see how to get from one to the other. Where is dwright3's 272 ? Where do the other data come from? Are they constants?

5

Yes, every month the perl script is run outputting

dwright3 272
esoto1 52
ewhite5 36

Then I need to combine that data with account information (doesnt change, constant)-

dwright3,Creary,327531**********
etc
etc

to finally get an output.csv that I can upload to our billing software like this

Primary Comments,Customer Account Number,Transaction Date,Service Description,Quantity,Unit,Price,Service Category,PI's Name,Purchaser's Last Name,Short Contributing Center Name,Resource Name,Line Item Assistant,Line Item Comments
600 NMR,201895**********,07/31/13,600 MHz,34.97,Hour,6,600 MHz,Ash,Ash,"""""",600 NMR,"""""",""""""

---------- Post updated at 01:22 PM ---------- Previous update was at 01:14 PM ----------

OHHH!! Maybe the output file is confusing, since it's not representative of the data given.

A sample of the output for dwright3 would be

600 NMR,327531**********,07/31/13,600 MHz,4.53,Hour,6,600 MHz,Creary,dwright3 ,"""""",600 NMR,"""""",""""""
6

Again, what about dwright3's 272 coming from your perl script?

7 
username  time_in_minutes
dwright3 272
esoto1 52
ewhite5 36

The time gets put into the Quantity field of the output.csv

8

Pls adapt to your exact needs:

awk     'NR==1          {print "Primary Comments,Customer Account Number,Transaction Date,Service Description,Quantity,U..."}
         FNR==NR        {S[$1]=$2; P[$1]=$3; next}
         $1 in S        {print "600...",P[$1],date,"600 MHz",$2/60,S[$1],$1,"..." }
        ' FS="," OFS="," OFMT="%.2f" date="07/31/13" file2 FS=" " file1
Primary Comments,Customer Account Number,Transaction Date,Service Description,Quantity,U...
600...,327531**********,07/31/13,600 MHz,4.53,Creary,dwright3,...
600...,330198**********,07/31/13,600 MHz,5.45,Miller,gliu3,...
1 Like
9

Thanks for this. Can I ask how do I tell awk what files to process. I think its something in here

{S[$1]=$2; P[$1]=$3; next}

are those variable's that I assign to my perl.csv and account.csv?

---------- Post updated at 03:14 PM ---------- Previous update was at 02:27 PM ----------

Nevermind, I guess I put them in here

file2 FS=" " file1

---------- Post updated at 03:50 PM ---------- Previous update was at 03:14 PM ----------

Thanks! It works great! :smiley: