How to put the values row-wise in a table?

[Mrig342]

Hi All,

I  have logs like below in Splunk:

log1:  Valid from: Mon Oct 11 05:12:56 EDT 2021 until: Wed Oct 11 05:12:56 EDT 2023

log2: Serial number: 6900015f06a7454c0728c2744b000000015f06

log3: Owner: CN=sd-72m2-rt6w.nam.nsroot.net, OU=166139, O=Citigroup Inc., L=warren, ST=NJ, C=US

log4: /apps/gcafews_SG/jboss-eap-7.3/ssl/server.jks

log5: /apps/gcafewshlc_SG/jboss-eap-7.3/ssl/server.jks

and so on...

Aim is to get the validity of each Instance and CN, so created the below query to extract the required fields and to find the validity in days:

..... | rex field=_raw "\/apps\/(?P<Instance>\w+)\/"
| rex field=_raw "CN\=(?P<CN>[^\,]+)\,"
| rex field=_raw "Serial\snumber\:(?P<Serial_Number>[^\,]+)"
| rex field=_raw "OU\=(?P<CSI_ID>[^\,]+)\,"
| rex field=_raw "until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"
| eval From = _time | eval Until = strptime(Valid_Until, "%a %b %d %H:%M:%S %Z %Y") | eval dur=Until-From | eval Validity = round(dur/(60*60*24))

Now to represent all these data in a tabular view I used the query :

| table Instance,CN,Serial_Number,CSI_ID,Valid_Until,Validity

But it gave me the table in the below manner:

Instance	CN	Serial_Number	CSI_ID	Valid_Until	Validity
				Wed Oct 11 05:12:56 EDT 2023	556
		6900015f06a7454c0728c2744b000000015f06
	sd-72m2-rt6w.nam.nsroot.net		166139
gcafews_SG
gcafewshlc_SG

The requirement is to create table with the values in single row as below:

Instance	CN	Serial_Number	CSI_ID	Valid_Until	Validity
gcafews_SG	sd-72m2-rt6w.nam.nsroot.net	6900015f06a7454c0728c2744b000000015f06	166139	Wed Oct 11 05:12:56 EDT 2023	556
gcafewshlc_SG	sd-72m2-rt6w.nam.nsroot.net	6900015f06a7454c0728c2744b000000015f06	166139	Wed Oct 11 05:12:56 EDT 2023	556

Please help modify the query to get the table in the desired manner.

[PickleRick]

Splunk treats all data event-wise which means that to be able to properly analyze your data you should have it properly split into single events which means that whole "pack" of information regarding one abstract "event" from the real world you're refering to should ideally be contained within a single splunk event. So it's best if you can configure your input to break the data properly at "real" event boundaries.

If you can't do that (and often you can't) you can use some approaches based on the common field or set of fields and aggregate events using stats or transaction commands. This however requires that splunk can decide which part belongs to each event - either by means of some common ID that is shared by all events which should be combined or by means of recognizable beginning and end of a transaction.

Unfortunately, if you cannot find such relations between events you have in splunk, it will not be able to reconstruct whole "events" from them. Even groupping by _time or designating transactions by start and end will not work if you have your events interleaved.

[somesoni2]

Is line breaking not working correctly for your logs? If all those log lines represent attribute for one instance, they should appear on same event. You can join them at search time if each log entry has some attribute based on which you can aggregate them.

[ITWhisperer · Accepted solution]

Assuming all the related log entries have the same timestamp in _time

| stats values(*) as * by _time
| mvexpand instance