Getting Data In
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
Ask a Question
Solved! Jump to solution

How to split a json message into multiple events?

mariobisio
Explorer
‎03-09-2021 01:05 AM

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

Labels (1)
Labels
Tags (1)
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust
‎03-12-2021 08:20 AM

@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

View solution in original post

0 Karma
Reply
Solved! Jump to solution
Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust
‎03-12-2021 08:20 AM

@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
0 Karma
Reply
Solved! Jump to solution

mariobisio
Explorer
‎03-16-2021 01:28 AM

Hi kamlesh_vaghela,

It works!!

thanks you  very much for your help.

Regards

Mario

0 Karma
Reply
Solved! Jump to solution

kamlesh_vaghela
SplunkTrust
SplunkTrust
‎03-11-2021 12:11 AM

@mariobisio 

 

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

0 Karma
Reply
Solved! Jump to solution

mariobisio
Explorer
‎03-11-2021 02:22 AM

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

Tags (1)
0 Karma
Reply
Solved! Jump to solution

bowesmana
SplunkTrust
SplunkTrust
‎03-09-2021 02:47 AM

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

 

0 Karma
Reply
Solved! Jump to solution

mariobisio
Explorer
‎03-09-2021 09:46 AM

Hi bowesmana,

thanks for you reply.

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

Thanks

Mario

0 Karma
Reply
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Persistent Queue at TcpOut — One of Splunk's Most Practical Features

Splunk introduced persistent queueing at the tcpout layer as one of the most practical resilience features in ...

Skip the Awkward Silence: Have a .conf-ersation at .conf26

Picture this. You arrive at .conf26 already having your socializing and networking plans mapped out. No ...

Rethinking Zero Trust: From Product Purchases to Logical Control Evidence

Implementing Zero Trust (ZT) across complex environments often falters at the very beginning due to a ...