How to combine 2 csv files and perform multiple eval operations on it and produce a graph?

harishy100 — Tue, 29 Sep 2020 17:42:30 GMT

I have 2 CSV files. Each CSV file has 2 fields "Start_Time" and "End_Time"
1. I need to find the "total time" taken i.e. sum of the difference between Start and End time for all the records.
2. The total time for each CSV file is plotted against one another.

How can this be achieved?
I have tried to

source="nfr-output_300_1.csv" host="IHTNW754752GG-L" index="main" sourcetype="csv"
 | join Seq_no, Ta_Id,objectID [search source="nfr-output_300_2.csv" host="IHTNW754752GG-L" index="main" sourcetype="csv" | rename Start_Time as Start_Time_openC, End_time as End_time_openC ]
 | eval end= strptime(End_time,"%Y-%m-%d %H:%M:%S")
 | eval start= strptime(Start_Time,"%Y-%m-%d %H:%M:%S")
 | eval diff= end-start
 | stats sum(diff) as Total_Time
 | eval Total_Time = Total_Time/3600

 | eval end1= strptime(End_time_openC,"%Y-%m-%d %H:%M:%S")
 | eval start1= strptime(Start_Time_openC,"%Y-%m-%d %H:%M:%S") 
 | eval diff1= end1-start1
 | stats sum(diff1) as Total_Time1
 | eval Total_Time1 = Total_Time1/3600
 | table Total_Time, Total_Time1

I have joined the 2 files and repeated the same 'sum' calculations 2 times. It didnt work.
Can someone help in achieving this problem?

Re: How to combine 2 csv files and perform multiple eval operations on it and produce a graph?

mayurr98 — Tue, 23 Jan 2018 07:16:14 GMT

hey you can try something like this

index="main" sourcetype="csv" source="nfr-output_300_1.csv" host="IHTNW754752GG-L" 
| eval end=strptime(End_time,"%Y-%m-%d %H:%M:%S") 
| eval start=strptime(Start_Time,"%Y-%m-%d %H:%M:%S") 
| eval diff=end-start 
| stats sum(diff) as Total_Time 
| eval Total_Time_csv1=round(Total_Time/3600) 
| fields- Total_Time 
| appendcols 
    [ search index="main" sourcetype="csv" source="nfr-output_300_2.csv" host="IHTNW754752GG-L" 
    | eval end=strptime(End_time,"%Y-%m-%d %H:%M:%S") 
    | eval start=strptime(Start_Time,"%Y-%m-%d %H:%M:%S") 
    | eval diff=end-start 
    | stats sum(diff) as Total_Time 
    | eval Total_Time_csv2=round(Total_Time/3600) 
    | fields- Total_Time]

let me know if this helps!

topic How to combine 2 csv files and perform multiple eval operations on it and produce a graph? in Splunk Search

How to combine 2 csv files and perform multiple eval operations on it and produce a graph?

Re: How to combine 2 csv files and perform multiple eval operations on it and produce a graph?