topic Need help in extraction and creating table in Splunk Search

Need help in extraction and creating table

monicateja — Sun, 30 Oct 2022 08:34:19 GMT

Below query is in string text format need to separate each field and create a table with all columns for operator , register , store , timestamp, associate_id, audit_result

log: {“timeMillis”:“166665", “timestamp”:“2022-10-16", “level”:“INFO”,“logger”:“com.abc”, “message”:“Business Key=null, Publishing status: [ client_req_id dc366, event_date 2022-10-16, event_name EXIT ], message {“receipts”:[{“id”:“150”, “date”:“2022-10-24”, “store”:“99”, “operator”:“48”, “register”:“48”,“status”:“pass”,}], “result”: {“date”:“2022-10-16",“store”:“99",“associate_id”:“92",“result”:“Pass”,“failure_reason”:null,“scanned_items”:1, “items_found”:[],“items_not_found”:[]}}”

Tried query as spath input= , path= , output= | table id, operator , register, store , timestamp but dont work

Re: Need help in extraction and creating table

ITWhisperer — Sun, 30 Oct 2022 09:09:10 GMT

Given that the string you shared is not valid JSON, it is not surprising that spath didn't work.

Assuming that the double quotes are supposed to be real double quotes (not pretty format versions), you could try this:

| rex "\"operator\":\"(?<operator>[^\"]+)\"" | rex "\"register\":\"(?<register>[^\"]+)\"" | rex "\"store\":\"(?<store>[^\"]+)\"" | rex "\"timestamp\":\"(?<timestamp>[^\"]+)\"" | rex "\"associate_id\":\"(?<associate_id>[^\"]+)\"" | rex "\"audit_result\":\"(?<audit_result>[^\"]+)\""

Note that audit_result does not exist in your shared example.

In future, it would be better to share your event examples (taken from the _raw field) in a code block using the </> button as I have done with the sample code, as this eliminates pretty formatting conversions.

Re: Need help in extraction and creating table

yuanliu — Mon, 31 Oct 2022 08:59:30 GMT

Similar to your other question, https://community.splunk.com/t5/Splunk-Search/Regex-search/m-p/618859#M215078, I seriously suspect that your illustrated data is not faithful to the original format. For example, inside the receipts block, the last comma was either extra (' "status":"pass",}] ' should be ' "status":"pass"}] '), or some additional fields are being omitted. (And that, again, is in addition to the inconsistent use of quotation mark character.) If I correct the quotation mark character as well as the extra comma, the second "message" part of the data is conformant JSON. You should not attempt to use regex for such structured data. Use built-in command spath that takes care of all the complexities.

| rex "message (?<message>{.+}})" | spath input=message | fields - receipts{}.* ``` receipts are multivalue. handle separately ``` | spath input=message path=receipts{} | mvexpand receipts{} | spath input=receipts{} | fields - message receipts{} result _time

This should give you something like

date	id	operator	register	result.associate_id	result.date	result.failure_reason	result.result	result.scanned_items	result.store	status	store
2022-10-24	150	48	48	92	2022-10-16	null	Pass	1	99	pass	99