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.
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)
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)
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
see update above
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