Splunk Search

Join two lines in the same search

clementros
Path Finder

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

Tags (2)
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

View solution in original post

arjunpkishore5
Motivator

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.

0 Karma

clementros
Path Finder

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
0 Karma

arjunpkishore5
Motivator

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

0 Karma

clementros
Path Finder

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

0 Karma

arjunpkishore5
Motivator

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

0 Karma

clementros
Path Finder
| 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
0 Karma

clementros
Path Finder

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

please help

0 Karma

woodcock
Esteemed Legend

I gave you a complete answer already.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!