Hi All,
I am new to the Splunk world and pls help me to explore.
I have a product.csv files which contains 6 fields let's say.
'Booking ID', 'start Time', 'End Time', 'Source', 'Destination', 'Delivery success %'
123 1/28/2017 8:03:00 PM 1/28/2017 11:59:00 PM Ind USA 98
111 1/28/2017 10:14:00 PM 1/28/2017 11:58:00 PM Sign USA 100
111 1/28/2017 12:14:00 PM 1/28/2017 14:58:00 PM UKL USA 100
444 1/29/2017 8:03:00 PM 1/29/2017 11:59:00 PM USA IND 56
555 1/29/2017 10:14:00 PM 1/29/2017 11:58:00 PM USA Sign 100
666 1/30/2017 8:03:00 PM 1/30/2017 11:59:00 PM CHN IND 100
777 1/30/2017 10:14:00 PM 1/30/2017 11:58:00 PM IND CHN 100
I need to sort (by ascending order) the csv file based on field name 'start Time' then need to plot a bar graph based on 'start Time' field (timestamps are in GMT) in X-axis and Y-axis should be '% of Delivery success'
Note: how to calculate '% Delivery success' for each day is : ( sum of 100 % 'Delivery success %'/ number of 'booking ID')
For example: for 28th jan 2017: '% Delivery success' would be (200/3) = 66.67%
for 29th jan 2017: '% Delivery success' would be (100/2) = 50%
for 30th jan 2017: '% Delivery success' would be (200/2) = 100%
Thanks In Adv.
Can you try this
| inputlookup product.csv
| rex field="start Time" "(?<bookingDate>[\S]+)"
| eventstats count by bookingDate
| search "Delivery success %"=100
| stats sum('Delivery success %') as SUM values(count) as COUNT by bookingDate
| eval deliveryPercent=round(SUM/COUNT, 2)
| fields - SUM,COUNT
Choose visualization as bar chart, take care of the field names in quotes and see whether really symbols like %
appear in your field names or not (like is given in question).
Append the following line right at the end of the above query to get the date in the desired format
| eval bookingDate=strftime(strptime(bookingDate, "%m/%d/%Y"), "%d/%b")
Can you try this
| inputlookup product.csv
| rex field="start Time" "(?<bookingDate>[\S]+)"
| eventstats count by bookingDate
| search "Delivery success %"=100
| stats sum('Delivery success %') as SUM values(count) as COUNT by bookingDate
| eval deliveryPercent=round(SUM/COUNT, 2)
| fields - SUM,COUNT
Choose visualization as bar chart, take care of the field names in quotes and see whether really symbols like %
appear in your field names or not (like is given in question).
Append the following line right at the end of the above query to get the date in the desired format
| eval bookingDate=strftime(strptime(bookingDate, "%m/%d/%Y"), "%d/%b")
@gokadroid: Thank You Very much! It worked for me. I had to change your search query little bit i.e: we can not create an alias name same as inbuild function name ( SUM, COUNT ) rest all are fine. Milion Thx to you!