Nifi: How to split , convert and concat dynamic String to an insert into statement ?

[#1]

Hi,

i have the following input single row FlowFiles:

Aug 9 10:34:12 sysxyz-p2 sudo: pam_unix(sudo:session): session opened for user root by (uid=0)
Aug 10 10:34:12 sysxxx.xxx.xx sudo: pam_unix(sudo:session): session opened for user root by (uid=0)
Aug 11 10:34:12 sysxxx.xxx.xx sudo: pam_unix(sudo:session): session closed for user root
Aug 12 10:34:12 sysxxx.xxx.xyz sudo: pam_unix(sudo:session): session closed for user root
Aug 13 10:34:01 sysxxx.xxx.xx CRON[26312]: pam_unix(cron:session): session opened for user monadmin by (uid=0)

1. Split the Flow into timestamp, host, message fields:

timestamp host message

Aug 9 10:34:12 sysxyz-p2 sudo: pam_unix(sudo:session): session opened for user root by (uid=0)
Aug 10 10:34:12 sysxxx.xxx.xx sudo: pam_unix(sudo:session): session opened for user root by (uid=0)
Aug 11 10:34:12 sysxxx.xxx.xx sudo: pam_unix(sudo:session): session closed for user root
Aug 12 10:34:12 sysxxx.xxx.xyz sudo: pam_unix(sudo:session): session closed for user root
Aug 13 10:34:01 sysxxx.xxx.xx CRON[26312]: pam_unix(cron:session): session opened for user monadmin by (uid=0)

2. Convert timestamp Format:

Aug 9 10:34:12 - > 'YYYY.MM.DD HH:MI:SS'

3. Concat the extracted files to insert into ... string:

example:

insert into table ('2017-08-09 10:34:12', 'sysxyz-p2', 'sudo: pam_unix(sudo:session): session opened for user root by (uid=0)';

Thanks for you help!
Timo

[#2 · Accepted solution]

Got the solution:

1. Replace String with actual year

search value: <(.*?)>
Replacement Value: ${now():format('yyyy ')}

2. Extract Timestamp and Message
time = (^.{20})
message = (^.*$)

3. Replace String with insert into clause:
search value: (?s)(^.*$)
Replacement Value: insert into syslog values ('${time:substring(0,20):toDate('yyyy MMM dd HH:mm:ss'):format('yyyy-MM-dd HH:mm:ss')}','${message:substring(21):substringBefore(' ')}','${message:substring(21):substringAfter(' ')}');