topic Re: data mapping in csv file during splunk search in Getting Data In

data mapping in csv file during splunk search

ektasiwani — Wed, 20 May 2015 08:49:27 GMT

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

Re: data mapping in csv file during splunk search

vganjare — Wed, 20 May 2015 11:49:33 GMT

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:

Load the data : *| inputlookup Data.csv *
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.
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!!

Re: data mapping in csv file during splunk search

vganjare — Wed, 20 May 2015 12:29:26 GMT

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!!

Re: data mapping in csv file during splunk search

ektasiwani — Wed, 20 May 2015 18:35:18 GMT

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 ..:)

Re: data mapping in csv file during splunk search

ektasiwani — Wed, 20 May 2015 18:37:14 GMT

I did same using where. Thanks

Re: data mapping in csv file during splunk search

ppablo — Thu, 21 May 2015 01:51:38 GMT

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.

Re: data mapping in csv file during splunk search

ektasiwani — Thu, 21 May 2015 05:07:28 GMT

oopss sorry.
I will take care of that.