How to create Inner Join with subsearch using Splunk Python?

[RBolconte]

I'm doing a main search of a sourcetype, then I need to join with a csv file using the inputlookup, both the main search and the subsearch have the `Name` column, but when sending the complete search through the api, it does not return the values correctly, but when I do the search manually in splunk it works correctly.

import splunklib.client as client

service = client.connect(host=host, port=port, username=user, password=password)

search = '''search''' +  '''index="aiops_main" sourcetype="scom_np" OR sourcetype="scom_p" type="*SQL*" AND (type="*AlwaysOn*" OR type="*Server Service Stopped*")
| join type=left Name
    [| inputlookup maintenance_window.csv max=0
    | eval Name=lower(Name)
    | table Name, maint_down_start_time, maint_down_end_time, change_ticket]
| eval is_maintenance = if((alwayson_failovertime >= maint_down_start_time) AND alwayson_failovertime < maint_down_end_time,"true","false")
| table Name, type, is_maintenance
'''

kwargs_export = {
    "earliest_time": '1',
    "latest_time": "now",
    "search_mode": "normal",
    "exec_mode": "blocking",
}

# Create job and return results
try:
    job = service.jobs.create(search, parse_only=False, **kwargs_export)
    print(time.strftime('\n%Y_%m_%d__%H:%M:%S'))
    print("...done!")

except Exception as e:
    print("Trouble connecting to Splunk. Try again in a few seconds")
    raise e

This error appears: "INFO: [subsearch]: Your timerange was substituted based on your search string"

In short: the is_maintenance field when run manually in Splunk returns some lines as True, while running the same search in python returns all as False.

[burwell]

Hi.  So when you test you are specifying a time range, I assume (not all time).

But for the python api search if you don't pass in earliest and latest, wouldn't it do an all time search?

[RBolconte]

If I take the earliest_time and latest_time from kwargs_export, it remains the same, without joining the information from maintenance_window.csv

[burwell]

Hi. Sorry I missed that you had passed in earliest and latest.

A few ideas

1) can you create a saved search and then run that. e.g. savedsearch "mysavedsearch"

2) is the splunk search you are running with username and password got the permission to view the lookup table?

3) break down the search to find the issue e.g. maybe just run the inputlookup with a | stats count to make sure that part is working?

4) if you are an admin, you could look in the audit log to find out the number of results returning from your search

[RBolconte]

I just ran the csv search and it actually returned nothing (Splunk API):

| inputlookup maintenance_window.csv max=0
    | eval Name=lower(Name)
    | table Name, maint_down_start_time, maint_down_end_time, change_ticket

However, in splunk I can return the values through manual search, that is, the user has access to the search. Can you tell if it's something from the api?

[burwell]

Maybe the inputlookup is restricted to permission in a given app. You are using that app in your splunk search via the web.

The api is running the search app? Just an idea.

[burwell]

If I try searching for a lookup that can't be found I get

ERROR SearchMessages - orig_component="SearchOperator:inputcsv" app="search" sid="1692127130.1422" message_key="INPUT_CSV:INVALID_LOOKUP_TABLE_TYPE" message=The lookup table 'maintenance_window.csv' requires a .csv or KV store lookup definition.

 this in /opt/splunk/var/log/splunk/search_messages.log

[RBolconte]

I ran it without the "search" at the beginning of the string just using the inputlookup and it worked, now I just need to understand how to make the inputlookup work as a subsearch, is it possible with savedsearch? Not sure what this is, I'll look into it.

[burwell]

Hi so why not just put the whole search as a saved search: the search part with the subsearch. It is simpler.

Then run the saved search as i suggested 

| savedsearch "mysaved_search_name"

Don't put search at the beginning.