Unable to get proper results for the Average field with my search

[OMohi]

Hi:

I am unable to get proper result for the Average Field.

Here is my search:

index=entloggingnonprod_catchall_bam sourcetype=mq_business_nonprod source="F:\\temp\\..." | xmlkv | search "mstns:ActivityTransCorrelationId"=* | eval Date=strftime(_time, "%m-%d-%Y") | stats min("mstns:MilestoneDate") as BeginMilestoneTime max("mstns:MilestoneDate") as EndMilestoneTime  by "mstns:ActivityTransCorrelationId" | eval it = strptime(BeginMilestoneTime, "%Y-%m-%dT%H:%M:%S.%3N") | eval ot = strptime(EndMilestoneTime, "%Y-%m-%dT%H:%M:%S.%3N") | eval ResponseTime = round((ot - it)/60) | appendcols [search index=entloggingnonprod_catchall_bam sourcetype=mq_business_nonprod source="F:\\temp\\..." | xmlkv | search "mstns:ActivityTransCorrelationId"=* | stats min("mstns:MilestoneDate") as BeginMilestoneTime max("mstns:MilestoneDate") as EndMilestoneTime  by "mstns:ActivityTransCorrelationId" | eval it = strptime(BeginMilestoneTime, "%Y-%m-%dT%H:%M:%S.%3N")  | eval ot = strptime(EndMilestoneTime , "%Y-%m-%dT%H:%M:%S.%3N") | eval ResponseTime = round((ot - it)/60) | stats avg(ResponseTime) as Average by ResponseTime ] | rename "mstns:ActivityTransCorrelationId" as ActivityTransCorrelationID | rex field=BeginMilestoneTime  mode=sed "s/T.*//" | rex field=EndMilestoneTime  mode=sed "s/T.*//" | rex field=EndMilestoneTime  mode=sed "s/T/  /g" | rex field=EndMilestoneTime  mode=sed "s/Z//g" |  sort date_mday ascending

Is there something that I may be missing?

[martin_mueller · Accepted solution]

That entire search is a mess. Here's what looks weird to me:

• You're basically running the same search and pre-computation twice - that's asking for trouble in terms of making quick errors, poor maintainability, and terrible performance.
• The first search returns a row per correlation ID, the second search returns a row per response time - appendcols won't yield useful results because the rows don't fit together.
• stats avg(ResponseTime) by ResponseTime doesn't make sense.
• Remove all the sed'ing for debugging to keep things simple, add fancy formatting once the facts work out.
• You're sorting by date_mday - that field shouldn't exist because your stats doesn't return it.

Without knowing what you're looking for, especially what the average field should correspond to, there really is no way to tell how the search should actually look like to yield your desired results.

[OMohi]

I used event-stats and am getting proper results. Thanks for your suggestions

[skoelpin]

How sure are you that it's due to the average command? What error message are you getting? I would suggest taking off your alias's first and see if you get the same error. I would also suggest trying it in pieces and adding more upon success. We need more information so we can help you