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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...