topic Re: tstats missing row for missing data in Splunk Search

tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 10:18:44 GMT

tstat works great when there is at least 1 event per day( span=1d).
but when there is no data inserted, it completely ignores that date .

but with timechart we do get a 0 for dates missing data.

how can i get similar output with tstat.. output should show 0 for missing dates..

Re: tstats missing row for missing data

spj1oct — Mon, 13 Jan 2020 11:04:19 GMT

set fillnull_value=0

more information on https://docs.splunk.com/Documentation/Splunk/8.0.1/SearchReference/Tstats

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 11:45:41 GMT

what's your query?

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 11:51:19 GMT

|tstats count where (index=name1 sourcetype=s1 earliest=-8d@d latest=-1d@d) groupby index,sourcetype,_time span=1d|reverse

This is giving me 5 rows , but its missing 8th and 9th date since on those days indexing failed...
i need to see those date rows with other column data (index sourcetype ) and the count should be 0

index sourcetype _time count
name1 s1 2020-01-11 18765
name1 s1 2020-01-10 37575
name1 s1 2020-01-07 18791
name1 s1 2020-01-06 18789
name1 s1 2020-01-05 18788

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 11:55:55 GMT

UPDATE(multiple index):

 | tstats count prestats=t where index=name1 ( sourcetype=s1 OR sourcetype=s2 ) earliest=-8d@d latest=-1d@d by _time sourcetype span=1d
| timechart span=1d count by sourcetype
| untable _time sourcetype count
| eval index="name1"
| reverse
| table index sourcetype _time count
| append [  | tstats count prestats=t where index=name2 ....
....
|eval index="name2"
.... ]
| append [ | tstats count prestats=t where index=name3 ..... ]

It is better to search and append each index that has been added.

UPDATE(Display index):

| tstats count prestats=t where index=name1 ( sourcetype=s1 OR sourcetype=s2 ) earliest=-8d@d latest=-1d@d by _time sourcetype span=1d
| timechart span=1d count by sourcetype
| untable _time sourcetype count
| eval index="name1"
| reverse
| table index sourcetype _time count

Hi, @jiaqya
How about this?

Display index:

| tstats count prestats=t where index=name1 ( sourcetype=s1 OR sourcetype=s2 ) earliest=-8d@d latest=-1d@d by _time sourcetype index span=1d
| stats count by _time index sourcetype 
| reverse
| timechart span=1d values(count) as count values(index) as index by sourcetype
| fields - "index: s2"
| rename "index: s1" as index
| rename "count: *" as *
| filldown index
| fillnull
| eval tmp=index.","._time
| fields - index _time
| untable tmp sourcetype count
| eval _time=mvindex(split(tmp,","),1) , index=mvindex(split(tmp,","),0)
| table index sourcetype _time count

maybe works.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 11:59:55 GMT

for the question, this stays answered.

Thanks, this works if its for a single index/stype , will this also work if there are multiple stypes ?
suppose i have another sourcetype s2

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 12:04:15 GMT

@jiaqya
my answer is updated. please confirm.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 12:06:16 GMT

Thanks for helping me on this.

the final output should be similar to below , with index,stype values along with 0 dated records..

index sourcetype _time count
name1 s1 2020-01-11 18765
name1 s1 2020-01-10 37575
name1 s1 2020-01-07 18791
name1 s1 2020-01-06 18789
name1 s1 2020-01-05 18788

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 12:14:06 GMT

Why is the index displayed?
No index is displayed for my answer query.

_time s1 s2
2020-01-11 **** ****
2020-01-10 **** ****
2020-01-09 **** ****
2020-01-08 **** ****
2020-01-07 **** ****
2020-01-06 **** ****
2020-01-05 **** ****

I think the result is.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 12:50:28 GMT

but i need the index and sourcetype displayed for further processing...

i am getting the desired output with tstats , but without the 0 value records, need the 0 value records also in same format.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 12:52:30 GMT

Error in 'stats' command: The argument 'fillnull_value=0' is invalid

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 12:56:40 GMT

@jiaqya
my answer is updated.
please confirm.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 13:15:49 GMT

Thanks for working this, this does work if we consider only 2 source types with exactly the same tabular format.
But in future we may have a lot of sourcetypes , then it will be a problem to update the code for so many sourcetypes

is there a better way to handle this, im sorry if i could not put my question correctly ..

probably also consider multiple indexes and multiple source types...

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 13:16:30 GMT

The fillnull_value option of tstats is used when a field has no value.

In this case, the _time field value is not missing and cannot be used because it is missing as a result of aggregation.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 13:18:32 GMT

Noted... Understood..

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 13:24:48 GMT

with my second query(maybe works), please change index and sourcetype:
(index=name1 OR index=name2 ...) (sourcetype=s1 OR sourcetype=s2 OR sourcetype=s3 ...)

It doesn't matter how many sourcetypes you have.
As the index increases, lines 5 and 6 may need to be modified.

Please accept my answer.

Re: tstats missing row for missing data

jiaqya — Mon, 13 Jan 2020 13:26:50 GMT

This does resolve my problem for now, but once the indexes/stypes increase can look at it later. thanks..

Re: tstats missing row for missing data

to4kawa — Mon, 13 Jan 2020 14:14:39 GMT

you're welcome.
In multiple indexes case, there is easy way that use append.
my answer is updated.

happy splunking.