topic Re: SPLUNK output from search query in Splunk Search

SPLUNK output from search query

abidkar — Tue, 18 May 2021 21:41:25 GMT

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

Re: SPLUNK output from search query

richgalloway — Tue, 18 May 2021 23:34:41 GMT

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.

Re: SPLUNK output from search query

abidkar — Wed, 19 May 2021 01:56:43 GMT

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

Re: SPLUNK output from search query

ITWhisperer — Wed, 19 May 2021 07:57:45 GMT

Use spath to extract the fields from the payload

Re: SPLUNK output from search query

abidkar — Wed, 19 May 2021 18:37:49 GMT

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

Can you please help me with the syntax?

Thanks for your help!

Re: SPLUNK output from search query

dhirendra761 — Wed, 19 May 2021 20:30:10 GMT

<your search>| spath | table *

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

Re: SPLUNK output from search query

abidkar — Thu, 20 May 2021 02:35:17 GMT

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.

Re: SPLUNK output from search query

ITWhisperer — Thu, 20 May 2021 05:41:17 GMT

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

Re: SPLUNK output from search query

dhirendra761 — Thu, 20 May 2021 06:59:12 GMT

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

Re: SPLUNK output from search query

abidkar — Thu, 20 May 2021 14:22:15 GMT

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

Re: SPLUNK output from search query

ITWhisperer — Thu, 20 May 2021 14:55:10 GMT

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

Re: SPLUNK output from search query

abidkar — Thu, 20 May 2021 17:26:57 GMT

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

Re: SPLUNK output from search query

abidkar — Wed, 02 Jun 2021 15:48:03 GMT

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

Re: SPLUNK output from search query

richgalloway — Wed, 02 Jun 2021 15:54:34 GMT

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.

Re: SPLUNK output from search query

abidkar — Wed, 02 Jun 2021 22:27:29 GMT

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

Re: SPLUNK output from search query

dhirendra761 — Thu, 03 Jun 2021 07:10:00 GMT

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

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

Re: SPLUNK output from search query

abidkar — Thu, 03 Jun 2021 19:00:15 GMT

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

Re: SPLUNK output from search query

abidkar — Thu, 03 Jun 2021 19:22:12 GMT

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