Join two lines in the same search

[clementros]

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

[woodcock · Accepted solution]

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

[arjunpkishore5]

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.

[clementros]

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

[arjunpkishore5]

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...

[clementros]

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

[arjunpkishore5]

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

[clementros]

| 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

[clementros]

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

please help

[woodcock]

I gave you a complete answer already.