topic Re: Subtract two time in one event not work properly in milisecond in Splunk Search

Subtract two time in one event not work properly in milisecond

nitesh218ss — Wed, 06 May 2015 13:49:42 GMT

hi try to subtract 2 time but some are subtract some show blank

my time format is 07:33:41.556
I below i write 2 time for subtract and answer also by splunk
07:33:41.556-07:33:39.337 =8338.000000

I also write 2 more time which result is blank in splunk
07:33:40.493 - 07:33:39.649 = blank(No result) why this happening what is solution? my query

index="uk" sourcetype="ukpro" serviceType=1 message="Received * bytes from IP*" | rename time as time1,message as Request | join audit [search index="uk" sourcetype="ukpro" serviceType=1 message="Deleted m_pReceivingSocket" | rename time as time2, message as Responce]| eval itime=strptime(time1,"%H:%M:%S.%z") | eval ptime=strptime(time2,"%H:%M:%S.%z") | eval TimeDiff=(ptime -itime)| table time2 time1 TimeDiff audit

Re: Subtract two time in one event not work properly in milisecond

richgalloway — Wed, 06 May 2015 15:14:46 GMT

Your strptime() calls have format strings that don't match your example times. Try strptime(time1,"%H:%M:%S.%3N").

Re: Subtract two time in one event not work properly in milisecond

woodcock — Wed, 06 May 2015 15:16:50 GMT

You have done the "time" parts correctly so the problem must be in the events. Check out the results of this search and adjust accordingly:

index="uk" serviceType=1 (sourcetype="ukpro"message="Received bytes from IP") OR (sourcetype="ukpro" message="Deleted m_pReceivingSocket") | where isnull(time)

Also, it would be best to avoid the join like this:

index="uk"  serviceType=1 (sourcetype="ukpro"message="Received bytes from IP") OR (sourcetype="ukpro" message="Deleted m_pReceivingSocket") | stats dc(sourcetypes) AS numSourcetypes, earliest(time) AS itime, latest(time) AS ptime by audit | eval TimeDiff=(ptime -itime) | table ptime itime TimeDiff audit

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Thu, 07 May 2015 05:16:41 GMT

Thankyou very much now they subtract correctly

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Mon, 28 Sep 2020 19:45:32 GMT

Thankyou for answer
In last query which you given they give result correctly but time is not subtracting
But i got new way to join event thankyou for this idea

But i want both event message part time as request and responce so with you query how i do this
for this resion i create this query

index="uk" sourcetype="ukpro" serviceType=1 message="Received * bytes from IP*" | rename time as time1*,message as Request* | join audit [search index="uk" sourcetype="ukpro" serviceType=1 message="Deleted m_pReceivingSocket" | rename time as time2, message as Responce]| eval itime=strptime(time1,"%H:%M:%S.%3N") | eval ptime=strptime(time2,"%H:%M:%S.%3N") | eval TimeDiff=ptime -itime| table time2 time1 TimeDiff audit Request Responce

In upper query i rename the message in sub query and main query and after that i use to show message part as different field
I ask this question because your query processing time fast i thing
Thankyou

Re: Subtract two time in one event not work properly in milisecond

woodcock — Thu, 07 May 2015 12:52:51 GMT

I do not understand what you are saying. Perhaps you are saying that you need the times distinctly matched to one message vs. the other? My non-join search does not discriminate but it presumes that the earliest time is "Received" time and the latest time is the "Deleted" time. Is this presumption not correct or do you need a distinctly match time for some other reason? In any case, I am sure I can help if you can be (very much) more clear about what my non-join version lacks.

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Fri, 08 May 2015 12:52:10 GMT

sorry my english is bad that y you not understand.
index="uk" sourcetype="ukpro2" serviceType=1 message="Received * bytes from IP*"|rename time as time1,message as Request | join type="outer" audit[search index="uk" sourcetype="ukpro2" serviceType=1 message="Deleted m_pReceivingSocket"| rename time as time2, message as Responce ]| eval itime=strptime(time1,"%H:%M:%S.%3N") | eval ptime=strptime(time2,"%H:%M:%S.%3N") | eval TimeDiff=ptime -itime |where TimeDiff > 0 |table time2 time1 TimeDiff audit Request Responce |stats avg(TimeDiff) as AVG, max(TimeDiff) as MAX, min(TimeDiff) as Min

In this query if you see table time2 time1 TimeDiff audit Request Responce
this way i able to see message (Request or Responce converted name) in result ?
With your query this is possible ? if not then No problem

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Fri, 08 May 2015 12:58:32 GMT

But i want count the sub query event but i fail because when i use ...| stats count as res in sub query they not work i post this question please reply that answer if possible
http://answers.splunk.com/answers/233225/stats-count-not-work-in-sub-query.html

Re: Subtract two time in one event not work properly in milisecond

woodcock — Fri, 08 May 2015 14:51:38 GMT

I try to avoid subqueries whenever possible because they do not scale. In any case, I still do not understand your question. Is there somebody near you who speaks English a bit better who can translate your question for you? I just do not understand your text.

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Fri, 08 May 2015 17:54:47 GMT

no english persion present here.
i want make query with out subquery if possible:

Result contain this fields:
Average TimeDiff, Min TimeDiff, Max TimeDiff , Total request means the message count which is equal to "Received bytes from IP" and total Responce count means the message which is "Deleted m_pReceivingSocket"
this result come if audit no is equal

Copy past this link in new browser in this i write one more question related to this: http://answers.splunk.com/answers/233225/stats-count-not-work-in-sub-query.html

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Fri, 08 May 2015 18:08:17 GMT

thanks for reply

Re: Subtract two time in one event not work properly in milisecond

woodcock — Fri, 08 May 2015 19:42:31 GMT

Let's start from the beginning. I am going to explain in excruciating detail what I think you mean. I will also explain what I am doing with my searches. You tell me where I am mistaken.

You have datasets:
D1 is index="uk" serviceType=1 sourcetype="ukpro"message="Received bytes from IP"
AND
D2 is index="uk" serviceType=1 sourcetype="ukpro" message="Deleted m_pReceivingSocket"

These 2 datasets share a field called audit.
The "audit" field can be used to cross-reference/link/join the datasets against one-another.
Any specific value for the "audit" field may appear more than once (may exist in multiple event records) in either dataset.

You would like to merge the 2 datasets and aggregate them based on the "audit" field such that all events in either dataset that share a common value for field "audit" are clumped together.

Within each "clump", you would like to calculate the following values:
A1: TimeDiff=Time span between earliest event and latest event.
A2: iNum=The number of "Received"-type events.
A3: pNum=The number of "Deleted"-type events.

That is this search:

index="uk" serviceType=1 (sourcetype="ukpro"message="Received bytes from IP") OR (sourcetype="ukpro" message="Deleted m_pReceivingSocket") | stats count(eval(like(message,"Received%"))) AS iNum, count(eval(like(message,"Deleted%"))) AS pNum, earliest(time) AS lowTime, latest(time) AS highTime by audit | eval TimeDiff=(highTime-lowTime) | table iNum pNum TimeDiff audit

Furthermore, once this aggregation is calculated, you would like to perform an additional level of aggregated statistical analysis to calculate the following values from the output from the previous search as follows:
B1: avg(TimeDiff)
B2: min(TimeDiff)
B3: max(TimeDiff)
B4: sum(iNum)
B5: sum(pNum)

That is this search:

index="uk" serviceType=1 (sourcetype="ukpro"message="Received bytes from IP") OR (sourcetype="ukpro" message="Deleted m_pReceivingSocket") | stats count(eval(like(message,"Received%"))) AS iNum, count(eval(like(message,"Deleted%"))) AS pNum, earliest(time) AS lowTime, latest(time) AS highTime by audit | eval TimeDiff=(highTime-lowTime) | stats avg(TimeDiff), min(TimeDiff), max(TimeDiff), sum(iNum), sum(pNum) by audit

Did I get it correct? If not, carefully highlight what I got wrong.

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Mon, 11 May 2015 10:10:49 GMT

Hi thanks for help
i got this Error in both query
Error in 'SearchProcessor': Mismatched quotes and/or parenthesis.
i also try to solve this now.

Re: Subtract two time in one event not work properly in milisecond

woodcock — Mon, 11 May 2015 13:04:29 GMT

I forgot to close my evals with right-parentheses. I edited and resaved it; try again. Also let me know if my translation/explanation matches what you are trying to do.

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Mon, 11 May 2015 17:58:30 GMT

yes match totally
Only i add this line:
A1: TimeDiff=Time span between earliest event and latest event which audit Number same.

Re: Subtract two time in one event not work properly in milisecond

woodcock — Mon, 11 May 2015 18:09:36 GMT

Then we agree completely already (without any changes) because that is what I mean by "within each clump" (share the same "audit number" value). Have you tried my search? It should give you what you expect now that I fixed the typo. If it does, please click "Accept".

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Tue, 12 May 2015 09:38:57 GMT

ya i run but avg(TimeDiff), min(TimeDiff), max(TimeDiff) is blank in output and sum(iNum), sum(pNum) is 1

I try to accept but they give message you already accept one answer other is not allow

Re: Subtract two time in one event not work properly in milisecond

woodcock — Tue, 12 May 2015 15:13:15 GMT

I think I found the problem: I used time instead of _time. Here is the corrected search:

index="uk" serviceType=1 (sourcetype="ukpro"message="Received bytes from IP") OR (sourcetype="ukpro" message="Deleted m_pReceivingSocket") | stats count(eval(like(message,"Received%"))) AS iNum, count(eval(like(message,"Deleted%"))) AS pNum, earliest(_time) AS lowTime, latest(_time) AS highTime by audit | eval TimeDiff=(highTime-lowTime) | stats avg(TimeDiff), min(TimeDiff), max(TimeDiff), sum(iNum), sum(pNum) by audit

If this doesn't work, then you are going to have to debug my search "pipe by pipe" to see where it is breaking down. Start with everything before the first pipe ("|") character and remove everything after it: does this base search return what you expect? If so, next add in everything up to the next pipe character: Does the work done by the added text work as it should? If so, keep adding. Eventually you will find the point where it is not doing what you expect it to. Report back and we will take it from there.

Re: Subtract two time in one event not work properly in milisecond

nitesh218ss — Tue, 12 May 2015 19:14:18 GMT

ok sir thanks