Sort field based on sorted field

[ohlafl]

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?

[somesoni2]

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.

[woodcock]

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]

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]

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

[woodcock]

So did you get a solution?

[ohlafl]

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?