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!

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 ...