topic Re: calculate duration in Splunk Search

calculate duration

indeed_2000 — Wed, 20 Oct 2021 11:46:18 GMT

Hi
need to calcualte duration bettween each Out/In where A=A+100 B=B IDS=IDS

00:03:02.067 app catZZ_DDP_AP: O[host]A[1000]B[123456]IDS[123456789987]
00:03:02.110 app catZZ_DDP_AP: I[host]A[1100]B[123456]IDS[123456789987]

expected output:
duration B IDS
00:00:00.043 123456 123456789987

Any idea?
Thanks

Re: calculate duration

ITWhisperer — Tue, 19 Oct 2021 18:04:23 GMT

Would this work for you?

| stats earliest(_time) as start latest(_time) as end by B IDS | eval duration=tostring(end-start,"duration") | table duration B IDS

Re: calculate duration

indeed_2000 — Wed, 20 Oct 2021 09:17:09 GMT

Thanks for answer, without the "where" I mention doesn't return correct duration.

any idea?

Re: calculate duration

ITWhisperer — Wed, 20 Oct 2021 09:51:12 GMT

Can you share more representative example data to show why it doesn't work?

Re: calculate duration

indeed_2000 — Wed, 20 Oct 2021 11:47:10 GMT

I have lot's of line like this so I need to if A,B,IDS matched with each other get latest one and calculate duration

1-step one, where:

A=A+100

B=B

IDS=IDS

2-step two, find latest one

3-calculate duration

00:03:00.010 app catZZ_DDP_AP: O[host]A[1000]B[123456]IDS[123456789987]
00:03:00.020 app catZZ_DDP_AP: I[host]A[1100]B[123456]IDS[123456789987]

00:03:01.025 app catZZ_DDP_AP: O[host]A[1000]B[123456]IDS[123456789987]
00:03:01.026 app catZZ_DDP_AP: I[host]A[1100]B[123456]IDS[123456789987]

00:03:02.067 app catZZ_DDP_AP: O[host]A[1000]B[123456]IDS[123456789987]
00:03:02.110 app catZZ_DDP_AP: I[host]A[1100]B[123456]IDS[123456789987]

output:

duration B IDS
00:00:00.043 123456 123456789987

Re: calculate duration

ITWhisperer — Wed, 20 Oct 2021 11:00:53 GMT

| sort 0 B IDS _time | streamstats latest(_time) as previous_time window=1 current=f by B IDS | stats latest(previous_time) as start latest(_time) as end by B IDS | eval duration=tostring(end-start,"duration") | table duration B IDS

Re: calculate duration

indeed_2000 — Wed, 20 Oct 2021 11:59:04 GMT

still return incorrect duration for some of the events that is not A=A+100

e.g:

it find 3 events like this and consider first line as OUT while second line is OUT event of the third line. if condition check "A=1000" must be match to "A+100=1100" it will be fixed.

00:03:00.000 app catZZ_DDP_AP: O[host]A[2000]B[123456]IDS[123456789987]

00:03:02.067 app catZZ_DDP_AP: O[host]A[1000]B[123456]IDS[123456789987]
00:03:02.110 app catZZ_DDP_AP: I[host]A[1100]B[123456]IDS[123456789987]

current output:

duration = 00:00:02.110

expected output:

duration = 00:00:00.043

"A" of each OUT must be match with "A"+100 means 1100

if "A" of OUT is 4000 then "A" of IN shoud be 4100

Re: calculate duration

ITWhisperer — Wed, 20 Oct 2021 12:07:37 GMT

| sort 0 B IDS _time | streamstats latest(_time) as previous_time latest(A) as previousA window=1 current=f by B IDS | where A=previousA+100 | stats latest(previous_time) as start latest(_time) as end by B IDS | eval duration=tostring(end-start,"duration") | table duration B IDS

Re: calculate duration

indeed_2000 — Wed, 20 Oct 2021 12:55:12 GMT

I think this line need to be replace with "match" instead of "where"

| where A=previousA+100

currently put result in A while it shoud check them

if A equal to A+100 they match then calculate duration.

Re: calculate duration

ITWhisperer — Wed, 20 Oct 2021 13:00:33 GMT

Where is keeping events in the pipeline where A in the event is equal to previousA + 100 - it is not an assignment

Re: calculate duration

indeed_2000 — Wed, 20 Oct 2021 15:35:35 GMT

you right about "where", it was my fault.

it work now in result page I have filed that call "servername" need to group by servername

current result:

servername duration B IDS
server1 00:00:00.043 123456 123456789987

server1 00:00:00.033 123456 123456789987

server1 00:00:00.093 123456 123456789987

server2 00:00:00.099 123456 123456789987

expected result:

servername duration B IDS
server1 00:00:00.043 123456 123456789987

00:00:00.033 123456 123456789987

00:00:00.093 123456 123456789987

server2 00:00:00.099 123456 123456789987

any idea?

thanks

Re: calculate duration

ITWhisperer — Wed, 20 Oct 2021 15:54:50 GMT

If you really want servername "removed" or other fields grouped by servername try this:

| stats list(duration) as duration list(B) as B list(IDS) as IDS by servername