Dashboards & Visualizations

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

Mrig342
Communicator

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.

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

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

View solution in original post

PickleRick
Ultra Champion

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.

0 Karma

somesoni2
Revered Legend

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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