Splunk Search

How to create a table in which mandatory and optional fields are correctly aligned

marcoemme41
New Member

Hello, I have a problem extracting data from a log with format not fixed.

I explain:
each row of my log contains a mandatory tag (always present) followed by some other optional tags (so they may be present or not).
For example:

father="A"; son1="A1"; son2="A2"; son3="A3"
father="B"; son1="B1"; son3="B3"
father="C"; son2="C2"; son3="C3"

I need a query returning a table like this, where all values are correctly aligned under the respective tag:

father sun1 sun2 sun3
 A     A1   A2   A3
 B     B1   -    B3
 C     -    C2   C3

OK, I must use "rex" statement to identify "father", "sun1", "sun2", "sun3" values.
But when, after this, I use "table" statement to see the results, I obtain a table where the alignment of the values is lost (for example, value C3 is under the wrong column "sun2" instead the correct column "sun3"):

father sun1 sun2 sun3
 A     A1   A2   A3
 B     B1   B3
 C     C2   C3

Can someone help me?

0 Karma
1 Solution

jason_prondak
Explorer

Does this help?

| makeresults 
| eval raw="father=A;son1=A1;son2=A2;son3=A3 father=B;son1=B1;son3=B3 father=C;son2=C2;son3=C3" 
| makemv raw
| mvexpand raw
| rename raw AS _raw 
| kv 
| fillnull value="-" 
| fields - _* 
| table f* s*

View solution in original post

0 Karma

Vijeta
Influencer

@marcoemme41 Please share the rex command you are using to get father, son1,son2 and son3 values.

0 Karma

jason_prondak
Explorer

Does this help?

| makeresults 
| eval raw="father=A;son1=A1;son2=A2;son3=A3 father=B;son1=B1;son3=B3 father=C;son2=C2;son3=C3" 
| makemv raw
| mvexpand raw
| rename raw AS _raw 
| kv 
| fillnull value="-" 
| fields - _* 
| table f* s*
0 Karma

marcoemme41
New Member

It works! Thanks jason and rich for your precious help.
Now I have to apply this solution to my real problem. I will keep you informed

0 Karma

Richfez
SplunkTrust
SplunkTrust

Add this to the end of your base search* :

... | table *

*base search, like sourcetype=foo index=bar so the whole thing is like sourcetype=foo index=bar | table *.

You should look into Splunk Fundamentals 1 class, I think it covers this and is free.

0 Karma

marcoemme41
New Member

Thank you rich7177,

unfortunately "table" statement doesn't work correctly in this case .

The absence of some tags causes a table with non-aligned fields, like the example I've posted before.

0 Karma

Richfez
SplunkTrust
SplunkTrust

Hmm! This is not how it should work, so there must be another issue at hand.

Just to confirm behavior, I tested by importing the data you provided into an index, telling Splunk nothing other than to break every line and to timestamp it at index time. Splunk extracted the values, and ...| table * worked fine. A tweak, as jason.prondak mentions below, is to ... | table s* f* so you don't get raw and stuff. But still, I have it working absolutely fine. Gaps appear in the responses as expected when there is an event with no value for a particular field.

So some follow on questions...

How are you using regex to extract these fields? Is that your actual data that has a problem or is it sample? What sourcetype is used for it?

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...