topic Re: How can I convert Key value into field value data? in Splunk Search

How can I convert Key value into field value data?

srv007 — Tue, 02 May 2023 18:24:43 GMT

The data is in key value format instead of field value due to limitation of fields to be used.

There are 10+ key value for each host. I'm looking to convert these key value data into fields so that i can apply the drop down filter as input and search result get updated as per the field selected in the drop down input.

Here is an example of how my data is appearing in splunk.

index=this_is_demo source="demo-kv" script=demo.ps1 hostname IN (*)

hostname - server1.com
IP - 128.xx.xx.xx
group -
key - username
value - john
script -demo.ps1

hostname - server1.com
IP - 128.xx.xx.xx
group -
key - hardware manufacturer
value - HP
script -demo.ps1

hostname - server1.com
IP - 128.xx.xx.xx
group -
key - Puppet status
value - active
script -demo.ps1

hostname - server1.com
IP - 128.xx.xx.xx
group -
key - CPU count
value - 12
script -demo.ps1

Appreciate all the knowledge and guidance here. 🙂

Re: Convert Key value into field value data..

rut — Tue, 02 May 2023 17:14:38 GMT

Hi @srv007

If I understood correctly, the example data is the _raw value for each row? If you want to solve this search time, you could try to use the extract command:

| extract kvdelim="-" pairdelim="\n"

Note that the default behavior of the command is set to a max of 50 kv pairs and it that it only works on the _raw field.

You can read more about the command over here:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Extract

If the above data is not contained in the _raw field, could you reply with an example of a data row?

Re: How can I convert Key value into field value data?

abi2023 — Tue, 02 May 2023 22:36:32 GMT

index=this_is_demo source="demo-kv" script=demo.ps1 hostname IN (*)
| rex "(?<key>\w+)\s+-\s+(?<value>[^\r\n]+)"
| kv
| stats values(*) as * by hostname

Re: How can I convert Key value into field value data?

bowesmana — Wed, 03 May 2023 01:07:08 GMT

If you mean key value as key=username, value=john for the first event, therefore what you are looking for are the keys

username

hardware manufacturer

Puppet status

CPU count

and the corresponding values for each of those keys

You can get those attributes from the data with (assuming your data is as presented)

| rex "key\s*-\s*(?<key>.*)\nvalue\s*-\s*(?<value>.*)"

then you have key and value column that each contain the name of the key and the value of that key

If you want to use the key as a dropdown input for key selection, just use fieldForLabel and value as key

As for a dropdown for value where you only want the values shown for your selected key, then you have to add an addition search fragment

| search key=$your_key_token|s$

in your dropdown generating search, so it only shows the values for the selected key.,

Re: How can I convert Key value into field value data?

srv007 — Wed, 03 May 2023 09:01:29 GMT

Requirement is to break key value into fields and show data in below format for each hosts --

hostname : server1.com

username : john

hardware manufacturer : HP

Puppet status : Active

CPU count : 12

hostname : server2.com

username : Tim

hardware manufacturer : IBM

Puppet status : Fail

CPU count : 12

Re: How can I convert Key value into field value data?

bowesmana — Thu, 04 May 2023 06:55:30 GMT

So something like this

index=this_is_demo source="demo-kv" script=demo.ps1 hostname IN (*) | rex "key\s*-\s*(?<key>.*)\nvalue\s*-\s*(?<value>.*)" | eval kv=key.":".value | stats values(kv) as kvs by hostname ``` And now put hostname into that list ``` | eval kvs = mvappend("hostname:".hostname, kvs) | fields - hostname

Re: How can I convert Key value into field value data?

srv007 — Thu, 04 May 2023 20:31:44 GMT

This is how the output is from above query which is not what im looking for.

What i want to achieve is something like below --

Sorry if i have not been clear much

Re: How can I convert Key value into field value data?

srv007 — Thu, 04 May 2023 20:35:38 GMT

And later on need to apply drop down filter on those fields such as username, Puppet status.

Re: How can I convert Key value into field value data?

srv007 — Fri, 05 May 2023 12:06:32 GMT

I managed to get the data displayed in below format by using chart command.

index=this_is_demo source="demo-kv" script=demo.ps1 hostname IN (*)
|chart limit=0 list(value) over hostname by key
|transpose 0

My only requirement left is use filters where we can select the keys such as username or puppet status and it should display the selected filter only.

Re: How can I convert Key value into field value data?

ITWhisperer — Sat, 06 May 2023 11:42:11 GMT

Assuming each group is a separate event and each event is multi-lined, try something like this

| extract pairdelim="\n" kvdelim="-" | eval {key}=value | fields - key value | stats values(*) as * by hostname

If not, please share you events in a code block (similar to above code using the </> button), so we can see what you are actually dealing with