appendcols with proper info

[jugarugabi]

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 Name	Active
1	pod5	2
2	pod1	2
3	pod3	2

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 name	Active	Expected
1	pod5	2	2
2	pod1	2	100
3	pod3	2	2

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

[richgalloway · Accepted solution]

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"