Nested Transactions? lookup tables or a better way?

[mrflibbleuk]

Hi,

I have a single large dataset that is related as follows. Each User has a UserID, when they login a SessionID is created, these Sessions then spawn TransactionID's which is a pair consisting of a request and a response.

The basic logic needs to go as follows:
I carry out a search on a particular UserID on the dataset.
I then collect all the SessionID's that are related to this UserID.
Then using this SessionID I collect a list of all TransactionID's that are used during this session.
Finally I lookup all of the TransactionID's so that I can view all requests and responses that the user has carried out.

I have tried a number of methods but cant seem to get this working correctly. Taking note that the SessionID does not appear in every log record. Sometimes a record is referred only by the TransactionID. The dataset is also huge so there is no way of carrying out a transaction statement on all records before I do the search.

As an interim I carried out the following to create an Index table and then run another search against this...and this works. But its clunky. Any ideas?

index=* [search CustomerID=9999 | fields SessionID] | fields LinkedID | table LinkedID | outputlookup transactions.csv

index=* [|inputlookup transactions.csv | fields + LinkedID] | transaction LinkedID

Cheers

[lguinn2]

First, is all of the data in the same index? Or are the events split across a number of indexes - if so, how are they split?
What exactly do you want to see in the results?

This might work, or it might spark some new ideas...

LinkedID=* | sort _time | stats list(_time) as Timestamp list(_raw) as Events by LinkedID
| join type=outer LinkedID [ search customerID=9999 
                             | dedup SessionID LinkedID | fields SessionID LinkedId ]
| table SessionID LinkedID Timestamp Events