topic Re: How to show all values of a field on the same table in Splunk Search

How to show all values of a field on the same table?

din98 — Sun, 12 Mar 2023 13:01:37 GMT

Re: How to show all values of a field on the same table

ITWhisperer — Sat, 08 Oct 2022 11:52:09 GMT

You can either just list all the fields in the table command and sometimes they will be null (shown as blank) and other times they will have values, or you can use an eval with an if function or coalesce command to place the values in another field depending on the job type.

Re: How to show all values of a field on the same table

din98 — Sun, 12 Mar 2023 13:03:31 GMT

Re: How to show all values of a field on the same table

ITWhisperer — Sat, 08 Oct 2022 13:13:16 GMT

use single quotes around field names (double quotes are for strings)

Re: How to show all values of a field on the same table

gcusello — Sat, 08 Oct 2022 13:27:39 GMT

Hi @din98,

you should explore the use of colesce option in eval command (https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/ConditionalFunctions#coalesce.28X.2C....29 or https://www.splunk.com/en_us/blog/tips-and-tricks/search-command-coalesce.html?locale=en_us )

something like this:

index=$tnkenvironment$ sourcetype=*json source=*webhook* $tknhost$ Type ="job.*" $tknProcessname$ $tknRobot$ $tknStatus$ | eval 'job.created'=coalesce('job.created','job{}.created'), 'job.stopped'=coalesce('job.stopped','job{}.stopped)' | table _time ...

Then in addition, son't use the search command after the main search it's slower than put the additional search parameter in the main search.

Thne don't use the asterisk at the beginning od a string because you have low performances, it's better to have a list using the IN operator.

Then, next time, please, put your search in the message as text not as screenshot, because in this case I have to manually insert it.

Ciao.

Giuseppe

Re: How to show all values of a field on the same table

din98 — Sun, 12 Mar 2023 13:02:21 GMT

Re: How to show all values of a field on the same table

gcusello — Sun, 09 Oct 2022 09:56:11 GMT

Hi @din98,

no problem, it's a pleasure to help you, only one thing: next time, open a new case for a new answer, don't attach it to another already answered one because you'll have less answers.

anyway, you have to create the conditions you described using eval, so for example:

| eval 'Job.Release.ProcessKey'=if(Type='job.created' OR Type='job.stopped','Job.Release.ProcessKey','Jobs{}.Release.ProcessKey')

you can use if or case to define all your conditions.

One additional thing, avoid to use fields with spaces or special chars (as doc), it's better to rename them before manipulations and calculations

| rename 'Job.Release.ProcessKey' AS Job_ProcessKey 'Jobs{}.Release.ProcessKey' AS Jobs_ProcessKey | eval Job_ProcessKey=if(Type="job.created" OR Type="job.stopped",Job_ProcessKey,Jobs_ProcessKey)

Ciao.

Giuseppe

Re: How to show all values of a field on the same table

yuanliu — Sun, 09 Oct 2022 20:38:29 GMT

@din98 As @gcusello mentioned, posting search sample and data sample in text would greatly help other people to help you. I want to address a more fundamental issue that you need to consider:

Given that Splunk notation jobs{} indicates an array, which could contain multiple elements, e.g.,

_raw	_time	job.key	job.type	jobs{}.key	jobs{}.type
{ "job": {"key": "job1", "type": "job.created"}}	2022-10-09 13:06:05	job1	job.created
{ "job": {"key": "job3", "type": "job.stopped"}}	2022-10-09 13:06:50	job3	job.stopped
{"jobs": [{"key": "job1", "type": "job.started"}, {"key": "job2", "type": "job.completed"}]}	2022-10-09 13:09:31			job1 job2	job.started job.completed

If your raw data have such combinations, dereferencing jobs{}.key, etc., directly will give Splunk ambiguous multivalue results. You may need to detangle the array first, that is, use mvexpand to split jobs{}.

index=$tnkenvironment$ sourcetype=*json source=*webhook* $tknhost$ Type ="job.*" $tknProcessname$ $tknRobot$ $tknStatus$ | spath input=data path=jobs{} ``` extract the array as unitary field ``` | mvexpand jobs{} ``` creates an event for each element in array ``` | spath input=jobs{} ``` extract nodes in array element ``` | foreach jobs{}.* ``` iterate over each node ``` [eval <<MATCHSTR>> = coalesce(<<MATCHSTR>>, 'job.<<MATCHSTR>>')] | fields - jobs.* jobs{}.*

This way, each element in the array will be tabulated separately, and all JSON nodes can be coalesced without explicitly naming each of them. Using the above data as example, you'll get

_time	key	type
2022-10-09 13:06:05	job1	job.created
2022-10-09 13:06:50	job3	job.stopped
2022-10-09 13:09:31	job1	job.started
2022-10-09 13:09:31	job2	job.completed

Whether you use mvexpand depends on your presentation style, of course. But if you want to further perform statistics on the output, or to use drilldown on any of job attributes, mvexpand will be necessary.

Re: How to show all values of a field on the same table

din98 — Sun, 12 Mar 2023 13:02:51 GMT

Re: How to show all values of a field on the same table

gcusello — Tue, 11 Oct 2022 14:20:26 GMT

Hi @din98,

I don't know all your conditions, It's important for me that you understand the approach, then you can add all the conditions.

If you have an intersting value to add, you can put it with an additional condition.

Ciao.

Giuseppe