Add dummy row with 0 when count returns 0

[jip31]

Hi

I  need to display 0 in the field "Number of index" if there is no host in this index

I tried but I have no results

Could you help me please??

| tstats count where (index=agd-*) by host index 
| join host 
    [| inputlookup fo_all] 
| stats dc(index) as "Number of index" by host 
| appendpipe 
    [| stats count as countKO
    | where countKO = 0 ] 
| eval countKO=if(countKO=0,"No host in index",countKO) 
| table countKO

[kamlesh_vaghela]

@jip31 

Are you looking something like this?

| tstats count where (index=agd-*) by host index 
| append
    [| inputlookup fo_all] 
| stats dc(index) as countKO by host | eval countKO=if(countKO=0,"No host in index",countKO)

[jip31]

Hi

Your code works but I have "No host in index" values and it's not normal...

So i a m always unable to display host with count = 0....

[kamlesh_vaghela]

@jip31 

Can you please share your expected OP if there is no host in index?? As there are no rec in tstats  search and I think lookup has only host values (correct me if my guessing is wrong).  What should be Output... !!?

KV

[jip31]

Is anybody can't help?

[inventsekar]

Hi @jip31 ... the if condition was wrong.. pls try this.. (i ran without the inputlookup, when you run it with the inputlookup, it should just be fine i think)

| tstats count where (index=agd-*) by host index  
| stats dc(index) as "Number of index" by host 
| appendpipe 
    [| stats count as countKO
    | where countKO = 0 ] 
| eval countKO=if(countK0="No host in index", "countKO=0", countKO) 
| table countKO

if this resolves your query, pls accept it as answer.. upvote would be appreciated. thanks.

[jip31]

unfortunately it doesnt works

And i think it's countKO="0" instead "countKO=0" ??

 what I need is to display 0 in the "Number of index" field if the hostname doesnt exists in the index