SPLUNK output from search query

[abidkar]

Hello,

I am trying to search the splunk log but I am getting the output in payload format. is there a way I can get it in tabular format instead of payload which I can use to directly insert in the table? Can someone please help?

Thanks in advance!

Avanti

[richgalloway]

We need more information to better help you.  How exactly are you running the query?  Is it in the UI, SDK, API, or other means?  What is a "payload format"?  The UI will display results in table form by default so if you're not getting that then we'll need to hear about what you're doing and the results you get.  A screenshot may be helpful.

[abidkar]

Here is the out put I am getting

{"socClassifVvCode":"XX","logicalDate":"20210518","billingAccountId":"XXXXXXXXX","lastUpdateDate":"20210518181503","msisdn":null,"subStatus":null,"lastUpdateStamp":2245,"deepEventName":"XXXXXXXXXX","deepEventId":"2XXXXXXXX","action":"XXX","effectiveDate":"2021-05-18T17:00:00.000Z","channelId":"XX","productType":null,"requiredSoc":null} ]

However I want in tabular format column wise for e.g.

ClassifCode|Date|AcctID

XX|XX|XXXX

I may not be able to paste the actual query but sample encrypted one is as below:

index=adms RestLoggingUtil XXXXXXXXXXX "/XXXXXXXXXXXXXX"| table BAN, PAYLOAD

[ITWhisperer]

Use spath to extract the fields from the payload

[abidkar]

Do you mean use spath in my search query after payload?

Can you please help me with the syntax?

Thanks for your help!

[dhirendra761]

<your search>| spath | table *

Hi @abidkar  In place of * , you can mentioned the field name which you want

[abidkar]

Thanks for all your help but I am still getting the same output. I tried both ways:

"My Search"| table BAN, PAYLOAD | spath

"My Search"| spath | table BAN, PAYLOAD  Still the output is same.

[ITWhisperer]

You are not giving us much to work with!

Is PAYLOAD a field which holds the json you are trying to extract from? if so, the syntax for spath will be something like this

index=adms RestLoggingUtil XXXXXXXXXXX "/XXXXXXXXXXXXXX"
| spath input=PAYLOAD
| table BAN ClassifCode Date AcctID

[abidkar]

Here is my updated query;

index=adms RestLoggingUtil XXXXXXXXXXXXXX "/billing/v1/update-soc" |rex "billingAccountId \":\"(?<BAN>\d+)"| spath input=PAYLOAD | table socClassifVvCode, billingAccountId

and also getting following error

5 errors occurred while the search was executing. Therefore, search results might be incomplete. Hide errors.

• Unknown error for indexer: prdtlplnk0113. Search Results might be incomplete! If this occurs frequently, check on the peer.
• Unknown error for indexer: prdtlplnk0117. Search Results might be incomplete! If this occurs frequently, check on the peer.
• Unknown error for indexer: prdtlplnk0132. Search Results might be incomplete! If this occurs frequently, check on the peer.
• [prdplplnk0155] Error in 'DispatchCommandProcessor': Search results may be incomplete, peer prdplplnk0155's search ended prematurely. Error = Peer prdplplnk0155 will not return any results for this search, because the search head is using an outdated generation (search head gen_id=6093030; peer gen_id=6093032). This can be caused by the peer re-registering and the search head not yet updating to the latest generation. This should resolve itself shortly.
• [prdplplnk016d] Error in 'DispatchCommandProcessor': Search results may be incomplete, peer prdplplnk016d's search ended prematurely. Error = Peer prdplplnk016d will not return any results for this search, because the search head is using an outdated generation (search head gen_id=6093030; peer gen_id=6093036). This can be caused by the peer re-registering and the search head not yet updating to the latest generation. This should resolve itself shortly

Original search and output:

Hope this helps.

and Appreciate all your inputs and help!

Thanks 

Avanti

[ITWhisperer]

Try trimming PAYLOAD to make it a single object instead of a collection with a single object in.

| makeresults 
| eval PAYLOAD="[ {\"socClassifVvCode\":\"XX\",\"logicalDate\":\"20210518\",\"billingAccountId\":\"XXXXXXXXX\",\"lastUpdateDate\":\"20210518181503\",\"msisdn\":null,\"subStatus\":null,\"lastUpdateStamp\":2245,\"deepEventName\":\"XXXXXXXXXX\",\"deepEventId\":\"2XXXXXXXX\",\"action\":\"XXX\",\"effectiveDate\":\"2021-05-18T17:00:00.000Z\",\"channelId\":\"XX\",\"productType\":null,\"requiredSoc\":null} ]"
| eval PAYLOAD=trim(PAYLOAD,"[] ")
| spath input=PAYLOAD

[abidkar]

Thanks a bunch to both of you for helping me on this one. It really makes it easy for me in this format. My next plan is to update my python code to directly insert this data in the table. If you have any other suggestions, please let me know.

Once again Thanks a bunch for all your help!

Thanks 

Avanti

[dhirendra761]

Also please make sure that key will be exist in json before extracting it.

I am not sure where is BAN and PAYLOAD in json

[abidkar]

Hello Team,

Need help with one more thing, is it possible to retrieve the data from SPLUNK search from a date? For e.g if I need the payload logs from April is it possible to download them?

Thanks for all your help!

Thanks and Regards,

Avanti

[richgalloway]

I still don't know what a "payload log" is, but it should be possible to retrieve those from April.  Just use the time picker to select "Date Range", choose the beginning and end of April, then click Apply.

[abidkar]

Hi  Thanks for your response.

Here is my sample search query:

index=adms RestLoggingUtil XXXXXXXXXXXXXX "/billing/v1/update-soc" |rex "billingAccountId \":\"(?<BAN>\d+)"| spath input=PAYLOAD | table socClassifVvCode, billingAccountId

The output of the search is payload data hence I mentioned payload in my earlier post.

so the time picker should be part of my search query correct?

Just wanted to confirm before trying it.

Thanks for your help!

Thanks 

Avanti

[dhirendra761]

No. time picker is not part of your search query.

You can select time range in button itself. refer attached image

[abidkar]

Thanks this did worked. however if I have to download the logs from my python code how can I use this date range option?

currently I am doing this:

service = client.connect(host=HOST, port=PORT, username=USERNAME, password=PASSWORD)

            kwargs_oneshot = {"earliest_time": "-60min","latest_time": "now","search_mode": "normal", "output_mode": "csv"}

and planning to extend it to -24H but is it possible to provide a date range here?

Thanks and Regards,

Avanti

[abidkar]

Also I am facing another issue. The below code is just bringing 102 records however if download form the tool there 4000+ records

        service = client.connect(host=HOST, port=PORT, username=USERNAME, password=PASSWORD)

            kwargs_oneshot = {"earliest_time": "-24h","latest_time": "now","search_mode": "normal", "output_mode": "csv"}

            searchquery_oneshot = r'search index=adms RestLoggingUtil FeatureChangeInitiated "/billing/v1/update-soc" |rex "billingAccountId  \":\"(?<BAN>\d+)"| eval PAYLOAD=trim(PAYLOAD,"[] ") | spath input=PAYLOAD |  table socClassifVvCode, logicalDate, billingAccountId, msisdn'

            oneshotsearch_results = service.jobs.oneshot(searchquery_oneshot, **kwargs_oneshot)

        except Exception as e:

            print("Reason:", e)

        print('Fething results from splunk server - please wait for response codes/error messages...')

        reader = results.ResultsReader(oneshotsearch_results)

        file = "REDD_SOC_ScreenLimits_" + currentDT.strftime("%Y%m%d%H%M%S") + ".csv"

        f = open(file, 'wb')

        f.write(oneshotsearch_results.read())

        f.close()

        print("Data Download Completed")

Not sure what am I missing