How to Include field name in table?

[bbeebe]

Hello! I have learned so much from this community over the years but there is one query I am trying to write that I cannot figure out.

I have a number of logs each containing four fields, each of those fields have a unique set of a few values. I am trying to do a count for each unique value and put it in a three column table including the field name, value, and count. I know I can hard-code all the values to give them a category/field name but as these values change over time I would rather not have to do that if possible.

Log examples

key exchange algo: dh-group-exchange-sha256, public key algo: ssh-dss, cipher algo: aes128-cbc, mac algo: sha256

key exchange algo: ecdh-sha2-nistp256, public key algo: ssh-rsa, cipher algo: aes256-ctr, mac algo: sha256

Desired result:

field	cipher	count
keyExchange	dh-group-exchange-sha256	##
keyExchange	ecdh-sha2-nistp256	##
publicKey	ssh-dss	##
publicKey	ssh-rsa	##

etc.

Is there a way to do this besides hard-coding a field for each cipher?

For reference, here is how I am pulling the two column list of cipher | count without the field name:

base search
| eval cipher=keyExchange.";".publicKey
| makemv delim=";" cipher
| stats count by cipher

 This also works for two columns but appears to be a bit slower

| eval cipher = mvappend(keyExchange,publicKey)
| mvexpand cipher
| stats count by cipher

Thanks!

[PickleRick]

I'm not sure I follow.

You want a single search to stats

1) overall count by kxalgo

2) overall count by pkalog

3) overall count by cipher

4) overall count by mac

Which would mean for different independent stats?

[bbeebe]

Essentially its four independent "stats" counts for each field put into one table. I could get all the results in a table with the values and their count but wanted to add a column with the field name as well. ITWhisperer provided a solution for that.

[ITWhisperer]

| extract pairdelim="," kvdelim=":"
| untable _time algo cipher
| eval algo_cipher=algo."|".cipher
| stats count by algo_cipher
| eval algo=mvindex(split(algo_cipher,"|"),0)
| eval cipher=mvindex(split(algo_cipher,"|"),1)
| table algo cipher count

[bbeebe]

Thanks for the reply. This returns the algos and ciphers but also every other extracted field as well so the table looks something like this:

algo	cipher
cipher_algo	aes256-ctr
date	20220515
date_hour	14
date_mday	15
host	xxxxx
index	xxxxx
key_exchange_algo	ecdh-sha2-nistp256

There are ~35 fields per result returned, I don't know enough about what you suggested to troubleshoot. Is there another way to do it if I already have the four fields extracted or do you need to use the paridelim & kvdelim to get the field names included?

[ITWhisperer · Accepted solution]

If you already have the fields extracted e.g. *_algo, try this

| table _time *_algo
| untable _time algo cipher
| eval algo_cipher=algo."|".cipher
| stats count by algo_cipher
| eval algo=mvindex(split(algo_cipher,"|"),0)
| eval cipher=mvindex(split(algo_cipher,"|"),1)
| table algo cipher count

[bbeebe]

This did it perfectly, wow. I really appreciate your time, going to dive a bit deeper into untable and mvindex to see what you did there.

Thanks!

[ITWhisperer]

Perhaps you could share the actual event so we can see what you are actually dealing with - it might help with getting a more accurate solution?