So a sample of the data I'm working with is as follows
TImestamp | ID | Amount
2015-12-30 09:50:45 | 1 | 28668
2015-12-30 09:50:45 | 2 | 24399
2015-12-30 09:50:45 | 2 | 904
2015-12-30 09:50:45 | 4 | 39292
2015-12-30 09:55:51 | 1 | 1000
2015-12-30 09:55:51 | 2 | 1045
2015-12-30 09:55:51 | 4 | 1035
Essentially, what I'm trying to do is built a Report/Alert that will pop when any user has a variance of say... Greater than 50k between _time (data is imported about every 5-10 minutes, so that's the _time variance).
What I've got so far is something like this:
sourcetype="Log" *| table _time, ID, subAmount1, subAmount2 | eval amount=(subAmount1+subAmount2 ) | delta amount p=1 as amountVar| eval amountVar=-(amountVar)
I can search for an individual ID, and see variances properly between _time, but I'm trying to make a more generic report to simply show highlights on a daily basis for ID's which have a variance greater than a threshold between a certain number of events.
Have you looked at the range
function for streamstats
?
| streamstats range(Amount) as diff by ID | table ID, diff | where diff>50000
I'd tried that, but it returns results similar to the following:
2015-12-30 11:07:38 | 1 | 50309
2015-12-30 10:47:09 | 2 | 50680
2015-12-30 10:47:07 | 2 | 50680
2015-12-30 10:57:23 | 1 | 51634
2015-12-30 10:47:07 | 3 | 52278
2015-12-30 11:17:53 | 4 | 60082
2015-12-30 11:12:45 | 4 | 60117
2015-12-30 11:12:45 | 4 | 60117
2015-12-30 11:07:39 | 4 | 60117
2015-12-30 11:07:38 | 4 | 60117
Where the range appears to be simply Max(Amount)-min(Amount) regardless of _time. The dataset changes dynamically during the day, so ideally I would have a query capable of expressing something similar to:
"For each ID, calculate the difference in Amount between each _time. If the difference between this _time and the previous _time is greater than X, Display a table for _time, ID, Amount."
The issue seems to be that it's difficult to make the query in such a manner than it looks at a delta by ID and _time, because delta is inherently calculating based on the previous event based on _time (regardless of ID, as I'm querying all IDs).