Splunk Search

Sort field based on sorted field

ohlafl
Communicator

Hello, I'm trying to get out a table that sorts connections by network type, and then for each connection displays the session code and session state.

It's sorted over four columns from left to right: Network type, connection, connection code, connection state.

As of now it sort of works, the connections are corretly listed by each network type but the corresponding connection state and code are not correct, they change at random and seems to be sorted by time of event or something rather than based on the connection.

I'm looking for something like this:

... | eval sessionCode=(get session code) | eval sessionState=(get session state) | stats list(connection), (list(connectionState) by connection), (list(connectionCode) by connection), by networkType

But this obviously doesn't work. How would this be possible?

Tags (4)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

... | eval sessionCode=(get session code) | eval sessionState=(get session state) | stats count by networkType connection connectionState connectionCode| stats list(connection), (list(connectionState) by connection), (list(connectionCode) by networkType

The first stats will sort the events by networkType connection connectionState and connectionCode, next status will just list them so the sorting order maintained.

0 Karma

woodcock
Esteemed Legend

With stats, when you use list, you get 1 entry for each occurrence (including duplicates) and it is listed in time-sequenced order but when you use values you get only distinct entries (no duplicates) and it is listed in alphabetically sorted order. Perhaps you should switch to using values instead of list. Of course if you do this, you will lose the "row-ish" relationship between lists (each list of values is sorted independently).

Alternatively, you could create an aggregate field and then sort based on that, like this:

... | eval sessionCode=(get session code) | eval sessionState=(get session state) | eval sessionCodeAndState = sessionCode . ":" . sessionState | stats list(connectionCodeAndState) by connection networkType | sort 0 connection networkType connectionCodeAndState

woodcock
Esteemed Legend

Perhaps what you are looking for is not "sorting" but "nesting". Does this look better?

... | eval sessionCode=(get session code) | eval sessionState=(get session state) | eventstats values(connection) AS connectionsByNetworkType BY networkType | eventstats values(connectionCode) AS connectionCodesByConnection BY connection | stats first(connectionsByNetworkType) AS connectionsByNetworkType first(connectionCodesByConnection) AS connectionCodesByConnection by connection networkType

ohlafl
Communicator

This was not the solution but it did help me to better understand the principles of running queries, thanks!

0 Karma

woodcock
Esteemed Legend

So did you get a solution?

0 Karma

ohlafl
Communicator

Switching to values does not have any impact but I guess it cannot be used the way I want to anyway.

Creating an aggregate field sort of works but the concenating does not, the row switches bewteen displaying connectionCode or connectionState depending on the latest event. Any ideas on this?

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