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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...