Support Questions

Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

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

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

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

View solution in original post

1 REPLY 1

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.