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
Your strptime() calls have format strings that don't match your example times. Try strptime(time1,"%H:%M:%S.%3N")
.
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.
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.
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.
yes match totally
Only i add this line:
A1: TimeDiff=Time span between earliest event and latest event which audit Number same.
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".
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
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.
ok sir thanks
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
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
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.
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
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
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.
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
thanks for reply
Your strptime() calls have format strings that don't match your example times. Try strptime(time1,"%H:%M:%S.%3N")
.
Thankyou very much now they subtract correctly