0

I need your expert advice to get the below output

INPUT

2020-01-09 15:39:39,sharm,07296,97  
2020-01-09 15:40:40,SRI,6657,197  
2020-01-09 15:42:40,rinku,null,0  
2020-01-09 15:42:40,SVRMO,8588,39  
2020-01-09 15:44:58,MASTA,093720,48  

here, fetch the last 5 min data. Assuming the command is run at 15:45, data from (15:40-15:45) should be selected.

2020-01-09 15:40:40,SRI,6657,197  
2020-01-09 15:42:40,rinku,null,0  
2020-01-09 15:42:40,SVRMO,8588,39  
2020-01-09 15:44:58,MASTA,093720,48

The output should be

284,4,3   

Where

  • 284 show sum of last row data (197+0+39+48)
  • 4 show total count of data in last 5 min
  • 3 show total count of data where last column value is not 0 (in the example: lines 1,3, and 4 where the value is 197,39, and 48, respectively)
  • 2
    What have you done on your own so far? Where exactly are you stuck? – Panki Jan 9 at 8:15
1

Below awk command first getting current & past 5minutes ago dates and convert them to epoch time (Unix time) and set in startTime and endTime respectively; and then for every line we read it also converts first field date column into epoch as well and checking if time is between startTime&endTime then do calculations required. In the END we print the last value of sum, total and nonZero variables calculated.

awk -vstartTime="$(date -d'-5 minutes' '+%s')" \
    -vendTime="$(date '+%s')" \
    -F, '{ epoch="date -d\"" $1 "\" +%s"; epoch |getline timestamp; }
         (timestamp>=startTime && timestamp<=endTime){ sum+=$NF; total++; if($NF!=0)nonZero++ }
END{ print sum, total, nonZero }' OFS=, infile

manually test on test data given:

awk -vstartTime=1578571800 \
    -vendTime=1578572100 \
    -F, '{ epoch="date -d\"" $1 "\" +%s"; epoch |getline timestamp; }
         (timestamp>=startTime && timestamp<=endTime){ sum+=$NF; total++; if($NF!=0)nonZero++ }
END{ print sum, total, nonZero }' OFS=, infile
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.