Group events by multiple fields in Splunk

Question

Hi I have some events in splunk which are of this form-

Location: some value(same value can be there in multiple events)

Client: some value(same value can be there in multiple events)

TransactionNumber: some value(Unique for each event)

Transaction Time: some value(Unique for each event)

Now I want a table in this form -

Basically each location can have multiple clients and each client can have different transactions. Transaction number and transaction time are unique and have one to one mapping.

I am using this query in splunk-

| stats list(TransactionNumber) list(TransactionTime) by Location Client

What's happening is I am getting unique combination of location and client but what I want is unique clients to be listed against a particular Location.

This is what i am getting-

How can the query be modified to achieve the same?

Answer 1

Here is a complete example using the _internal index

index=_internal

| stats list(log_level) list(component) by sourcetype source

| streamstats count as sno by sourcetype 
| eval sourcetype=if(sno=1,sourcetype,"") 
| fields - sno

For your use-case I think this should work

| stats list(TransactionNumber) list(TransactionTime) by Location Client
| streamstats count as sno by Location 
| eval Location=if(sno=1,Location,"") 
| fields - sno

If this fixes your problem, take a moment to accept the answer. This can be done by clicking on the check mark beside the answer to toggle it from greyed out to filled in!

Cheers

Answer 2

I would do this:

index=ndx sourcetype=srctp Location=* Client=* TransactionNumber=* TransactionTime=*
| eval TNTT=TransactionNumber+" sep "+TransactionTime
| stats values(TNTT) as TNTT by Location Client
| rex field=TNTT "(?<TransactionNumber>\S+) sep (?<TransactionTime>.+)"
| table Location Client TransactionNumber TransactionTime

What this does is carry-over the unique, one-to-one mapping (as you described it) of the Time & Number through the stats values() line, then splits them back out afterwards.

You may want to | mvexpand TNTT before doing the rex line - incase you want to sort the table in some other manner later