topic Re: Is it possible to transform JSON events to be independent, then store them in another index? in Getting Data In

Is it possible to transform JSON events to be independent, then store them in another index?

splunksumman — Fri, 31 May 2019 20:19:12 GMT

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?

Re: Is it possible to transform JSON events to be independent, then store them in another index?

siddharthkhatsu — Sat, 01 Jun 2019 11:36:55 GMT

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.

Re: Is it possible to transform JSON events to be independent, then store them in another index?

splunksumman — Sat, 01 Jun 2019 15:47:19 GMT

Hi @siddharthkhatsuriya thank for you answer,

Is it possible how are the names of the fields to build a dataset similar to this example?

What I'm looking for is being able to answer questions like:

Top of tasks
duration of tasks
etc..

Thank you.

Re: Is it possible to transform JSON events to be independent, then store them in another index?

kamlesh_vaghela — Fri, 07 Jun 2019 11:05:54 GMT

@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.