topic Re: How to split a json message into multiple events? in Getting Data In

How to split a json message into multiple events?

mariobisio — Tue, 09 Mar 2021 09:05:47 GMT

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

Re: How to split a json message into multiple events?

bowesmana — Tue, 09 Mar 2021 10:47:15 GMT

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

Re: How to split a json message into multiple events?

mariobisio — Tue, 09 Mar 2021 17:46:33 GMT

Hi bowesmana,

thanks for you reply.

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

Thanks

Mario

Re: How to split a json message into multiple events?

kamlesh_vaghela — Thu, 11 Mar 2021 08:11:44 GMT

@mariobisio

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

Re: How to split a json message into multiple events?

mariobisio — Thu, 11 Mar 2021 10:22:34 GMT

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

Re: How to split a json message into multiple events?

kamlesh_vaghela — Fri, 12 Mar 2021 16:20:52 GMT

@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

Re: How to split a json message into multiple events?

mariobisio — Tue, 16 Mar 2021 08:28:57 GMT

Hi kamlesh_vaghela,

It works!!

thanks you very much for your help.

Regards

Mario