Hello community, once again, I need your help.
I have a training source in json format in which transactions with 50 different tasks are registered, something like this:
{
"NoTramite": "123456",
"task": {
"A": {
"FechaAsignacion": "01-10-2018",
"FechaGestion": "23-10-2018",
"UsuarioGestion": "PEDRO",
"Observacion": ""
},
"B": {
"FechaAsignacion": "01-11-2018",
"FechaGestion": "01-11-2018",
"UsuarioGestion": "CARLOS",
"Observacion": ""
},
"C": {
"FechaAsignacion": "01-12-2018",
"FechaGestion": "10-12-2018",
"UsuarioGestion": "MARIO",
"Observacion": ""
}
}
}
each event of a transaction has tasks A, B, C ... n up to 50 tasks
indexing them in Splunk the name of the field is like this:
task.A.DateAsignation
task.A.FechaGestion
task.A.UsuarioGestion
task.A.Observation
task.B.FechaAsignacion
task.B.FechaGestion
task.B.UsuarioGestion
task.B.Observation
I know that the ideal is to have an event for each task assigned to the number of the transaction.
Is it possible to transform these events to be independent and then store them in another index?
What would be the best recommendation to handle this type of event?
@splunksumman
Can you please below search?
YOUR_SEARCH | rex field=_raw "\"task\":\s{(?<task>.*)}}$"
| rex max_match=0 mode=sed field=task "s/\"},\"/\"}|\"/g"
| eval task=split(task,"|")
| mvexpand task
| eval _raw=task
| table _raw | rex field=_raw "\"(?<PArea>.*)\":\s\{\"FechaAsignacion\":\s\"(?<FechaAsignacion>.*)\",\"FechaGestion\":\s\"(?<FechaGestion>.*)\",\"UsuarioGestion\":\s\"(?<UsuarioGestion>.*)\",\"Observacion\":\s\"(?<Observacion>.*)\"\}" | table PArea FechaAsignacion FechaGestion UsuarioGestion Observacion
My Sample Search:
| makeresults
| eval _raw="{\"NoTramite\": \"123456\",\"task\": {\"A\": {\"FechaAsignacion\": \"01-10-2018\",\"FechaGestion\": \"23-10-2018\",\"UsuarioGestion\": \"PEDRO\",\"Observacion\": \"\"},\"B\": {\"FechaAsignacion\": \"01-11-2018\",\"FechaGestion\": \"01-11-2018\",\"UsuarioGestion\": \"CARLOS\",\"Observacion\": \"\"},\"C\": {\"FechaAsignacion\": \"01-12-2018\",\"FechaGestion\": \"10-12-2018\",\"UsuarioGestion\": \"MARIO\",\"Observacion\": \"\"},\"D\": {\"FechaAsignacion\": \"01-12-2018\",\"FechaGestion\": \"10-12-2018\",\"UsuarioGestion\": \"MARIO1\",\"Observacion\": \"\"}}}"
| rex field=_raw "\"task\":\s{(?<task>.*)}}$"
| rex max_match=0 mode=sed field=task "s/\"},\"/\"}|\"/g"
| eval task=split(task,"|")
| mvexpand task
| eval _raw=task
| table _raw | rex field=_raw "\"(?<PArea>.*)\":\s\{\"FechaAsignacion\":\s\"(?<FechaAsignacion>.*)\",\"FechaGestion\":\s\"(?<FechaGestion>.*)\",\"UsuarioGestion\":\s\"(?<UsuarioGestion>.*)\",\"Observacion\":\s\"(?<Observacion>.*)\"\}" | table PArea FechaAsignacion FechaGestion UsuarioGestion Observacion
Note: The regex might require changes in case of variation of JSON pattern.
@splunksumman
Can you please below search?
YOUR_SEARCH | rex field=_raw "\"task\":\s{(?<task>.*)}}$"
| rex max_match=0 mode=sed field=task "s/\"},\"/\"}|\"/g"
| eval task=split(task,"|")
| mvexpand task
| eval _raw=task
| table _raw | rex field=_raw "\"(?<PArea>.*)\":\s\{\"FechaAsignacion\":\s\"(?<FechaAsignacion>.*)\",\"FechaGestion\":\s\"(?<FechaGestion>.*)\",\"UsuarioGestion\":\s\"(?<UsuarioGestion>.*)\",\"Observacion\":\s\"(?<Observacion>.*)\"\}" | table PArea FechaAsignacion FechaGestion UsuarioGestion Observacion
My Sample Search:
| makeresults
| eval _raw="{\"NoTramite\": \"123456\",\"task\": {\"A\": {\"FechaAsignacion\": \"01-10-2018\",\"FechaGestion\": \"23-10-2018\",\"UsuarioGestion\": \"PEDRO\",\"Observacion\": \"\"},\"B\": {\"FechaAsignacion\": \"01-11-2018\",\"FechaGestion\": \"01-11-2018\",\"UsuarioGestion\": \"CARLOS\",\"Observacion\": \"\"},\"C\": {\"FechaAsignacion\": \"01-12-2018\",\"FechaGestion\": \"10-12-2018\",\"UsuarioGestion\": \"MARIO\",\"Observacion\": \"\"},\"D\": {\"FechaAsignacion\": \"01-12-2018\",\"FechaGestion\": \"10-12-2018\",\"UsuarioGestion\": \"MARIO1\",\"Observacion\": \"\"}}}"
| rex field=_raw "\"task\":\s{(?<task>.*)}}$"
| rex max_match=0 mode=sed field=task "s/\"},\"/\"}|\"/g"
| eval task=split(task,"|")
| mvexpand task
| eval _raw=task
| table _raw | rex field=_raw "\"(?<PArea>.*)\":\s\{\"FechaAsignacion\":\s\"(?<FechaAsignacion>.*)\",\"FechaGestion\":\s\"(?<FechaGestion>.*)\",\"UsuarioGestion\":\s\"(?<UsuarioGestion>.*)\",\"Observacion\":\s\"(?<Observacion>.*)\"\}" | table PArea FechaAsignacion FechaGestion UsuarioGestion Observacion
Note: The regex might require changes in case of variation of JSON pattern.
Hey, I have found a similar issue with a proper answer
https://answers.splunk.com/answers/376095/how-can-i-split-a-json-array-in-mutiple-events.html
Hope this helps.