topic Re: Python script to read Splunk data in Splunk Search

Python script to read Splunk data

bergen288 — Mon, 27 Sep 2021 18:08:31 GMT

I need to collect Specific Splunk data for business analysis. My target URL is https://splunk.usce.l.az.fisv.cloud/en-US/app/epayments/postpayee_success_and_failure?form.SponsorId=*&form.SubscriberId=*&form.CorrelationId=*&form.Status=*&form.Exception=-&form.timespan.earliest=-7d%40h&form.timespan.latest=now. After login with my username/password, it will show "Post Payee Exception List".

I am trying to write a Python script to read Splunk data in last 7 days. Below is my code:

session = requests.Session()

response = session.post(LOGIN_URL, auth = HTTPBasicAuth(user, password), verify=False)

print(response.status_code)

The user/password are the same ones for Web access and the LOGIN_URL is 'https://splunk.usce.l.az.fisv.cloud/en-US/account/login?return_to=%2Fen-US%2F' However, the response status code is 401 which is a failure. What's the correct Python way to login to Splunk website?

In addition, I am trying to connect to Splunk server with Splunk-SDK package via port 8089. Below is my Python code:

import splunklib.client as client

import splunklib.results as results

HOST = "splunk.usce.l.az.fisv.cloud"

PORT = 8089

credentials = get_splunk_pwd()

username = credentials['username']

password = credentials['password']

service = client.connect(

host=HOST,

port=PORT,

username=username,

password=password)

print(service)

rr = results.ResultsReader(service.jobs.export("search index=_internal earliest=-24h | head 5"))

for result in rr:

if isinstance(result, results.Message):

# Diagnostic messages might be returned in the results

print('%s: %s' % (result.type, result.message) )

elif isinstance(result, dict):

# Normal events are returned as dicts

print(result)

Below is the output. It looks like the Splunk connection is established successfully. But the serarch is invalid. What's the valid search string based on my target URL in 1st line?

<splunklib.client.Service object at 0x0000029461421790>

DEBUG: Configuration initialization for /opt/splunk/etc took 91ms when dispatching a search (search ID: 1632765670.57370_31B6A7A0-BF6B-46EF-BD46-2CF0D6AB351A)

DEBUG: Invalid eval expression for 'EVAL-SessionDateTime' in stanza [source::dbmon-tail://*/CCAuditLogSelect]: The expression is malformed. An unexpected character is reached at '“%Y-%m-%d %H:%M:%S.%3N”)'.

DEBUG: Invalid eval expression for 'EVAL-TrxDateTime' in stanza [source::dbmon-tail://*/CCAuditLogSelect]: The expression is malformed. An unexpected character is reached at '“%Y-%m-%d %H:%M:%S.%3N”)'.

DEBUG: base lispy: [ AND index::_internal ]

DEBUG: search context: user="xzhang", app="search", bs-pathname="/opt/splunk/etc"

Re: Python script to read Splunk data

richgalloway — Mon, 27 Sep 2021 20:26:43 GMT

None of the DEBUG messages shown indicate a problem with the search query. Two of them refer to errors in a props.conf file ("Invalid eval expression") and the others are just informational. What leads you to believe there's something wrong with the query?

The error 401 indicates you're not passing your login credentials correctly. See https://dev.splunk.com/enterprise/docs/devtools/python/sdk-python/howtousesplunkpython/howtoconnectpython/ for assistance.

Re: Python script to read Splunk data

bergen288 — Tue, 28 Sep 2021 14:18:54 GMT

First, I don't see any valid search result with print(result) statement. My key question is how to define search string for https://splunk.usce.l.az.fisv.cloud/en-US/app/epayments/postpayee_success_and_failure?form.SponsorId=*&form.SubscriberId=*&form.CorrelationId=*&form.Status=*&form.Exception=-&form.timespan.earliest=-7d%40h&form.timespan.latest=now after Splunk client connection? Second, I don't see Splunk website login example in your link?

Thanks.

Re: Python script to read Splunk data

richgalloway — Thu, 30 Sep 2021 13:08:35 GMT

You don't get any results back because of the error 401, which indicates an authentication failure. Fixing the search query will not change that.

Authentication is done by the client.connect call. Carefully compare your code to that in the examples at dev.splunk.com.

Re: Python script to read Splunk data

bergen288 — Thu, 30 Sep 2021 13:25:39 GMT

Sorry for the confusion. I am trying with 2 different approaches with the same login credentials. The 1st one is regular Web access with failed 401 error and the 2nd one is connection via Splunk-SDK client which is successful. It is confirmed with <splunklib.client.Service object at 0x0000013682881790> for print(service) statement. For my 1st Web access connection, my question is how to login Spunk website correctly. For my 2nd Splunk client connection, my question is how to modify its "search" string to get correct results. I am fine with either one.

Re: Python script to read Splunk data

bergen288 — Thu, 30 Sep 2021 13:28:11 GMT

Sorry, I replied to your previous response. Here you go again:

Re: Python script to read Splunk data

richgalloway — Thu, 30 Sep 2021 15:15:26 GMT

I think that rather than the job.export command, you want job.results command. See https://docs.splunk.com/Documentation/Splunk/8.2.2/RESTREF/RESTsearch#search.2Fjobs.2F.7Bsearch_id.7D.2Fresults

Re: Python script to read Splunk data

bergen288 — Fri, 29 Oct 2021 20:34:58 GMT

My application developer gives me correct Splunk search string (see below), but its output is in

<class 'collections.OrderedDict'> format which is pretty ugly. Is there a way to define output in CSV format?

Thanks.

SEARCH_STRING = """

search index=pivotal cf_app_name=ips-challenger-challengerapi-* "*PostPayeeAsync*"

earliest=-2d latest=-d@d

msg.Properties.LoggingTemplate.Exception !="*SubscriberStatus*"

| eval Message='msg.Properties.LoggingTemplate.Message'

| eval SponsorId ='msg.Properties.LoggingTemplate.TenantId'

| eval SubscriberId = 'msg.Properties.LoggingTemplate.UserId'

| eval Exception = 'msg.Properties.LoggingTemplate.Exception'

| eval CorrelationId = 'msg.Properties.LoggingTemplate.AdditionalInformation.CorrelationId'

| eval SessionId='msg.Properties.LoggingTemplate.AdditionalInformation.SessionId'

| eval PayeeName= 'msg.Properties.LoggingTemplate.AdditionalInformation.PayeeName'

| eval Address= 'msg.Properties.LoggingTemplate.AdditionalInformation.Address'

| eval MerchantType= 'msg.Properties.LoggingTemplate.AdditionalInformation.MerchantType'

| eval MerchantId= 'msg.Properties.LoggingTemplate.AdditionalInformation.MerchantId'

| eval AccountNumber= 'msg.Properties.LoggingTemplate.AdditionalInformation.AccountNumber'

| sort _time

| table _time,SponsorId,SubscriberId,Message,Exception,CorrelationId,SessionId,PayeeName,Address,MerchantType,MerchantId,AccountNumber

"""

Re: Python script to read Splunk data

richgalloway — Sat, 30 Oct 2021 12:13:03 GMT

The SDK lets you choose the output format. See https://dev.splunk.com/enterprise/docs/devtools/python/sdk-python/howtousesplunkpython/howtodisplaysearchpython#The-search-results-APIs

Re: Python script to read Splunk data

bergen288 — Mon, 01 Nov 2021 14:04:22 GMT

I tried both rr = results.ResultsReader(service.jobs.export(SEARCH_STRING, **{"output_mode": "CSV"})) and rr = results.ResultsReader(service.jobs.export(SEARCH_STRING, output_mode="CSV")). Both give me the following invalid format CSV error:

Traceback (most recent call last):

File "e:\Python_Projects\Payees\Code\get_splunk_sdk.py", line 43, in <module>

rr = results.ResultsReader(service.jobs.export(SEARCH_STRING, **{"output_mode": "CSV"}))

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\client.py", line 2989, in export

return self.post(path_segment="export",

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\client.py", line 821, in post

return self.service.post(path, owner=owner, app=app, sharing=sharing, **query)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\binding.py", line 290, in wrapper

return request_fun(self, *args, **kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\binding.py", line 71, in new_f

val = f(*args, **kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\binding.py", line 764, in post

response = self.http.post(path, all_headers, **query)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\binding.py", line 1242, in post

return self.request(url, message)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\binding.py", line 1262, in request

raise HTTPError(response)

splunklib.binding.HTTPError: HTTP 400 Invalid output mode specified (CSV). -- Invalid output mode specified (CSV).

If I try the following code:

rr = results.ResultsReader(service.jobs.export(SEARCH_STRING, output_mode="csv"))

for result in rr:

print(result)

It seems OK with "rr" statement, but gives me the following error:

Traceback (most recent call last):

File "e:\Python_Projects\Payees\Code\get_splunk_sdk.py", line 47, in <module>

for result in rr:

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\results.py", line 210, in next

return next(self._gen)

File "C:\ProgramData\Anaconda3\lib\site-packages\splunklib\results.py", line 219, in _parse_results

for event, elem in et.iterparse(stream, events=('start', 'end')):

File "C:\ProgramData\Anaconda3\lib\xml\etree\ElementTree.py", line 1227, in iterator

yield from pullparser.read_events()

File "C:\ProgramData\Anaconda3\lib\xml\etree\ElementTree.py", line 1302, in read_events

raise event

File "C:\ProgramData\Anaconda3\lib\xml\etree\ElementTree.py", line 1274, in feed

self._parser.feed(data)

xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 6, column 101

I also tried to add "|outputcsv myoutput.csv" inside my SEARCH_STRING, I don't know where is its location on Windows Server 2016?

By the way, your document is pretty hard to understand. Do you mind to give me direct answer next time?

Thanks.

Re: Python script to read Splunk data

bergen288 — Mon, 01 Nov 2021 14:22:00 GMT

The key question is that the default output in <class 'collections.OrderedDict'> format is ugly and hard to convert to pandas dataframe. The output in CSV format is much easier to load into dataframe. If there is new way to convert output to dataframe, I don't mind what output format it is.

Thanks.

Re: Python script to read Splunk data

bergen288 — Mon, 01 Nov 2021 14:39:10 GMT

Don't worry, I found a way to load OrderedDict data into dataframe.

Thanks.