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

[marcoemme41]

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?

[Vijeta]

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

[jason_prondak · Accepted solution]

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*

[marcoemme41]

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

[Richfez]

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.

[marcoemme41]

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.

[Richfez]

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?