Python script to read Splunk data

[bergen288]

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"

[bergen288]

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.

[richgalloway]

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.

[bergen288]

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

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. 

[richgalloway]

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.7...

[bergen288]

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

"""

[richgalloway]

The SDK lets you choose the output format.  See https://dev.splunk.com/enterprise/docs/devtools/python/sdk-python/howtousesplunkpython/howtodisplays...

[bergen288]

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.

[bergen288]

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.

[bergen288]

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

Thanks.

[richgalloway]

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/howtoconnectp...for assistance.

[bergen288]

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.