Splunk Search

Subtract two time in one event not work properly in milisecond

nitesh218ss
Communicator

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

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

nitesh218ss
Communicator

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

nitesh218ss
Communicator

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

nitesh218ss
Communicator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

nitesh218ss
Communicator

ok sir thanks

0 Karma

woodcock
Esteemed Legend

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

nitesh218ss
Communicator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

nitesh218ss
Communicator

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

0 Karma

nitesh218ss
Communicator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

nitesh218ss
Communicator

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

0 Karma

nitesh218ss
Communicator

thanks for reply

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

nitesh218ss
Communicator

Thankyou very much now they subtract correctly

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!