topic Re: How to create a table in which mandatory and optional fields are correctly aligned in Splunk Search

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

marcoemme41 — Fri, 22 Feb 2019 13:48:07 GMT

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?

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

Richfez — Fri, 22 Feb 2019 14:00:01 GMT

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.

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

marcoemme41 — Fri, 22 Feb 2019 17:12:31 GMT

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.

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

jason_prondak — Fri, 22 Feb 2019 18:02:03 GMT

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*

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

Vijeta — Fri, 22 Feb 2019 20:17:26 GMT

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

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

Richfez — Fri, 22 Feb 2019 20:47:41 GMT

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?

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

marcoemme41 — Mon, 25 Feb 2019 09:57:19 GMT

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