Splunk Search

How to transform event data for table display?

dzyfer
Path Finder

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

Labels (7)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

dzyfer
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

dzyfer
Path Finder

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The fillnull comand should help with that.

| fillnull stTestData9
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...