Need to extract the values from the columns in the events and add them as a separate fields

[Renunaren]

Hi Team,

We have events that are being received from csv and json files in the below format.

"message": "Dataframe row : {\"_c0\":{\"0\":\"Linux\",\"1\":\"00:00:01\",\"2\":\"00:10:01\",\"3\":\"00:20:01\",\"4\":\"00:30:01\",\"5\":\"00:40:01\",\"6\":\"00:50:01\",\"7\":\"01:00:01\",\"8\":\"01:10:01\",\"9\":\"01:20:02\",\"10\":\"01:30:01\",\"11\":\"01:40:01\",\"12\":\"01:50:01\"},\"_c2\":{\"0\":\"(fraasdwhbdd1.de.db.com)\",\"1\":\"%user\",\"2\":\"1.28\",\"3\":\"1.05\",\"4\":\"1.13\",\"5\":\"1.25\",\"6\":\"0.98\",\"7\":\"1.08\",\"8\":\"1.75\",\"9\":\"1.04\",\"10\":\"1.22\",\"11\":\"1.11\",\"12\":\"1.05\"}

The above event is related to cpu utilization which has been sent to splunk from csv and json files by executing SAR command. 

The requirement is that we have to extract the time stamps such as 00:00:01 , 00:10:01, 00:20:01 values and %utilization values such as 1,28, 1.05 from the above message event and add them as a separate columns or fields.

Also we have to construct a dashboard panel using the above values. Please do the needful on this,

[bowesmana]

Do you always have a single header row and data row in each of the events?

[bowesmana]

Here's an example that will make the headers and assign the values, but it is relying on your data example.

The foreach statement is what builds the column/value pairings by assigning c2.* to c0.* fields.

| makeresults
| fields - _time
| eval _raw="\"message\": \"Dataframe row : {\"_c0\":{\"0\":\"Linux\",\"1\":\"00:00:01\",\"2\":\"00:10:01\",\"3\":\"00:20:01\",\"4\":\"00:30:01\",\"5\":\"00:40:01\",\"6\":\"00:50:01\",\"7\":\"01:00:01\",\"8\":\"01:10:01\",\"9\":\"01:20:02\",\"10\":\"01:30:01\",\"11\":\"01:40:01\",\"12\":\"01:50:01\"},\"_c2\":{\"0\":\"(fraasdwhbdd1.de.db.com)\",\"1\":\"%user\",\"2\":\"1.28\",\"3\":\"1.05\",\"4\":\"1.13\",\"5\":\"1.25\",\"6\":\"0.98\",\"7\":\"1.08\",\"8\":\"1.75\",\"9\":\"1.04\",\"10\":\"1.22\",\"11\":\"1.11\",\"12\":\"1.05\"}"
| rex "Dataframe row\s+?:\s+?(?<raw_json>.*)"
| spath input=raw_json
| fields - _raw raw_json
| foreach c0.* [ eval "{<<FIELD>>}"='c2.<<MATCHSTR>>']
| fields - c*

[Renunaren]

Here the time stamp value and the percentage of utilization are Dynamic values, those are not static. The event which I have given is a sample event. Please look into this and kindly do the needful

[ITWhisperer]

@Renunaren  Please remember that this is a community of volunteers, we are not paid to do your job. We are here to help where we can to the extent that we decide, not you.

@bowesmana  has given you a solution for a representation of the data you provided. If this is not sufficient, you need to provide a detailed explanation of why not, so we might be able to assist you further.

Having said that, the sample data used assumes that the escaped quotes in your embedded json don't exist in your data. If they do actually exist, they will need to be removed, as in this example:

| makeresults 
| fields - _time
| eval _raw="\"message\": \"Dataframe row : {\\\"_c0\\\":{\\\"0\\\":\\\"Linux\\\",\\\"1\\\":\\\"00:00:01\\\",\\\"2\\\":\\\"00:10:01\\\",\\\"3\\\":\\\"00:20:01\\\",\\\"4\\\":\\\"00:30:01\\\",\\\"5\\\":\\\"00:40:01\\\",\\\"6\\\":\\\"00:50:01\\\",\\\"7\\\":\\\"01:00:01\\\",\\\"8\\\":\\\"01:10:01\\\",\\\"9\\\":\\\"01:20:02\\\",\\\"10\\\":\\\"01:30:01\\\",\\\"11\\\":\\\"01:40:01\\\",\\\"12\\\":\\\"01:50:01\\\"},\\\"_c2\\\":{\\\"0\\\":\\\"(fraasdwhbdd1.de.db.com)\\\",\\\"1\\\":\\\"%user\\\",\\\"2\\\":\\\"1.28\\\",\\\"3\\\":\\\"1.05\\\",\\\"4\\\":\\\"1.13\\\",\\\"5\\\":\\\"1.25\\\",\\\"6\\\":\\\"0.98\\\",\\\"7\\\":\\\"1.08\\\",\\\"8\\\":\\\"1.75\\\",\\\"9\\\":\\\"1.04\\\",\\\"10\\\":\\\"1.22\\\",\\\"11\\\":\\\"1.11\\\",\\\"12\\\":\\\"1.05\\\"}"
``` the lines above set up dummy data in line with your example ```
| rex "Dataframe row\s+?:\s+?(?<raw_json>.*)"
| eval raw_json=replace(raw_json,"\\\\","")
| spath input=raw_json
| fields - _raw raw_json
| foreach c0.* [ eval "{<<FIELD>>}"='c2.<<MATCHSTR>>']
| fields - c*

[bowesmana]

You will need to indicate how the data can vary. Unless you can give guidance on how the data can and will look, it's not really practical to provide a solution.

How does the example work in your environment and does it work?

If not what is not working and what does your data look like at that point.

My solution works with the example data you supplied.

[Renunaren]

Hi User,

Thanks for the reply this is helpful for me to some extent which I will work on further.