The values of the table are made to come to the start of the column instead of filling zeros

[priyaramki16]

Hi,

I am writing a search to create 3 columns of data P,F and C based on Teams.

The table which I expect is this

Teams	P	C	F
team1	441	0	6
team2	4668	0	0
team3	2163	57	27

and the result table which i got is

Teams	P	C	F
team1	441	57	6
team2	4668		27
team3	2163

The search which i am using is

index="fq"
| where Status="P"
| stats count as P by Teams
| fillnull value=0 P
| appendcols
[ search index="fq"
| where Status="F" 
| stats count as F by Teams
| fillnull value=0 F]
| appendcols
[ search index="fq"
| where Status="C"
| stats count as C by Teams
| fillnull value=0 "Covered"]

Used fillnull too..But it did not work

Kindly help me with this.

[richgalloway · Accepted solution]

The problem here lies with the appendcols command.  Appendcols matches events in the order they occur without regard for field values.  IOW, if the first search returns 3 events and the second search returns one event then appendcols will pair the single return from search2 with the first event of search1.  One fix for that is to use append rather than appendcols and allow stats to match up the fields.

index="fq"
| where Status="P"
| stats count as P by Teams
| fillnull value=0 P
| append
[ search index="fq"
| where Status="F" 
| stats count as F by Teams]
| append
[ search index="fq"
| where Status="C"
| stats count as C by Teams]
| stats values(*) as * by Teams
| fillnull value=0

An even better solution is to avoid append completely.

index="fq" (Status="P" OR Status="F" OR Status="C")
| stats count(eval(Status="P")) as P, count(eval(Status="F")) as F, count(eval(Status="C")) as C by Teams

[priyaramki16]

@richgalloway Thank you so much for the quick reply!! It worked!!