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

[Renunaren]

The above snippet consists of the raw data in the events in our splunk environment.

Need Help in extracting the jobIds (that are highlighted) in the raw data and add them as a separate field like below using SPL in user interface.

[ITWhisperer]

Rather than using graphics, please can you share your raw, unformatted events in a code block </> so that testing of solutions will be easier?

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