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