logging qstat (DIRRTTY)

logging.schema

CREATE TABLE log(
    time TEXT TEXT,    
    account TEXT TEXT,    
    env_list TEXT,    
    gid TEXT,    
    grp TEXT,    
    job_name TEXT,    
    job_number TEXT,    
    jobshare TEXT,    
    mail_list TEXT,    
    notify TEXT,    
    owner TEXT,    
    sge_o_home TEXT,    
    sge_o_host TEXT,    
    sge_o_log_name TEXT,    
    sge_o_path TEXT,    
    sge_o_shell TEXT,    
    sge_o_workdir TEXT,    
    submission_time TEXT,    
    uid TEXT,    
    usage_1 TEXT,    
    exec_file TEXT,    
    script_file TEXT,    
    cwd TEXT,    
    stdout_path_list TEXT,    
    job_args TEXT,    
    stderr_path_list TEXT,    
    hard_resource_list
);

logging.sh

#!/bin/bash -e
if [[ ! -e logging.db ]];
then
    sqlite3 logging.db < logging.schema
fi

for j in `qstat -u "*" | awk '$5 ~ /^r$/ {print $1}'`
do
  echo $j
  qstat -j $j | grep -v ^= | sed -n '1,/^scheduling/p' | head -n -1| sed -e 's/:[ ]*/,/' | awk -F, '{gsub(" +", "_", $1); print $1","$2}' | awk -F, '{gsub("^group$", "grp", $1); print $1","$2}' | tr '"' '.' | awk -F, -v date="`date`" '
BEGIN {
    Sfields="account,env_list,gid,grp,job_name,job_number,jobshare,mail_list,notify,owner,sge_o_home,sge_o_host,sge_o_log_name,sge_o_path,sge_o_shell,sge_o_workdir,submission_time,uid,usage_1,exec_file,script_file,cwd,stdout_path_list,job_args,stderr_path_list,hard_resource_list"
    nf=split(Sfields, fields, ",")
    tmp["time"]=date
    for (f=1; f<=nf; f++)
        tmp[fields[f]]=""
} {
    for (f=1; f<=nf; f++)
        if ($1==fields[f]) tmp[fields[f]]=$2
} END {
    printf "INSERT INTO log values(\"%s\"",tmp["time"]
    for (f=1; f<=nf; f++)
        printf ", \"%s\"",tmp[fields[f]]
    print ");"
}' | sqlite3 logging.db

done