pass arguments to the date command in LHS of sed

Question

I need to convert a date string from the form: "Jan 07, 2016 12:12:12 EDT" (from a nessus report) to a simple 2016-01-07. I have the match logic solved in sed (and python and awk) and the conversion logic also solved (outside of sed) with the use of the date command: date "Jan 07, 2016 12:12:12 EDT" +%Y-%m-%d

I am using match groups to capture the month, day and year so I can refer to them in back references. I just cannot seem to find the correct combination of quotes to get the data command to recognize the arguments in the RHS of a sed match, e.g.:

sed -E "s/(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s[0-9]{1,2}[,].[0-9]{1,4}.?[0-9]{1,2}.[0-9]{1,2}.[0-9]{1,2}.\w{3})/`date -d "\1\2" "+%Y-%m-%d"`/g" 20170917.csv

Note using any quoting, results in date ignoring the first argument and printing today's date according to the second argument.

I have iterated through using ",',\",\' to surround the backrefs \1 and \2 but it seems that as soon as they are enclosed in back ticks, `` to allow the date command to be executed, they are ignored.

Input is csv from Tenable Nessus report.

head tmp/20180121.csv

(1) "Plugin","Plugin Name","Family","Severity","IP Address","Protocol","Port","Exploit?","Repository","MAC Address","DNS Name","NetBIOS Name","Plugin Text","First Discovered","Last Observed","Mitigated On","Exploit Frameworks"

(2) "73571","Oracle Java SE Multiple Vulnerabilities (April 2014 CPU) (Unix)","Misc.","Critical","10.140.162.132","TCP","0","Yes","Individual Scan","00:50:56:c0:00:01","host.example.com","","Plugin Output: The following vulnerable instance of Java is installed on the remote host :

Path : /opt/Geneious_linux64_7_1_9_with_jre/ Installed version : 1.7.0_51 Fixed version : 1.5.0_65 / 1.6.0_75 / 1.7.0_55 / 1.8.0_5","Jan 21, 2018 22:14:50 EST","Jan 21, 2018 22:14:50 EST","",""

Each numbered line is a record -- (1) is the header, and (2) is representative of the remainder.

The Date strings to be converted are in the last line of row 2. I need this be simply: 2018-01-21 in both cases.

Answer 1

A common way to do it is to add a translation table and use back-references:

echo 'Jan 07, 2016 12:12:12 EDT' | sed -E '
  s/$/Jan01Feb02Mar03Apr04May05Jun06Jul07Aug08Sep09Oct10Nov11Dec12/
  s/^(...) (..), (....).*\1(..).*/\3-\2-\4/'

If you wanted to have sed run date to translate the date, you'd have to use a GNU extension: the e flag of the s command (and also the GNU date extension -d).

echo 'Jan 07, 2016 12:12:12 EDT' | sed -E '
  s/^([[:alpha:]]{3} [[:digit:]]{2}, [[:digit:]]{4}).*$/date -d "\1" +%F/e'

Though than means running a shell and date for each line of input, which is pretty inefficient (and also dangerous as we're having sh interpret code that is dynamically generated based on input which we don't know whether we can trust or not). You might as well use a text processor that can do the translation by itself like perl, if you don't want to implement it in sed.