Dashboards & Visualizations
Highlighted

Column chart with Multiple chart overlay series ?

Builder

I have 3 columns in my search output. For e.g. date, col1, col2, col3. Date will be X-Axis
The column chart will show the bars for the value of Col1 by date and I want to do the chart overlay for col2 and col3 on this column chart. At present, I can overlay col2 series values on the chart, but not able to figure out how can I overlay third col3 on the same column chart ? Please help in this ?

For eg. attached image has the details (query output and graph I want to show ). The bars represents the avg number of hours users worked for that date, also the yellow line represents the same thing ( used appendcols with the same search to overlay the value on bars), so that it can show the trend. I want to add the third overlay ( red line), that will show the number of total users (user_count) on that particular day ?

user_count and "Avg User Session Time" should be the overlay on the bars.

Also, I am not able to sort the date properly, April values are showing first and then march values ?

Below is my query :

index=paloalto sourcetype="syslog" userid!="tem*" zone="VPN" | eval date=date_mday+" - "+date_month |stats earliest(_time) as earliest latest(_time) as latest by userid,status,date | sort date| where status="login" | eval duration=latest-earliest  | stats avg(duration) as duration dc(userid) as user_count by date| eval duration=round(duration/60/60,0) | rename date as Date duration as "Avg User Session Time (Hrs)" | appendcols [ search index=paloalto sourcetype="syslog" userid!="tem*" zone="VPN" | eval date=date_mday+" - "+date_month |stats earliest(_time) as earliest latest(_time) as latest by userid,status,date | sort date| where status="login" | eval duration=latest-earliest  | stats avg(duration) as duration by date| eval duration=round(duration/60/60,0) | rename date as Date duration as "Avg User Session Time" ]

![alt text][2] [2]: /storage/temp/286728-overlay.jpg

alt text

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Ultra Champion
| makeresults
| eval _raw="Date,Avg_user_session_Hrs,User_count,Avg_user_session
1 - april,9,196,9
2 - april,6,193,6
26 - march,7,167,7
27 - march,7,60,7
28 - march,6,77,6
29 - march,8,177,8
30 - march,9,175,9
31 - march,9,179,9"
|multikv
| table Date,Avg_user_session_Hrs,User_count,Avg_user_session

Is it like this?
chart

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

i cant see the image u have uploaded ? it is not displaying the image u have attached. Can you re upload the image please ?

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

@to4kawa - the query result you have put is proper, but how do i overlay 2 series.. I think u attached the image of the resulted chart, but it is not showing up ? please re-share ?

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Ultra Champion
| makeresults 
| eval _raw="Date,Avg_user_session_Hrs,User_count,Avg_user_session
1 - april,9,196,9
2 - april,6,193,6
26 - march,7,167,7
27 - march,7,60,7
28 - march,6,77,6
29 - march,8,177,8
30 - march,9,175,9
31 - march,9,179,9" 
| multikv 
| rex field=Date "(?<day>\d+) - (?<month>\w+)" 
| eval month=substr(upper(month),1,1).substr(month,2) 
| eval _time=strptime(day.month,"%d%B") 
| fieldformat _time= strftime(_time, "%d - %B") 
| eval User_count=round(User_count * 0.02,2) 
| sort _time 
| fields - _* 
| fields Date,Avg_user_session_Hrs,User_count,Avg_user_session

image

If you want User_count display in the middle of bar, you should use the trick

View solution in original post

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

I am able to plot the series like the one you have done, but actually there are some issues, listed below :

  1. The date is not showing in chronological order, it should start with 26-march, 27-march,...., 2-april
  2. I want to display the line Avgusersession on top of the bar and
  3. I want to display user_count in the middle of the bar so that we identify the series properly.

How can I display the chart like the one attached ? the first image I attached in my question ? is that possible ?

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

see the image overlay.jpg in my question. I want to show user_count as red line in the image that should display in the middle of the bars ? is that possible ?

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

can you share the trick u have used to display that series in chart ?

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Ultra Champion

look no.16 on my query.

0 Karma
Highlighted

Re: Column chart with Multiple chart overlay series ?

Builder

ok. i will try that and revert. Thanks.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.