Hi,
I am trying to write a search that seems a bit more tricky than it first looked like... 😉
We have a scenario where users log on to a service and perform several transactions.
In a day, there can be more than 50000 customers, and each customer can have between 100 and 5000 transactions in several different sessions on different client_types.
We have 2 events with this type of data:
timestamp event=login accountid=12345 client_type=mobile
timestamp event=trans accountid=12345 amount=7
timestamp event=trans accountid=12345 amount=3
timestamp event=login accountid=88888 client_type=mobile
timestamp event=trans accountid=12345 amount=9
timestamp event=trans accountid=12345 amount=5
timestamp event=trans accountid=88888 amount=3
timestamp event=login accountid=12345 client_type=web
timestamp event=trans accountid=12345 amount=9
timestamp event=trans accountid=12345 amount=5
I am trying to do a report on sum(amount) by client_type.
We assume that all trans events have the same client_type until next login event occurs.
The transaction command will fail due to the number of events...
// Andreas
I think you can use the streamstats command to achieve your goal, if the data is sorted as above
...
| streamstats last(client_type) AS client_type BY account_id
| stats sum(amount) By client_type
It looks promising but it does not quite produce the correct results.
With the test data above I get this:
You can use streamstats but you first need to reverse the sort order.
| sort + _time| streamstats last(client_type) AS client_type BY account_id| stats sum(amount) By client_type