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!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...