Compare inputlookup values and look for partial match with a field from search

[fishn]

I have an inputlookup that has a list of pod names that we expect to be deployed to an environment. The list would look something like:

pod_name_lookup,importance

poda,non-critical

podb,critical

podc,critical

We also have data in splunk that gives us pod_name, status, and importance. Results from the below search would look like this:

index=abc sourcetype=kubectl 
| table pod_name, status, importance

poda-284489-cs834 Running non-critical

podb-834hgv8-cn28s Running critical

Note podc was not found..

I need to be able to compare the results from this search to the list from the inputlookup and show that podc was not found in the results and that it is a critical pod. Need to be able to count how many critical and non-critical pods are not found as well as table the list of missing pods. 

I have tried several iterations of searches but havent came across one that allows me to compare a search result to an inputlookup using a partial match. eval result=if(like(pod_name_lookup...etc is close but requires a pattern and not the wildcard value of a field.

Thoughts?

[yuanliu]

This ask could have two interpretations.  The simple one is extremely simple.  Let me give you the formula first.

| inputlookup pod_name_lookup where NOT
    [search index=abc sourcetype=kubectl
    | eval pod_name = mvindex(split(pod_name, "-"), 0)
    | stats values(pod_name) as pod_name]
| stats dc(pod_name) as count values(pod_name) as pod_name by importance

Your mock data will give you something like

pod_name	importance
podc	critical

Now, my interpretations of your use case.  First, I think your lookup table actually look like this, with pod_name as column name instead of pod_name_lookup.  Is this correct?

pod_name	importance
poda	non-critical
podb	critical
podc	critical

I call the lookup name "pod_name_lookup".  Second, I interpret the "pod_name" column in the lookup table, mocked up as "poda", "podb", "podc", to be the first part of running pod names (mocked up as "poda-284489-cs834" and "podb-834hgv8-cn28s") that does not contain a dash.  If this is not how the two names match, you will need to either make the transformation, or come up with more accurate mockups.

Now, I am assuming that 'importance" in lookup and events match exactly.  If you want to detect the discrepancies in "importance" as well, the search will be more complicated.

[fishn]

---

@yuanliu wrote:

This ask could have two interpretations.  The simple one is extremely simple.  Let me give you the formula first.

| inputlookup pod_name_lookup where NOT
    [search index=abc sourcetype=kubectl
    | eval pod_name = mvindex(split(pod_name, "-"), 0)
    | stats values(pod_name) as pod_name]
| stats dc(pod_name) as count values(pod_name) as pod_name by importance

This query gets me really close. The one edge case I did not bring up is that some pods have multiple parts of the expected name that are also split by dashes.

For example, I would have this in the lookup:

podd-unique-name

critical

and need to match

podd-unique-name-h98erg-n2439f

Running

critical

from the results.

Yes the "importance" in both will match exactly, but it is only important in the lookup field. The goal of this is to display pods that are not found in the search results compared to the inputlookup, and using the "importance" field from the lookup display the missing pod name and importance.

[bowesmana]

Start with this

index=abc sourcetype=kubectl 
| stats count by pod_name, status, importance
| rex field=pod_name "^(?<pod_name_lookup>pod.)"
| inputlookup append=t pod_lookup.csv
| fillnull value=0 count
| stats max(count) as count values(status) as status values(pod_name) as pod_name by pod_name_lookup, importance

this gets the data pods seen, creates the lookup name with rex, then appends the control lookup to the end, filling the count/status with 0 and something suitable.

The second stats just join them together on the lookup pod name and criticality.

If count is 0 then you do not have any pods of that variant. The pod_name and status fields will give you all the values seen for the pod_name in the data - use them if you need, otherwise remove them.

So, you can do 

| where count=0

to get the missing pods

[fishn]

Thanks for the response. Im not getting any matches though. Everything is coming back as count=0 even though there are entries in the lookup that should match.

[KendallW · Accepted solution]

Hi @fishn 

To match the partial string in the lookup (e.g. poda) with the data (e.g. "poda-284489-cs834"), you need to append each of the pod_name_lookup values with a wildcard asterisk, i.e. poda*, podb*, podc*

Then, add a lookup definition with the following setting, under the Advanced options checkbox:

Then in your search: (where lkp_pod_name is your lookup definition)

| lookup lkp_pod_name pod_name_lookup as pod_name

 ---

Next, to show which pods are missing and their importance, you can do it like this:

index=abc sourcetype=kubectl
| eval Observed=1
| append
[| inputlookup lkp_pod_name
| eval Observed=0 ]
| lookup lkp_pod_name pod_name_lookup as pod_name OUTPUT pod_name_lookup
| stats max(Observed) as Observed by pod_name_lookup, importance
| where Observed=0

 ---

Finally, to count how many critical and non-critical pods are not found as well as table the list of missing pods, you can append this line to the above search:

| eventstats count as count_by_importance by importance

[fishn]

This is perfect. Thank you very much!