Column chart with Multiple chart overlay series ?

[pgadhari]

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

[to4kawa · Accepted solution]

| 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

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

[to4kawa]

@pgadhari
If you don't display Avg_user_session , User_count can display with original value.
It can't be helped by default chart.

[pgadhari]

Yes, I thought so thats why I opened the question to check if any options available for it. But, thanks for your quick reponse and support. I will not display Avg_usesr_session and keep the chart as simple.

Also, thanks for that trick.

[pgadhari]

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

[to4kawa]

look no.16 on my query.

[pgadhari]

I can understand that we have to multiply by 0.02, but then it is not showing the actual figures (user_count) on the chart, and this will confuse the people as it is displaying the values like 3.32, 1.5 etc. I can understand that you are trying to fit it in the scale, but that will create more confusion for the users to read the chart. Any other method is there ?

[pgadhari]

ok. i will try that and revert. Thanks.

[pgadhari]

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 Avg_user_session 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 ?

[pgadhari]

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 ?

[to4kawa]

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

[pgadhari]

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

[pgadhari]

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