Dashboards & Visualizations

Column chart with Multiple chart overlay series ?

pgadhari
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
1 Solution

to4kawa
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

to4kawa
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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

pgadhari
Builder

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.

0 Karma

pgadhari
Builder

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

0 Karma

to4kawa
Ultra Champion

look no.16 on my query.

0 Karma

pgadhari
Builder

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 ?

0 Karma

pgadhari
Builder

ok. i will try that and revert. Thanks.

0 Karma

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

0 Karma

pgadhari
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

to4kawa
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

pgadhari
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

pgadhari
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...