Is there a design pattern for join searches with simple data?

[andreasknutsso1]

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

[HeinzWaescher]

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

[andreasknutsso1]

It looks promising but it does not quite produce the correct results.

With the test data above I get this:

[sideview]

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