How to split a json message into multiple events?

[mariobisio]

Hi Guys,

could anyone help me to split the following json file in multiple events?

I tried in different ways, adding KVMODE=json, modifying LINE_BREAKER or adding EVENT_BREAKER to my propos.conf, but I'm unable to find a solution...

Following a json example,

What I'm trying is to capture the messages between square brackets and the split the events contained.

In this example I have two differents events starting with "eventId" field and eneding with "policyId"field

{
"name": "SecureSphere_Audit_PCI_-_Login_audit_15.01.2021_1043_19.02.2021_2359_ith-aru-sec-imp-gw03_0_mxName.0000000002",
"messageRaw": [{
"eventId": "6930995712914260054",
"eventCreationTime": "2021-02-19T17:04:32Z",
"streamId": "20",
"sourcePort": 2978,
"destinationPort": 1527,
"originalUserName": "sapserviceid6",
"parsedQuery": "N/A (login)",
"logCollectorName": "N/A",
"realDateTime": "2021-02-19T17:04:31Z",
"base": {
"keysCrc": "3392074420543545270",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapserviceid6",
"sqlSourceGroup": "Default oracle group",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapserviceid6",
"gatewayName": "ith-aru-sec-imp-gw03",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"policy": "PCI - Login audit",
"policyId": "993812781714235096"
}, {
"eventId": "6930995712914335615",
"eventCreationTime": "2021-02-19T17:04:41Z",
"streamId": "30",
"sourcePort": 2978,
"destinationPort": 1527,
"originalUserName": "sapid6",
"parsedQuery": "N/A (login)",
"logCollectorName": "N/A",
"realDateTime": "2021-02-19T17:04:41Z",
"base": {
"keysCrc": "-4699307483851221009",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"sqlSourceGroup": "Default oracle group",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw03",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"policy": "PCI - Login audit",
"policyId": "993812781714235096"
}]
}

Thanks in advance for your help

Mario

[kamlesh_vaghela · Accepted solution]

@mariobisio 

Can you please try below configuration?

[YOUR_SOURCETYPE]
SHOULD_LINEMERGE=0
LINE_BREAKER=\}(,\s+)\{
NO_BINARY_CHECK=true
SEDCMD-remove_header=s/.*messageAgg\":\s\[//g
SEDCMD-remove_footer=s/\]\}//g

[mariobisio]

Hi kamlesh_vaghela,

It works!!

thanks you  very much for your help.

Regards

Mario

[kamlesh_vaghela]

@mariobisio 

Can you please share your exact _raw data? So I can help you on this with props.con..

[mariobisio]

Hi kamlesh_vaghela,

thanks for your help.

Following the entire _raw log.

{
"name": "SecureSphere_Audit_PCI_-_Login_and_logout_audit_15.01.2021_1043_19.02.2021_1639_ith-aru-sec-imp-gw02_0_mxName.0000000001",
"messageAgg": [{
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "40",
"responseTimeSum": "11",
"base": {
"keysCrc": "-8551114388220623619",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapserviceid6",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapserviceid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "80",
"base": {
"keysCrc": "6526603515572082956",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGOUT",
"operation": "Logout",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "1",
"responseTimeSum": "1742",
"base": {
"keysCrc": "-8163044881711936885",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time1to10",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "39",
"responseTimeSum": "8",
"base": {
"keysCrc": "-204053942017404474",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "3",
"base": {
"keysCrc": "5464671818046985164",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapsr3db",
"sourceIp": "10.1.5.190",
"osUser": "sapserviceep7",
"host": "sap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "ep7",
"schema": "sapsr3db",
"isExceptionOccurred": true,
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "2",
"base": {
"keysCrc": "296121360254800243",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"sqlSourceGroup": "Default oracle group",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGOUT",
"operation": "Logout",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "22",
"responseTimeSum": "10",
"base": {
"keysCrc": "-7569040835949211912",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapserviceid6",
"sqlSourceGroup": "Default oracle group",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapserviceid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "44",
"base": {
"keysCrc": "-2959819095772425042",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"sqlSourceGroup": "Default oracle group",
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGOUT",
"operation": "Logout",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}, {
"timeSlot": "2021-02-19T16:37:58Z",
"hits": "22",
"responseTimeSum": "14",
"base": {
"keysCrc": "517624223826118305",
"serverGroup": "LAB",
"service": "Oracle",
"application": "Default Oracle Application",
"eventSourceType": "Network",
"userType": "Valid",
"dbUser": "sapid6",
"sqlSourceGroup": "Default oracle group",
"isUserAuthenticed": true,
"sourceIp": "10.1.5.190",
"sourceApp": "disp+work.exe",
"osUser": "sapserviceid6",
"host": "sapysap1",
"serviceType": "Oracle",
"destinationIp": "10.1.5.191",
"eventType": "LOGIN",
"operation": "Login",
"database": "id6",
"schema": "sapid6",
"gatewayName": "ith-aru-sec-imp-gw02",
"sourceOfActivity": "REMOTE",
"dbInstance": "id6"
},
"responseSizeBucket": "Size0",
"affectedRowsBucket": "Size0",
"responseTimeBucket": "Time0to1",
"destinationPort": "1527",
"policy": "PCI - Login and logout audit",
"policyId": "993812025799991000"
}]
}

The file contains SecureSphere sample audit logs

Thanks again

Regards

Mario

[bowesmana]

This should work - here's a run anywere example with your example data

| makeresults
| eval _raw="{
\"name\": \"SecureSphere_Audit_PCI_-_Login_audit_15.01.2021_1043_19.02.2021_2359_ith-aru-sec-imp-gw03_0_mxName.0000000002\",
\"messageRaw\": [{
\"eventId\": \"6930995712914260054\",
\"eventCreationTime\": \"2021-02-19T17:04:32Z\",
\"streamId\": \"20\",
\"sourcePort\": 2978,
\"destinationPort\": 1527,
\"originalUserName\": \"sapserviceid6\",
\"parsedQuery\": \"N/A (login)\",
\"logCollectorName\": \"N/A\",
\"realDateTime\": \"2021-02-19T17:04:31Z\",
\"base\": {
\"keysCrc\": \"3392074420543545270\",
\"serverGroup\": \"LAB\",
\"service\": \"Oracle\",
\"application\": \"Default Oracle Application\",
\"eventSourceType\": \"Network\",
\"userType\": \"Valid\",
\"dbUser\": \"sapserviceid6\",
\"sqlSourceGroup\": \"Default oracle group\",
\"isUserAuthenticed\": true,
\"sourceIp\": \"10.1.5.190\",
\"sourceApp\": \"disp+work.exe\",
\"osUser\": \"sapserviceid6\",
\"host\": \"sapysap1\",
\"serviceType\": \"Oracle\",
\"destinationIp\": \"10.1.5.191\",
\"eventType\": \"LOGIN\",
\"operation\": \"Login\",
\"database\": \"id6\",
\"schema\": \"sapserviceid6\",
\"gatewayName\": \"ith-aru-sec-imp-gw03\",
\"sourceOfActivity\": \"REMOTE\",
\"dbInstance\": \"id6\"
},
\"policy\": \"PCI - Login audit\",
\"policyId\": \"993812781714235096\"
}, {
\"eventId\": \"6930995712914335615\",
\"eventCreationTime\": \"2021-02-19T17:04:41Z\",
\"streamId\": \"30\",
\"sourcePort\": 2978,
\"destinationPort\": 1527,
\"originalUserName\": \"sapid6\",
\"parsedQuery\": \"N/A (login)\",
\"logCollectorName\": \"N/A\",
\"realDateTime\": \"2021-02-19T17:04:41Z\",
\"base\": {
\"keysCrc\": \"-4699307483851221009\",
\"serverGroup\": \"LAB\",
\"service\": \"Oracle\",
\"application\": \"Default Oracle Application\",
\"eventSourceType\": \"Network\",
\"userType\": \"Valid\",
\"dbUser\": \"sapid6\",
\"sqlSourceGroup\": \"Default oracle group\",
\"isUserAuthenticed\": true,
\"sourceIp\": \"10.1.5.190\",
\"sourceApp\": \"disp+work.exe\",
\"osUser\": \"sapserviceid6\",
\"host\": \"sapysap1\",
\"serviceType\": \"Oracle\",
\"destinationIp\": \"10.1.5.191\",
\"eventType\": \"LOGIN\",
\"operation\": \"Login\",
\"database\": \"id6\",
\"schema\": \"sapid6\",
\"gatewayName\": \"ith-aru-sec-imp-gw03\",
\"sourceOfActivity\": \"REMOTE\",
\"dbInstance\": \"id6\"
},
\"policy\": \"PCI - Login audit\",
\"policyId\": \"993812781714235096\"
}]
}"
| spath input=_raw messageRaw{} output=messageRaw
| fields - _raw _time
| mvexpand messageRaw
| spath input=messageRaw

Last 4 lines do the work

[mariobisio]

Hi bowesmana,

thanks for you reply.

I'm looking for some props configuration to parse correctly the json files.

Thanks

Mario