Search Query issues involving transaction & sum

[aputz]

Hello there,

So I built this query and as the case often is it worked fine with a smaller set of test data but does not behave as expected with a larger set. When working as planned this search language returns a tally of how many times a feature in our system triggers as well as a total duration that the feature was turned on (broken out by location). This worked fine with a smaller set of data.

With a larger amount of events it seems to return inflated numbers that I couldn't find any pattern to the amount of increase. Some triggered totals are off by as much as 6, some as little as 3 or 4. The "duration on" totals end up being off by +50% more to almost +200% more.

index=stuff | transaction startswith="Feature_On" endswith="Feature_Off" | eval FeatureToggled=if(status="Feature_Off",1,0) | stats sum(duration) as D sum(FeatureToggled) as "Times Feature Triggered ON" by Location | eval "Total Feature Duration (min)"=(D/60) | fields "Times Feature Triggered ON" "Total Feature Duration (min)" Location

The information is correct when I specify which "Location" at the beginning of the query so I think it may be incorrectly counting events from other locations when searching/tallying across all locations. I threw a location-picker into the dashboard that displays this information for the time being. Ideally I can tweak this query to display accurate totals/counts for all sites with a single search.
Any help is greatly appreciated, thank you very much!

[aputz]

Hey, thanks for the suggestion! It's working now. Just adding "location" after transaction has cleared up the previous confusion, it looks like so:
transaction location startswith="Feature_On" endswith="Feature_Off"

I couldn't figure out how to mark your comment as the solution to my problem but thanks for the solution, aholzer!

[aholzer]

aputz, thanks for the good intentions, I have switched my comment to answer now 🙂

[aholzer · Accepted solution]

Your transaction doesn't seem to take location into consideration. This would explain why if you specify a location at the beginning of your query, you get expected results, but if you don't then you get out of whack data.

Example:
event1: location A, toggle ON
event2: location B, toggle ON
event3: location B, toggle OFF
event4: location A, toggle OFF

This would put events1-3 into one transaction and throw out event 4 because it would not have a paired start event.

Hope this helps