streastats or stats has unexpected results after a day of no activity

[spencer6524]

I have built a table showing mac addresses and the locations they are traveling between. The goal is to know which devices are traveling to the most locations in any given time. Using the below search yields what looks like good results:

`sourcetype=adsm "`Desc=Client" 
|lookup OFFICE_NBR as office OUTPUT LNG_MSR LTD_MSR
|streamstats current=f window=0 last(LTD_MSR) as LTD_MSR_2 last(LNG_MSR) as LNG_MSR_2  last(_time) as t2 by dev_mac
|eval mph=((sqrt(pow(LTD_MSR-LTD_MSR_2,2)+pow(LNG_MSR-LNG_MSR_2,2)))*3.1415926*3959/360)/((t2-_time)/60/60)
|eval distance=(sqrt(pow(LTD_MSR-LTD_MSR_2,2)+pow(LNG_MSR-LNG_MSR_2,2)))*3.1415926*3959/360
|stats distinct_count(office) as ct_office max(distance) as max_dist max(mph) as max_travel_speed_mph by dev_mac
|where ct_office > 2 and max_travel_speed_mph < 45 and max_dist < 500

Basically, I have a lookup table of the lat/lons for each office and I calculate the distance between them and the time between events to get a (very) rough estimate of the distance traveled and speed which needed to be traveled.

What I get is a list of dev_mac's, max distance and max speed between events for any given mac. However, if a device has no events for a couple days, the calculations turn blank in the stats output and they drop off the list even if the search time frame includes all events (plus a couple of days with no events). I can see them if I remove the 'where' clause. Any thoughts as to why this would happen?

Any help would be appreciated.

[DalJeanis]

1) Why do you require 3 offices (ct_office > 2)?
2) Why are you looking for SLOW speeds (max_travel_speed_mph < 45)?

3) I assume you know that by default you are processing the most recent events first, and then going back in time across the time frame...
4) If you calculate distance first, then the formula for MPH becomes much simpler. (Assuming the formulae are correct.)
4) The logical condition AND must be capitalized in splunk.

[spencer6524]

Thanks DalJeanis. I would assume nothing, I've been using Splunk for about two weeks now. I think the answer lies somewhere in the processing order. I added two fillnulls for distance and mph after the streamstats and all the proper mac addresses showed up, but with the fillnull values only in the stats table.

As for points 1 and 2, I added those based on our user behavior and distance between offices to eliminate the noise. Thanks for the help!