Splunk Search

How to plot a bar graph based on date in a csv file?

Explorer

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%
alt text

Thanks In Adv.

0 Karma
1 Solution

Motivator

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")

View solution in original post

Motivator

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")

View solution in original post

Explorer

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!