Shell script (KSH) to list ONLY the ID of male employees whose last loging time was during the last

Sara_84 · February 13, 2012, 6:57am

Can someone help me on my issue please

I have a table like this format:

# cat myfile.txt
 
 Employee  Gender    NAME      Last Login 
    ID                                       Time        
-------------------------------------------------
210125       M         ABC        02/03/2012 08:07 
451235       F         EFG         02/012/2012 16:53  
241567       M         GHI         02/01/2012 14:03  
314584       M         JKL         02/02/2012 12:43

*****************

I need shell script (KSH) to list ONLY the ID of male employees whose last loging time was during the last two days.

the output should be list of IDs whic meet the requirements

methyl · February 13, 2012, 7:14am

Is the second line of your data typed correctly? I think we can guess that the date format is American mm/dd/yyyy but that line is different.

What Operating System and version do you have? Do you have the GNU "date" command?
How do you define "during the last two days"? Do you mean 48 hours from the time it is at the moment you run the script, or is this something to do with the date?

Are you trying to run the script as a one-off for today only or is this a regular requirement?

Sara_84 · February 13, 2012, 7:30am

I'm using AIX 6.1. yes I have the "date" command

the date format is American.

I mean by "during the last two days" from the the moment I run the script.

The purpose of the script to list every IDs of employees who logged in during the last two days.

Thaaanks

durden_tyler · February 13, 2012, 9:32am

If Perl is an option, then -

$
$ cat myfile.txt
 Employee  Gender    NAME      Last Login
    ID                                       Time
-------------------------------------------------
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/02/2012 16:53
241567       M         GHI         02/01/2012 14:03
314584       M         JKL         02/02/2012 12:43
123456       M         MNO         02/12/2012 00:00
654321       F         PQR         02/12/2012 00:00
$
$ perl -lne 'BEGIN {use Time::Local; $prior = time-2*24*60*60}
             if (m|^(\d+)\s+([MF])\s+\w+\s+(\d+)/(\d+)/(\d+)\s+(\d+):(\d+)$|){
               ($id, $g, $mo, $d, $y, $h, $mi) = ($1, $2, $3, $4, $5, $6, $7);
               $y -= 1900; $mo--;
               $ts = timelocal(0,$mi,$h,$d,$mo,$y);
               print $id if $ts > $prior and $g eq "M";
             }' myfile.txt
123456
$
$

tyler_durden

kalpeer · February 14, 2012, 2:32am

please provide me the sample input with more record

Sara_84 · February 14, 2012, 5:13am

Thanks , here is more records of my input..

Employee  Gender    NAME      Last Login
    ID                                       Time
-------------------------------------------------
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/02/2012 16:53
241567       M         GHI         02/01/2012 14:03
314584       M         JKL         02/02/2012 12:43
123456       M         MNO         02/12/2012 00:00
654321       F         PQR         02/12/2012 00:00
210120       M         ZVF         02/03/2012 08:07
451230       F         UNM         02/02/2012 16:53
241560       M         IOP         02/05/2012 14:03
314580       M         UOP         02/04/2012 12:43
123450       M         MNN         02/12/2012 00:00
654320       F         PQP         02/11/2012 00:00

ctsgnb · February 14, 2012, 5:29am

@Sara,

The right way to do it would be to pass an SQL statement directly at database level to extract the information you need and spool it to a file.

Sara_84 · February 14, 2012, 5:42am

I'm not using SQL

kalpeer · February 14, 2012, 6:06am

i done a little modification to my script and display the last two days and today report

#! /bin/bash

#retrieve current date and month
da_date="`date +%d`"
#da_date="2"
da_month=`date "+%m"`
#da_month="02"

 prev_date=0
 prev_mon=0
da_year=`date "+%Y"`
# Check  whether date is above 3
if [ "$da_date" -ge "3" ];then
  da_dat=`expr $da_date - 3`
  da_dat="""$da_dat"
else
 # If date is 2, it display the record of 2,1 and last day of previous month
 if  [ "$da_date" -eq "2" ];then
  da_dat=2
  prev_mon=`expr $da_month - 1`
  prev_date=`cal $prev_mon $da_year | xargs -n1 | tail -1`
  prev_date="""$prev_date"
 else
 # If date is 1, it display the record of 1 and last two days of previous month
   da_dat=1
  prev_mon=`expr $da_month - 1`
  prev_date=`cal $prev_mon $da_year | xargs -n1 | tail -2| head -1`
  prev_date="""$prev_date"

  fi
fi

while read line1
do
line=`echo $line1 |grep -v Employee  |  awk '{if($2=="M") print $4}'`
if [ ! -z "$line" ];then
op_da=`echo $line | awk -F/ '{print $2}'`
op_mo=`echo $line | awk -F/ '{print $1}'`
op_year=`echo $line | awk -F/ '{print $3}'`

#if date is 3 and above following block will be executed
if [ \( \( "$da_dat" -eq "`expr $op_da - 3`" \) -o  \( "$da_dat" -eq "`expr $op_da - 2`" \) -o  \( "$da_dat" -eq "`expr $op_da - 1`" \) \) \
 -a \( "$op_mo" -eq "$da_month" \) -a \( "$op_year" -eq "$da_year" \) -a \( "$prev_date" -eq "0" \)  ];then
 echo $line1 | awk '{print $1}'
 #echo $line1
fi

#if date is 2 or 1 following block will be executed
if [ \( "$da_dat" -lt "3" \) -a \( \
 \( \( "$op_da" -ge "$prev_date" \) -a \( "$op_mo" -eq "$prev_mon" \) -a "$op_year" -eq "$da_year" \) -o \
\( \( "$op_da" -le "$da_dat" \) -a  \( "$op_mo" -eq "$da_month" \) -a \( "$op_year" -eq "$da_year" \) \) \) ];then

 echo $line1 | awk '{print $1}'
 #echo $line1
fi

fi
done < inp6

Input: inp6

Employee  Gender    NAME      Last Login
    ID                                       Time
-------------------------------------------------
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/02/2012 16:53
241567       M         GHI         02/01/2012 14:03
314584       M         JKL         02/02/2012 12:43
123456       M         MNO         02/12/2012 00:00
654321       F         PQR         02/12/2012 00:00
210120       M         ZVF         02/03/2012 08:07
451230       F         UNM         02/02/2012 16:53
241560       M         IOP         02/05/2012 14:03
314580       M         UOP         02/04/2012 12:43
123450       M         MNN         02/12/2012 00:00
654320       F         PQP         02/11/2012 00:00
65430       M         PQP         01/30/2012 00:00
65432       M         PQP         01/31/2012 00:00
245432       M         PQP         01/29/2012 00:00

When i execute the script i got the below output.
Output

Today date is 02/14/2012.
It displayed the record from 02/12/2012 to 02/14/2012.

123456 M MNO 02/12/2012 00:00
123450 M MNN 02/12/2012 00:00

Thanks,
Kalai

ygemici · February 14, 2012, 7:22am

if your os support "date '+%s'" format,you can try this

# cat file1
Employee ID Gender    NAME          Last Login Time
-------------------------------------------------
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/12/2012 16:53
241567       M         GHI         02/01/2012 14:03
314584       M         JKL         02/02/2012 12:43

# ./justdoit file1 2
451235       F         EFG         02/12/2012 16:53

# ./justdoit file1 12
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/12/2012 16:53

# cat justdoit
#!/usr/bin/ksh
## justdoit @ygemici unix.com simple date calculator ##
tmpf='times.tmp.bak1X';file="$1";day=$2
awk '/^[0-9]/{if(!f)f=NR;b[c++]=$(NF-1);split($NF,a,":");aa[x++]=a[1]*60*60+a[2]*60}
END{for(j=0;j<c;j++){system("date '+%s' -d" b[j]);printf "%d %d %s\n", aa[xx++],f++,FILENAME}
}' $file|sed 'N;s/\n/ /'|awk -vtmpf=$tmpf 'BEGIN{printf "">tmpf}{print $1+$2,$3,$4>>tmpf}'
awk -vnow=$(date '+%s') -vt=$day '{if($1<now&&$1>now-t*24*60*60)system("awk NR=="$2"{print} " $NF)}' $tmpf

regards
ygemici

ctsgnb · February 14, 2012, 1:19pm

# DEADLINE contains J-2 in a YYYYMMDDHHMM format

DEADLINE=$(perl -e 'printf "%s\n",scalar(localtime(time - 2*86400 ))' | awk -F"[ :]" 'BEGIN{
split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec",d," ")}{
for(i=0;++i<=12;) if (d==$2) m=i}{printf "%s%02d%s\n",$NF,m,$3 $4 $5}')

# Then build the date in YYYYMMDDHHMM from your file then performs check
# if M and last log within DEADLINE the || NR<4 is to ensure that the header is also displayed

awk -v d="$DEADLINE" '{split($4,l,"/");split($5,t,":")}$2=="M"&&((l[3]l[1]l[2]t[1]t[2])+0>d)||NR<4' yourfile

$ cat tst
Employee  Gender    NAME      Last Login
    ID                                       Time
-------------------------------------------------
210125       M         ABC         02/03/2012 08:07
451235       F         EFG         02/02/2012 16:53
241567       M         GHI         02/01/2012 14:03
314584       M         JKL         02/02/2012 12:43
123456       M         MNO         02/12/2012 23:00
654321       F         PQR         02/12/2012 20:10
210120       M         ZVF         02/03/2012 08:07
451230       F         UNM         02/02/2012 16:53
241560       M         IOP         02/05/2012 14:03
314580       M         UOP         02/04/2012 12:43
123450       M         MNN         02/13/2012 00:00
654320       F         PQP         02/11/2012 00:00
$ date
mardi 14 f�vrier 2012, 19:24:45 (UTC+0100)
$ DEADLINE=$(perl -e 'printf "%s\n",scalar(localtime(time - 2*86400 ))' | awk -F"[ :]" 'BEGIN{
> split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec",d," ")}{
> for(i=0;++i<=12;) if (d==$2) m=i}{printf "%s%02d%s\n",$NF,m,$3 $4 $5}')
$ echo $DEADLINE
201202121924
$ awk -v d="$DEADLINE" '{split($4,l,"/");split($5,t,":")}$2=="M"&&((l[3]l[1]l[2]t[1]t[2])+0>d)||NR<4' tst
Employee  Gender    NAME      Last Login
    ID                                       Time
-------------------------------------------------
123456       M         MNO         02/12/2012 23:00
123450       M         MNN         02/13/2012 00:00

Sara_84 · February 15, 2012, 1:17am

Thaaanks darling :o

the code is working great

But it seems that dealing with dates is a bit hard and confusing. the code is working fine but when I try to understand it and modify it to to run on a different table , it did not work

kalpeer · February 15, 2012, 2:38am

if you want to search for different date u can modify the below items

Step 1:
Comment the below line and the new line
#da_date="`date +%d`"
da_date="2" # provide the date u need to search
Step 2:
#da_month=`date "+%m"`
da_month="02" # provide the month u need to search

Thanks,
Kalai

Sara_84 · February 15, 2012, 6:11am

Many thanks for your help :o