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.
Assuming all the related log entries have the same timestamp in _time
| stats values(*) as * by _time
| mvexpand instance
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.
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.
Assuming all the related log entries have the same timestamp in _time
| stats values(*) as * by _time
| mvexpand instance