if one field is returning null value then how do i get all the fields to return null value

[ak9092]

Hi Guys,

I have one search query which is combining two Searches and giving results.

But based on the conditions set in query, sometimes one field doesn't return any results, so in such case I want other fields to also not return any results.

I am using the following stats command to combine the results of two Searches -

stats values(A) as A, values(B) as B, values(C) as C, values(D) as D by name

So if in case 'D' is null(not returned any results) then i want all the other fields also to return NO results.

Note - there are multiple values for A B C and D for one field "name".

For example -

name A B C D
foo 1 2 3 4
5 6 7
10 13 12 14

In the above results I would be wanting that 5 6 7 should not be included in results since D has not returned any value.

the problem is, when the above result are displayed in Splunk ,then the last result value for D i.e 14 is shifted up, so now the results are not accurate and it look likes below -

name A B C D
foo 1 2 3 4
5 6 7 14
10 13 12

What i want is

name A B C D
foo 1 2 3 4
10 13 12 14

Don't know whether this is possible or not as both are completely different searches with just one common field in them which is "name"
Also if it helps, values for D is written in one Search and values for A B and C are return in other.
Can someone please check on possibility for this ?
Thanks in advance.

[bboudreau_splun]

Hey @ak9092,

You could add a search command before your stats command to filter results to where D does exist, or you could add a where clause after your stats command to filter out the null values with isnotnull.

Something like:

| search D=*
| stats values(A) as A, values(B) as B, values(C) as C, values(D) as D by name

or

| stats values(A) as A, values(B) as B, values(C) as C, values(D) as D by name
| where isnotnull(D)

Hope that helps!

[richgalloway]

Have you tried

... | stats values(*) as * by name
| where (isnotnull(A) AND isnotnull(B) AND isnotnull(C) AND isnotnull(D)
| ...

[ak9092]

Hi @richgalloway & @bboudreau_splunk ,

Thanks both for your responses.
I guess I haven't frame my question appropriately, apologies for that.

So to add - under one "name" field there are multiple values for A B C and D , so if in some results D doesn't return any value then I want all the entries for A B and C to be excluded for that but keeping intact the other results which has returned all the values .

For example -

name A B C D
foo 1 2 3 4
5 6 7
10 13 12 14

In the above results I would be wanting that 5 6 7 should not be included in results since D has not returned any value.

the problem is, when the above result are displayed in Splunk ,then the last result value for D i.e 14 is shifted up, so now the results are not accurate and it look likes below -

name A B C D
foo 1 2 3 4
5 6 7 14
10 13 12

What i want is

name A B C D
foo 1 2 3 4
10 13 12 14

Don't know whether this is possible or not as both are completely different searches with just one common field in them which is "name"
Also if it helps, values for D is written in one Search and values for A B and C are return in other.

[bboudreau_splun]

Hey @ak9092,

If you could post your SPL, it would help me understand better.

I think you might be misunderstanding the values() command. The values in each column have no relation to each other aside from being related to the "name" (in this case, the name being "foo").
When you say the 14 is shifted up, it's just that the list of values for D is shorter.
The only row is for the name "foo", and the related values are listed in lexicographical order for each field (A, B, C, D).

Instead of using the stats command, the table command might be what you're looking for. It's difficult to say without seeing the query you're trying to perform.