Splunk Search

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

andreasknutsso1
Engager

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

andreasknutsso1
Engager

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

With the test data above I get this:

alt text

0 Karma

sideview
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...