Parse file in Pattern Designer, strange issues - help

[Henrik Jutterst]

I'm trying to read the output from a file created from Horizontal Discovery on Oracle Catalogs.

I have a script that's creating a temporary file on the host. The file contains two rows. The file can look like this:

HOST_NAME#DB_NAME#INSTANCE_NAME#DB_STATUS#STANDBY_HOST_NAME#dbversion#db_size_gb#dg_status#is_rac#partition_used
aa42pdb101#AOBP#AOBP#PRIMARY#aa42pdb101#19.15.0.0.0#3535.49#true#false#true

I then have this step where I read the input file:

This Works fine if all fields have data in them. But the issue is below:

Issue:

When I use Delimited Text parsing and if a field on the second row is empty, the delimiter does not work. I can see that in the log.

2022-09-05 17:12:41: Command result:
HOST_NAME#DB_NAME#INSTANCE_NAME#DB_STATUS#STANDBY_HOST_NAME#dbversion#db_size_gb#dg_status#is_rac#partition_used
aa42pdb201#AOBP#AOBP#PHYSICAL_STANDBY#aa42pdb101###true##

2022-09-05 17:12:41: setAttribute(file_input,[{schema=AOBP, size=, dataguard=, rac=, partitioning=, version=true, forEach=/tmp/AOBP.txt}])

Since some values are blank, and I guess Pattern Designer can't handle ### as 3 separators. Then the version get set to true, where it should be blank instead... 

Ok... So can I use Regular Expression instead then?

I Define Parsing to use Regular expression instead and I use this filter:

\n\w+?\#\w+?\#(\w+)?\#\w+?\#\w+?\#(.+)?\#(.+)?\#(\w+)?\#(\w+)?\#(\w+)?

I validate in in regex101 webpage and it looks good:

But when I look at the log the data looks empty 😞

2022-09-05 16:20:48: Command result:
HOST_NAME#DB_NAME#INSTANCE_NAME#DB_STATUS#STANDBY_HOST_NAME#dbversion#db_size_gb#dg_status#is_rac#partition_used
aa42pdb201#AOBP#AOBP#PHYSICAL_STANDBY#aa42pdb101###true##
2022-09-05 16:20:48: Execution time: 4807 ms
Transform Table
2022-09-05 16:20:48: setAttribute(cmdb_ci_db_ora_catalog,[])
2022-09-05 16:20:48: Execution time: 0 ms

I don't understand what /why the values are not there.
But I guess it has to do with the RegEx in ServiceNow OR Transform Below:

Anyone who can point me in the right direction..?

[Henrik Jutterst · Accepted solution]

Ok.. Maybe this was a tough one to solve since I didn't get any feedback on the post, but I had to solve the issue anyway.

I can now say that the issue is solved, but not the way that I was hoping. Yes, I used RegEx to filter out my issue. It seemed like the RegEx that I used worked on RegEx101-website, but not in ServiceNow. So after some feedback from a collegue I did debugging on the file and tried to build up a RegEx from step by step and make sure that the right events was captured.

But since I never got the linebreak (new line) to trigger/work I was able to modify the output-file and exclude the header. And by adding the RegEx below I got the desired match:

.*?\#.*?\#(.*?)\#.*?\#.*?\#(.*?)\#(.*?)\#(.*?)\#(.*?)\#(.*)

If anyone have good experience with RegEx in Pattern Designer and know of a better solution, please update the case and I can test it and update the answer if so.

But for now I concider this post "solved".