Define column results dependent on another search

[jmheaton]

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.

[somesoni2 · Accepted solution]

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]

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

[kristian_kolb]

see update above

[jmheaton]

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