Splunk Search

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

fishn
Explorer

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?

 

 

 

Labels (1)
0 Karma
1 Solution

KendallW
Path Finder

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:

KendallW_0-1713233903623.png

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

 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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_nameimportance
podccritical

 

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_nameimportance
podanon-critical
podbcritical
podccritical

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.

Tags (2)

fishn
Explorer

@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-namecritical

 

and need to match

podd-unique-name-h98erg-n2439fRunningcritical


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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

KendallW
Path Finder

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:

KendallW_0-1713233903623.png

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
Explorer

This is perfect. Thank you very much!

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...