topic Re: Join two lines in the same search in Splunk Search

Join two lines in the same search

clementros — Wed, 30 Sep 2020 03:08:57 GMT

Hi all,

I'm currently monitoring log files.
I have exctrated 2 fields end_collection_timestamp & starting_collection_timestamp.

I want to calculate duration of execution.

| eval duration = end_collection_timestamp - starting_collection_timestamp

But this method do not work because every lines with field end_collection_timestamp do not contain the fields starting_collection_timestamp.

I do not understand all but i think this is the root cause.

The result i want is a timechart with avg duration by day & source.

Thanks for your help

Re: Join two lines in the same search

arjunpkishore5 — Wed, 27 Nov 2019 13:33:04 GMT

I would guess you have some kind of id field for which you are calculating your duration, if yes, you need to first bring them to the same row. One way to do this is like this.

| stats max(end_collection_timestamp ) as end_collection_timestamp , min(starting_collection_timestamp) as starting_collection_timestamp by <your id field>
| eval duration = end_collection_timestamp - starting_collection_timestamp

This is just one of the solutions. If this is not what you were looking for, please elaborate on the question by adding some sample data.

Hope this helps.

Re: Join two lines in the same search

clementros — Wed, 27 Nov 2019 13:45:33 GMT

Hi @arjunpkishore5,

Thank you for your help.

i tried this search but the field duration is empty.

index="saplogs" sourcetype=SAPCARBOOKING source="CARBOOKING.*.log" 
| stats max(end_collection_timestamp) as end_collection_timestamp , min(starting_collection_timestamp) as starting_collection_timestamp by source 
| eval duration = end_collection_timestamp - starting_collection_timestamp 
| table duration, starting_collection_timestamp, end_collection_timestamp, source

Re: Join two lines in the same search

arjunpkishore5 — Wed, 30 Sep 2020 03:09:11 GMT

Hi @clementros

Are the fields end_collection_timestamp and end_collection_timestamp stored as a string or epoch?
If it is a string, you have to first use strptime to convert it to epoch before the stats like this.

 index="saplogs" sourcetype=SAPCARBOOKING source="CARBOOKING.*.log" 
| eval end_collection_timestamp= strptime(end_collection_timestamp, "<time format>")
| eval starting_collection_timestamp= strptime(starting_collection_timestamp, "<time format>")
| stats max(end_collection_timestamp) as end_collection_timestamp , min(starting_collection_timestamp) as starting_collection_timestamp by source 
| eval duration = end_collection_timestamp - starting_collection_timestamp 
| table duration, starting_collection_timestamp, end_collection_timestamp, source

Here is the documentation for strptime -
https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/DateandTimeFunctions#strptime.28X.2CY.29

Re: Join two lines in the same search

clementros — Wed, 30 Sep 2020 03:09:13 GMT

They are store like string.

I updated your search like this :

strptime(end_collection_timestamp, "%Y-%m-%d %H:%M:%S")

But still have no value for end_collection_timestamp, starting_collection_timestamp, duration

Re: Join two lines in the same search

arjunpkishore5 — Wed, 27 Nov 2019 16:57:52 GMT

can you post a sample of the timestamp in it's string format

Re: Join two lines in the same search

woodcock — Wed, 27 Nov 2019 17:40:18 GMT

Like this:

... | streamstats count(end_collection_timestamp) AS sessionID BY source
| stats min(starting_collection_timestamp) AS _time max(end_collection_timestamp) AS end_collection_timestamp BY sessionID source
| eval duration = end_collection_timestamp - _time
| timechart span=1d avg(duration) BY source

Re: Join two lines in the same search

clementros — Fri, 29 Nov 2019 07:58:19 GMT

| makeresults | eval t=typeof(starting_collection_timestamp)
            _time                      |             t
2019-11-29 08:56:43                 Invalid

| makeresults | eval t=typeof(end_collection_timestamp)

            _time                      |             t
2019-11-29 08:56:43                 Invalid

Re: Join two lines in the same search

clementros — Wed, 30 Sep 2020 03:15:38 GMT

starting_collection_timestamp = Thu Oct 17 22:40:10 GMT 2019
end_collection_timestamp = Thu Oct 17 22:40:21 GMT 2019

please help

Re: Join two lines in the same search

woodcock — Thu, 05 Dec 2019 15:35:25 GMT

I gave you a complete answer already.