Splunk Search

How to Include field name in table?

bbeebe
Explorer

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!

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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?

 

0 Karma

bbeebe
Explorer

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
0 Karma

bbeebe
Explorer

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:

algocipher
cipher_algoaes256-ctr
date20220515
date_hour14
date_mday15
hostxxxxx
indexxxxxx
key_exchange_algoecdh-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?

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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!

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...