Parse JSON log file with JSON Path node

I have a JSON logfile from an MS Exchange server that I need to convert to a table so that I do some analytics.

Exchange_AuditLogs_2020-03-30.json (12.3 KB)

When I read the file with JSON reader node, instead of being multiple rows, it is all lumped together as a single JSON string.

Does anyone have any idea how I would go about splitting this up into individual rows?

Thanks,

tC/.

image

2 Likes

Hi … and thanks for the reply but I tried that and could not get it to work. Could you attach an export of the working KNIME flow?

tC/.

Hi @TigerCole,

As @umutcankurt has suggested, you can parse the JSON file with the JSON Path node checking “List” option for attributes with multiple instances and then use the Ungroup node to split the list into rows.
To parse JSON, select an instance of the value you want to extract then right click and select “Add JSONPath”. And then replace the counter in the path with * character. For example, the path below extract IDs:
$.*['targetResources']*['id']

:blush:

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.