Splunk Search

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

priyaramki16
Path Finder

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

TeamsPCF
team144106
team2466800
team321635727

and the result table which i got is

TeamsPCF
team1441576
team24668 27
team32163  

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.

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

 

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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

 

 

---
If this reply helps you, Karma would be appreciated.

priyaramki16
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...