How to extract job Id values from raw text in events and add the job id into a separate field?

[Renunaren]

Hi User,

Thanks for the reply. Below is the raw text that has been received on splunk user interface. 

{"timestamp": "2023-01-24T08:06:29.621490Z", "level": "INFO", "filename": "splunk_sample_csv.py", "funcName": "main", "lineno": 38, "message": "Dataframe row : {\"_c0\":{\"0\":null,\"1\":\"266\",\"2\":\"267\",\"3\":\"268\"},\"_c1\":{\"0\":\"Timestamp\",\"1\":\"2023\\/01\\/10 13:31:19\",\"2\":\"2023\\/01\\/10 13:31:19\",\"3\":\"2023\\/01\\/10 13:31:19\"},\"_c2\":{\"0\":\"application\",\"1\":\"DWHEAP\",\"2\":\"DWHEAP\",\"3\":\"DWHEAP\"},\"_c3\":{\"0\":\"ctm\",\"1\":\"LNDEV02\",\"2\":\"LNDEV02\",\"3\":\"LNDEV02\"},\"_c4\":{\"0\":\"cyclic\",\"1\":\"False\",\"2\":\"False\",\"3\":\"False\"},\"_c5\":{\"0\":\"deleted\",\"1\":\"False\",\"2\":\"False\",\"3\":\"False\"},\"_c6\":{\"0\":\"description\",\"1\":\"Job to populate data to RDV for SK SOURCE SALES_EVENT\",\"2\":\"Job to populate data to RDV for SK SOURCE SALES_HIERARCHY\",\"3\":\"Job to populate data to RDV for SK SOURCE SALES_EVENT\"},\"_c7\":{\"0\":\"endTime\",\"1\":null,\"2\":null,\"3\":null},\"_c8\":{\"0\":\"estimatedEndTime\",\"1\":\"[u'20230110144400']\",\"2\":\"[u'20230110123200']\",\"3\":\"[u'20230110123200']\"},\"_c9\":{\"0\":\"estimatedStartTime\",\"1\":\"[u'20230110122700']\",\"2\":\"[u'20230110122700']\",\"3\":\"[u'20230110122700']\"},\"_c10\":{\"0\":\"folder\",\"1\":\"DWHEAP_RDV_SKBACKEND\",\"2\":\"DWHEAP_RDV_SKBACKEND\",\"3\":\"DWHEAP_RDV_SKBACKEND_TEST\"},\"_c11\":{\"0\":\"folderId\",\"1\":\"LNDEV02:\",\"2\":\"LNDEV02:\",\"3\":\"LNDEV02:\"},\"_c12\":{\"0\":\"held\",\"1\":\"False\",\"2\":\"False\",\"3\":\"False\"},\"_c13\":{\"0\":\"host\",\"1\":\"fraasdwhbdd1.de.db.com\",\"2\":\"fraasdwhbdd1.de.db.com\",\"3\":\"fraasdwhbdd1.de.db.com\"},\"_c14\":{\"0\":\"jobId\",\"1\":\"LNDEV02:5jtzl\",\"2\":\"LNDEV02:5jtzi\",\"3\":\"LNDEV02:5jtho\"},\"_c15\":{\"0\":\"logURI\",\"1\":\"https:\\/\\/lnemd.uk.db.com:8443\\/automation-api\\/run\\/job\\/LNDEV02:5jtzl\\/log\",\"2\":\"https:\\/\\/lnemd.uk.db.com:8443\\/automation-api\\/run\\/job\\/LNDEV02:5jtzi\\/log\",\"3\":\"https:\\/\\/lnemd.uk.db.com:8443\\/automation-api\\/run\\/job\\/LNDEV02:5jtho\\/log\"},\"_c16\":{\"0\":\"name\",\"1\":\"SALES_EVENT_RDV\",\"2\":\"SALES_HIERARCHY_RDV\",\"3\":\"SALES_EVENT_RDV\"},\"_c17\":{\"0\":\"numberOfRuns\",\"1\":\"0\",\"2\":\"0\",\"3\":\"0\"},\"_c18\":{\"0\":\"orderDate\",\"1\":\"230106\",\"2\":\"230106\",\"3\":\"230106\"},\"_c19\":{\"0\":\"outputURI\",\"1\":\"Job did not run, it has no output\",\"2\":\"Job did not run, it has no output\",\"3\":\"Job did not run, it has no output\"},\"_c20\":{\"0\":\"startTime\",\"1\":null,\"2\":null,\"3\":null},\"_c21\":{\"0\":\"status\",\"1\":\"Wait Condition\",\"2\":\"Wait Condition\",\"3\":\"Wait Condition\"},\"_c22\":{\"0\":\"subApplication\",\"1\":\"RDV_SKBACKEND\",\"2\":\"RDV_SKBACKEND\",\"3\":\"RDV_SKBACKEND_TEST\"},\"_c23\":{\"0\":\"type\",\"1\":\"Command\",\"2\":\"Command\",\"3\":\"Command\"}} ", "process": 2819, "processName": "MainProcess"}

In the above raw text there are jobId's 

\"_c14\":{\"0\":\"jobId\",\"1\":\"LNDEV02:5jtzl\",\"2\":\"LNDEV02:5jtzi\",\"3\":\"LNDEV02:5jtho\"}

We need to extract those jobids from the raw text and add them as a seperate field in the events using SPL in the user interface.

Please help me on this.

[yuanliu]

Two directions.

1. Flatten _c14 into flat fields as SPL usually does.
2. Extract _c14 into key-value pairs.

In the first method, do

| spath path=message
| eval message = replace(message, "Dataframe row *: *", "")
| spath input=message

Here, you get four flattened fields with c14.* (along with many other flattened fields)

c14.0	c14.1	c14.2	c14.3
jobId	LNDEV02:5jtzl	LNDEV02:5jtzi	LNDEV02:5jtho

In the second method, do

| spath path=message
| eval message = replace(message, "Dataframe row *: *", "")
| message = replace(message, "_c", "c") ``` unfortunately, this trick is needed ```
| spath input=message path=c14
| spath input=c14

You get four key-value pairs (along with some intermediate fields)

0	1	2	3
jobId	LNDEV02:5jtzl	LNDEV02:5jtzi	LNDEV02:5jtho