Splunk Search
Highlighted

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

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 clienttype.
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

Highlighted

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

Motivator

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
Highlighted

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

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

With the test data above I get this:

alt text

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma