Splunk Search

Define column results dependent on another search

jmheaton
Path Finder

So i want to create a table where i define the first column and then a search fills in results in the second column. I am sure i am missing something incredibly easy but i'm otherwise stumped.

Basicly i want something like this

Search for sourcetype=* | stats count(field) by sourcetype

sourcetype1 17
sourcetype2 0
sourcetype3 14

Right now i am getting sourcetype 1 and 3 reporting.
I cant get it to show when there are no results, where i want it to define a 0 when nothing is found.

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

You said "you want to create a table where i define the first column and then a search fills in results in the second column". So first get your first column data ready, either in a lookup file or by using data for a slightly larger period.

first portion-

sourcetype=* earliest=-30d@d| stats count by sourcetype| fields - count

OR

|inputlookup yoursourcetypelookupfile

This should give list of all the sourcetype for which you want to generate data/count.

Then join the same with your original query, to get the count and fill the null values as 0.

sourcetype=* earliest=-30d@d| stats
count by sourcetype| fields - count |
join type=outer sourcetype [search
sourcetype=*|stats count by
sourcetype] | eval
count=coalesce(count,0)

OR

|inputlookup yoursourcetypelookupfile|
join type=outer sourcetype [search
sourcetype=*|stats count by
sourcetype] | eval
count=coalesce(count,0)

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

You said "you want to create a table where i define the first column and then a search fills in results in the second column". So first get your first column data ready, either in a lookup file or by using data for a slightly larger period.

first portion-

sourcetype=* earliest=-30d@d| stats count by sourcetype| fields - count

OR

|inputlookup yoursourcetypelookupfile

This should give list of all the sourcetype for which you want to generate data/count.

Then join the same with your original query, to get the count and fill the null values as 0.

sourcetype=* earliest=-30d@d| stats
count by sourcetype| fields - count |
join type=outer sourcetype [search
sourcetype=*|stats count by
sourcetype] | eval
count=coalesce(count,0)

OR

|inputlookup yoursourcetypelookupfile|
join type=outer sourcetype [search
sourcetype=*|stats count by
sourcetype] | eval
count=coalesce(count,0)

kristian_kolb
Ultra Champion

fillnull is probably what you want.

... | fillnull | ...

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Fillnull

"Replaces null values with a user
specified value (default 0). Null
values are those missing in a
particular result, but present for
some other result. If a field-list is
provided, fillnull is applied to only
fields in the given list (including
any fields that does not exist at
all). Otherwise, applies to all
existing fields."


UPDATE:

I think you should perhaps post a few sample events. However, I do get your initial search to work fine;

index=_internal earliest=-1m | stats count(Bogus) as BogusCount by sourcetype 

There is no field called Bogus in my _internal index, and this gets reported as;

sourcetype           BogusCount
splunkd              0
splunk_web_access    0
splunkd_access       0

For a slightly more real search we will look for the field "status", which exist in some events;

index=_internal earliest=-1m | stats count(status) as StatusCount by sourcetype

and the result is;

sourcetype           StatusCount
splunkd              0
splunk_web_access    1
splunkd_access       6

A more explicit way might be to look for your counts like this;

index=_internal earliest=-1m | stats count(eval(component="LicenseUsage")) as LicenseUsage, count(eval(component="ExecProcessor")) as ExecProcessor, count(eval(component="Metrics")) as Metrics, count(eval(component="Bogus")) as Bogus  

/K

0 Karma

kristian_kolb
Ultra Champion

see update above

0 Karma

jmheaton
Path Finder

That in a way is what i want to happen, but i want to list out column X and return results for column Y.
Right now if no results are found the field doesn't populate in column X.

I want to list out column X completely, and compare restults to it and list results in Y.

Now:
X1 Y1
X2 Y2
X3 Y3
X5 Y5

Wanted:
X1 Y1
X2 Y2
X3 Y3
X4 0
X5 Y5

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...