Dashboards & Visualizations

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

Renunaren
Loves-to-Learn Everything

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,

Labels (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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*

 

0 Karma

Renunaren
Loves-to-Learn Everything

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

@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*
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

0 Karma

Renunaren
Loves-to-Learn Everything

Hi User,

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

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...