How to transform event data for table display?

[dzyfer]

Hi, I would like display values of variables from an event as a Table. 

My data format is as follow:

Time	Event
9/16/22 10:10:10.000 AM	index=* sourcetype=* type=* "Name1" : "A", "Name2" : "B", "Name3" : "C", ... "Name10" : "J", "Var1" : 10, "Var2" : 10, "Var3" : 25, ... "Var10" : 50

I would like the search data to be transformed into a table formatted like this, internalizing the field names Name*, Var* and replacing the column headers with new names as shown below.

Station	Value
A	10
B	10
C	25
...	...
J	50

How can I do this? Thanks

[yuanliu]

In addition to mvzip that @richgalloway suggested, there could be a shortcut using multikv if data format is as regular as illustrated.  Try

| eval event = replace(event, "[^:]+:( *[^,]+)", "\1")
| eval _raw = replace(event, "(\d+.*)", "
\1")
| multikv

This is assuming that the resultant keys ("A", "B", etc.) contain no numeric characters.  The method is still usable if they do contain numeric characters, but the formula needs to be refined.

[richgalloway · Accepted solution]

It can be done, but it requires jumping through a few hoops.  The first step is to extract the Name and Var values.  Then the Names and Vars are paired up and then separated into their own events.  Finally, the pairs are split up for display.

Here's a run-anywhere example.

| makeresults | eval _raw="index=* sourcetype=* type=* \"Name1\" : \"A\", \"Name2\" : \"B\", \"Name3\" : \"C\", ... \"Name10\" : \"J\",
\"Var1\" : 10, \"Var2\" : 10, \"Var3\" : 25, ... \"Var10\" : 50"
```Above defines test data```
```Use rex to extract fields```
| rex max_match=0 "Name\d+\\\"\s*:\s*\\\"(?<Name>[^\\\"]+)"
| rex max_match=0 "Var\d+\\\"\s*:\s*(?<Var>\d+)"
```Pair-up Name and Var values```
| eval zip=mvzip(Name, Var)
```Create separate events for each pair```
| mvexpand zip
```Break up the pairs```
| eval unzip=split(zip, ",")
| eval Name=mvindex(unzip,0), Var=mvindex(unzip,1)
```Display the results```
| table Name,Var

[dzyfer]

Hi @richgalloway , thanks for the reply, unfortunately I am unable to make this method work on my actual data despite it working on the run-anywhere. I am not very familiar with rex, hence please pardon me as I have some questions on the code.

1. Do I need to define the data as per this line? What if I have many (>100) variables?

\"Name1\" : \"A\", \"Name2\" : \"B\", \"Name3\" : \"C\", ... \"Name10\" : \"J\",
\"Var1\" : 10, \"Var2\" : 10, \"Var3\" : 25, ... \"Var10\" : 50"

2. After the | mvexpand zip line, the search returns "Field 'zip' does not exist in the data."


Any further help is much appreciated, thank you.

[richgalloway]

The makeresults and first eval commands must be removed to use the query with real data.  Replace them with the search the returns your data.

The rex commands may need to be modified to fit the real-life data.  Share a sanitized sample of that data if you need help crafting a regular expression.

The mvexpand command will fail if the Name and/or Var fields are missing so it's important to get the regular expressions right.

[dzyfer]

Hi @richgalloway , thanks for the tips, they've helped me solve the issue. 
I have an additional question,

I have fields containing strings that sometimes do not return data, how can I include these as NULL in the table as well? 

E.g.

"stTestData9":"","stTestData10":"TestValue",

Thank you

[richgalloway]

The fillnull comand should help with that.

| fillnull stTestData9