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

timo_burmeister — Mon, 14 Aug 2017 21:00:32 GMT

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

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

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

timo_burmeister — Wed, 16 Aug 2017 16:48:39 GMT

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(' ')}');

question Re: Nifi: How to split , convert and concat dynamic String to an insert into statement ? in Archives of Support Questions (Read Only)

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

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