topic Re: Transforming event data for table display in Splunk Search

How to transform event data for table display?

dzyfer — Wed, 21 Sep 2022 03:32:27 GMT

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

Re: Transforming event data for table display

richgalloway — Fri, 16 Sep 2022 14:13:42 GMT

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

Re: Transforming event data for table display

dzyfer — Mon, 19 Sep 2022 04:43:03 GMT

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.

Re: Transforming event data for table display

richgalloway — Mon, 19 Sep 2022 12:55:54 GMT

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.

Re: Transforming event data for table display?

yuanliu — Tue, 20 Sep 2022 09:25:26 GMT

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.

Re: Transforming event data for table display

dzyfer — Wed, 21 Sep 2022 05:53:16 GMT

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

Re: Transforming event data for table display

richgalloway — Wed, 21 Sep 2022 12:26:52 GMT

The fillnull comand should help with that.

| fillnull stTestData9