Splunk Search

How to calculate the round trip time by the modulo-type sequence number without using join command?

tac24
New Member

By monitoring "ping" packets with tcpdump software, the following records are saved as an example.

alt text

The first two request packets with sequence number(seq) 19611 and 19612 have no reply.
From these records, I want to calculate round trip time (delay). My naive search and its result (=what I expected and is correct) are as follows:

base_search | where direction=="request" | join seq type=outer [ base_search| where direction=="reply" | rename _time as reply_time |table seq reply_time] |table _time seq reply_time | rename _time as request_time |eval request_time=strptime(request_time,"%Y-%m-%d %H:%M:%S.%6N") | eval reply_time=strptime(reply_time, "%Y-%m-%d %H:%M:%S.%6N") | eval delay=reply_time - request_time |eval request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N") | eval reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N") | table request_time seq reply_time delay

alt text

Questions are:
(1) How can I get the same result by not using join search command?
(2) Seq is implemented as 16 bits field in the packet, that is, mod 65536. Therefore, if monitored for a long time, then the same seq value could appear in the saved records. When this happens, my naive program fails to get delay correctly.

How can I solve this problem (also by not using join command)?

Thanks.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

base_search
| stats values(*) AS * range(_time) AS delay earliest(_time) AS request_time latest(_time) AS reply_time dc(direction) AS directions BY seq
| search directions = 2
| fieldformat request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N")
| fieldformat reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N")
| table request_time seq reply_time delay

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

base_search
| stats values(*) AS * range(_time) AS delay earliest(_time) AS request_time latest(_time) AS reply_time dc(direction) AS directions BY seq
| search directions = 2
| fieldformat request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N")
| fieldformat reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N")
| table request_time seq reply_time delay
0 Karma

tac24
New Member

Hi woodcock,
Thank you very much for your answering to my question #2. After I understood your solution, I prepared longer fake data and
tried to check your solution.
Now I believe your code made correct result ( I made some remove/change search commands).

Below is a little bit longer fake data.

alt text

Major changes:
(1) seq of request direction always exist, but not always for reply direction.
So, to determine the sessionID, I used "request" direction, instead of "reply" direction.
(2)It seems that inside of the "stats .... by seq sessionID", _time behaves as number, not YYYY-MM-DD HH:MM:SS.
So, max/min are used, instead of earliest/latest.
As a result, code would be as follows:

base search (assumes that _time is ascending order)
 | streamstats count count(eval(direction="request")) AS sessionID BY seq
 | stats range(_time) as delay  max(_time) as reply_time min(_time) as request_time dc(_time) as directions by seq sessionID
 | search directions=2
 | eval request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N") 
 | eval reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N") 
 | table request_time reply_time delay seq 
 | sort 0 request_time 

Below is the result of this code and is correct.

alt text

Woodcock, thanks for your guidance. Please let me know if the change I made was wrong or I was misunderstanding.

0 Karma

woodcock
Esteemed Legend

As regards the _time field, min=earliest and max=latest so that change is meaningless/harmless. Because you switched from reply to request to trigger the sessionID, you needed to remove both the reverse lines, which you did correctly.

0 Karma

tac24
New Member

Hi woodcock,
thank you very much for your answer. It makes the result what I expected ( I just changed strptime of #4. and #5. to strftime). Combination of stas, range, ..., and by seq is what I can not imagine and my question (1) was solved.

But the above search can not solve the second question:
If monitored for a long time, the same value of seq could appear more than twice,
therefore, the search result could become like this.

alt text

It would be appreciated if you could kindly solve the problem of the question (2).
Thank you very much.

0 Karma

woodcock
Esteemed Legend

I do not understand question #2 so if you supply example (fake) data and then show me what is wrong with the resulting output, I may be able to help. Also, I adjusted my answer to fix the strptime -> strftime mistake.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

He's saying that seq rolls over and is reused. Which means that even the code above wouldn't always work after rollover starts in a high-duration search.

It's not particularly elegant, but something like this would work. First, detect long gaps in the time between iterations. Here it's set for 30 days -- 30*86400 -- but anything up to about 25% less than the rollover time would work.

Every time there's that big a time gap, then add one to a counter. You can start the counter at 1, as below, or at 0. (Just remove the test for timedelta=0 and the first record will get 0.) Then, concatenate that counter on the seq field, creating a newseq field.

base_search
| streamstats window=2 count as seqcount, avg(_time) as avgseqtime by seq 
| eval timedelta=2*abs(_time - avgseqtime)
| eval nextSeqTran= case(timedelta=0,1,timedelta>30* 86400,1,true(),0)
| streamstats sum(nextSeqTran) as nbrSeqTran by seq
| eval newseq= tostring(seq)."-".tostring(nbrSeqTran)

...here you can test to see it's working by having the base search return only 2-3 sequence numbers and then outputting them something like this...

| sort 0 seq _time 
| table seq newseq _time seqcount timedelta  nextSeqTran nbrSeqTran

... which should give you some confidence that each pair is getting a new newseq. Make sure to include test data for a tran that's still missing its response. Once you're certain it's working, then you can proceed...

...and then apply woodcock's code to newseq...

 | stats values(*) AS * range(_time) AS delay earliest(_time) AS request_time latest(_time) AS reply_time dc(direction) AS directions BY newseq
 | search directions = 2
 | fieldformat request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N")
 | fieldformat reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N")
 | table request_time newseq reply_time delay
0 Karma

tac24
New Member

Hi DalJeanis,
Thank you very much for your answer. For a while, I will try woodcock's code below, then return your code.

0 Karma

woodcock
Esteemed Legend

OK, then try this.

This fakes the data (with duplicate seq😞

| makeresults
| eval raw="10.237.122.38 10.255.3.58 request 19611::10.237.122.38 10.255.3.58 request 19612::10.237.122.38 10.255.3.58 request 19613::10.255.3.58 10.237.122.38 reply 19613::10.237.122.38 10.255.3.58 request 19614::10.255.3.58 10.237.122.38 reply 19614::10.237.122.38 10.255.3.58 request 19615::10.255.3.58 10.237.122.38 reply 19615::10.237.122.38 10.255.3.58 request 19614::10.255.3.58 10.237.122.38 reply 19614" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "(?<from_ip>\S+)\s+(?<to_ip>\S+)\s+(?<direction>\S+)\s+(?<seq>.+)" 
| streamstats count AS _serial
| eval _time = _time + 10*_serial
| table _time from_ip to_ip direction seq

This is your updated solution:

| reverse
| streamstats count count(eval(direction="reply")) AS sessionID BY seq
| reverse
| stats range(_time) AS delay earliest(_time) AS request_time latest(_time) AS reply_time dc(direction) AS directions BY seq sessionID
| search directions = 2
| fieldformat request_time=strftime(request_time,"%Y-%m-%d %H:%M:%S.%6N")
| fieldformat reply_time=strftime(reply_time, "%Y-%m-%d %H:%M:%S.%6N")
| table request_time seq reply_time delay
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...