Splunk Search

appendcols with proper info

jugarugabi
Path Finder

Hi, 

Following search query produces output in table below:

index=_pods  pod=* project=project_name state="Running"
| eventstats latest(_time) as current | where current=_time
| stats count as Active by pod
| rename pod as "Pod Name"
| table "Pod Name" "Active"
| fillnull "Active"

Output:

 Pod NameActive
1pod52
2pod12
3pod32

 

Now, I am having a csv file with the following info:

pod,Expected
pod1,2
pod5,100
pod3,2

 I want to add the "Expected" row from the csv file into the search output. 

So far I have done: 
index=_pods  pod=* project=project_name state="Running"
| eventstats latest(_time) as current | where current=_time
| stats count as Active by pod
| appendcols [| from inputlookup:"lookup_expected_pods_dk0766.csv"]
| rename pod as "Pod Name"
| table "Pod Name" "Active" "Expected"
| fillnull "Active"

But the output is:

 pod nameActiveExpected
1pod522
2pod12100
3pod322

 

As it can be seen, the value Expected=100 should be for pod5 (csv file), but the output is showing 100 to pod1 in splunk. 

I have no idea how to have inputlookup to search for the pod name in the csv file and have the "Expected" value added to the proper pod name. 

How should I adjust the search query to have the needed output?

Also, is there a possiblity to highlight with a color differences between active and expected?

Thanks

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

You've run into the appendcols trap.  That command attaches its results to the results of the main search in the order they occur.  There is no matching of field values within the events.  Instead, try using append and then grouping the events by pod name.

index=_pods  pod=* project=project_name state="Running"
| eventstats latest(_time) as current | where current=_time
| stats count as Active by pod
| append [| from inputlookup:"lookup_expected_pods_dk0766.csv"]
| stats values(*) as * by pod
| rename pod as "Pod Name"
| table "Pod Name" "Active" "Expected"
| fillnull "Active"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You've run into the appendcols trap.  That command attaches its results to the results of the main search in the order they occur.  There is no matching of field values within the events.  Instead, try using append and then grouping the events by pod name.

index=_pods  pod=* project=project_name state="Running"
| eventstats latest(_time) as current | where current=_time
| stats count as Active by pod
| append [| from inputlookup:"lookup_expected_pods_dk0766.csv"]
| stats values(*) as * by pod
| rename pod as "Pod Name"
| table "Pod Name" "Active" "Expected"
| fillnull "Active"
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Enterprise 9.4: Features to Power Your Digital Resilience

Hey Splunky People! We are excited to share the latest updates in Splunk Enterprise 9.4. In this release we ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...