Getting Data In

data mapping in csv file during splunk search

ektasiwani
Communicator

Hi,

I have a csv file, and i don't have access to raw data. csv file is created by some other software.
its structure is like:

time busycore cpu0 cpu1 cpu2
01:20:30 1 20% 50% 10%
01:28:30 0 80% 30% 40%
01:30:25 1 40% 90% 30%

i have to plot a graph showing at which time which cpu was busiest one and the value of that cpu.
for example at 01:20:30 busycore is 1 means cpu1 was busiest and it was used by 50%.
graph should show at 01:20:30 cpu1 was busiest and used by 50%.
any graph is ok with me. it can be bubble or scatter anything.

Thanks

Tags (2)
0 Karma
1 Solution

vganjare
Builder

HI,

The solution will be much simpler if you can get the access to raw logs. In case, its not possible, you can try following approach.

| inputlookup Data.csv | eval customField=[| inputlookup Data.csv | table time,busycore,cpu0,cpu1,cpu2 | fields - time busycore | transpose | fields column| eval customField = "\"".column."=\".".column.".\"~\"." | stats values(customField) as QueryValues | makemv delim="||" QueryValues | eval QueryFilter = substr(QueryValues , 1, len(QueryValues)-5) | return $QueryFilter] | makemv delim="~" customField | fields time busycore customField | mvexpand customField | eval customField = split(customField ,"=") | eval cpuName = mvindex(customField, 0) | eval cpuUsage=mvindex(customField, 1) | eventstats max(cpuUsage) as maxUsageByTime by time | eval maxCheck=if(cpuUsage==maxUsageByTime,"Yes","NO")

Here is an explanation:

  1. Load the data : *| inputlookup Data.csv *
  2. Dynamically construct a customField which will have value like "cpu0=".cpu0."~". "cpu1=".cpu1."~". "cpu2=".cpu2. This value will change depending on the number of CPU information you have in the data. The subsearch dynamically construct the string similar to above string.
  3. Rearrange the data as multiple events so that stats function can be used. All the events with maxCheck=Yes are the maximum value for that time.

Thanks!!

View solution in original post

0 Karma

ektasiwani
Communicator

I did same using where. Thanks

0 Karma

ppablo
Community Manager
Community Manager

Hi @ektasiwani

Please be sure that when responding to someone's answer, click on "Add comment" directly below their answer or, if responding to someone's comment, type in the "Add your comment..." box directly below their comment. You keeping typing your response in the "Enter your answer here..." box at the very bottom of the page which, instead, posts a brand new answer when it was really meant as a comment. This will help with a clean continuous flow of the conversation. Right now, it's hard to tell who you your responses were intended for. In addition, unless the user is following the question, they won't get a notification that you posted something new. Just something to keep in mind from here on out.

0 Karma

ektasiwani
Communicator

oopss sorry.
I will take care of that.

0 Karma

ektasiwani
Communicator

Sorry my mistake, now i am getting output. but for single time i am getting 4 entry , 3 entry showing 'no' and one entry showing ' yes'.
I want to remove all three "no" entries.
Can we use if condition to do that.
i mean just check busycore value and then match it with last digit of cpu0,cpu1 and cpu2.
if both are same keep data else ignore.

Thanks ..:)

0 Karma

vganjare
Builder

HI,

The solution will be much simpler if you can get the access to raw logs. In case, its not possible, you can try following approach.

| inputlookup Data.csv | eval customField=[| inputlookup Data.csv | table time,busycore,cpu0,cpu1,cpu2 | fields - time busycore | transpose | fields column| eval customField = "\"".column."=\".".column.".\"~\"." | stats values(customField) as QueryValues | makemv delim="||" QueryValues | eval QueryFilter = substr(QueryValues , 1, len(QueryValues)-5) | return $QueryFilter] | makemv delim="~" customField | fields time busycore customField | mvexpand customField | eval customField = split(customField ,"=") | eval cpuName = mvindex(customField, 0) | eval cpuUsage=mvindex(customField, 1) | eventstats max(cpuUsage) as maxUsageByTime by time | eval maxCheck=if(cpuUsage==maxUsageByTime,"Yes","NO")

Here is an explanation:

  1. Load the data : *| inputlookup Data.csv *
  2. Dynamically construct a customField which will have value like "cpu0=".cpu0."~". "cpu1=".cpu1."~". "cpu2=".cpu2. This value will change depending on the number of CPU information you have in the data. The subsearch dynamically construct the string similar to above string.
  3. Rearrange the data as multiple events so that stats function can be used. All the events with maxCheck=Yes are the maximum value for that time.

Thanks!!

View solution in original post

0 Karma

vganjare
Builder

Can you try using following query:

| inputlookup Data.csv | eval customField=[| inputlookup Data.csv | fields - time busycore | transpose | fields column| eval customField = "\"".column."=\".".column.".\"~\"." | stats values(customField) as QueryValues | makemv delim="||" QueryValues | eval QueryFilter = substr(QueryValues , 1, len(QueryValues)-5) | return $QueryFilter] | makemv delim="~" customField | fields time busycore customField | mvexpand customField | eval customField = split(customField ,"=") | eval cpuName = mvindex(customField, 0) | eval cpuUsage=mvindex(customField, 1) | eventstats max(cpuUsage) as maxUsageByTime by time | eval maxCheck=if(cpuUsage==maxUsageByTime,"Yes","NO")

Thanks!!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!