Splunk Search

X-Axis duration in hours, not seconds

New Member

I am using the following search to get a total VPN connection time for users:

index=pan_logs eventtype=pan_system log_subtype=globalprotect sourcetype=pan:system
| transaction pan_gp_user startswith="globalprotectgateway-auth-succ" endswith="globalprotectgateway-logout-succ"
| stats sum(duration) by pan_gp_user
| sort by -sum(duration)

I am using Bar Chart and X-Axis is showing duration in seconds. Converting to hh:mm:ss format worked in a regular search, but not for Bar Chart. Any way I can make X-Axis to show time in readable format (hh:mm:ss), rather than in seconds?

alt text

0 Karma

Revered Legend

In bar chart, the x-axis shows the series values and it has to be numeric in order to be plotted as chart. Converting to hh:mm will make it as string and it will not work. For your case try this workaround (runanywhere sample)

    | gentimes start=-1 | eval temp="user1#2000 user2#1400 user3#1100 user4#1700" | table temp | makemv temp | mvexpand temp | rex field=temp "(?<user>\w+)#(?<series>\d+)" | table user series 
| eval duration=tostring(series,"duration") | chart values(series) over user by duration | addtotals | sort -Total | fields - Total

Replace first 2 lines with your current search and use stacked option in the bar chart visualization.

0 Karma

New Member

Thanks. I ended up using this search:

index=pan_logs eventtype=pan_system log_subtype=globalprotect sourcetype=pan:system 
| transaction pan_gp_user startswith="globalprotectgateway-auth-succ" endswith="globalprotectgateway-logout-succ"
| where duration>0
| eval event_duration=tostring(duration,"duration")
| chart values(duration) over pan_gp_user by event_duration
| addtotals
| sort -Total
| fields - Total

Events did stack up together, but X-Axis is still in seconds

0 Karma

Legend

I don't believe you can change the format of x-axis for a bar chart t a string value, just like you cannot change the format of y-axis on a column chart.

0 Karma

Esteemed Legend

That is the beauty of fieldformat; it does not change the value.

0 Karma

Esteemed Legend

Try this:

index=pan_logs eventtype=pan_system log_subtype=globalprotect sourcetype=pan:system
| transaction pan_gp_user startswith="globalprotectgateway-auth-succ" endswith="globalprotectgateway-logout-succ" 
| stats sum(duration) AS duration BY pan_gp_user
| sort by -duration | fieldformat duration=tostring(duration, "duration")
0 Karma

New Member

Thanks. I already tried fieldformat before asking this question. It does not change the chart at all

0 Karma

Esteemed Legend

You are right; I should have tested. I think that it is impossible with native dashboarding facilities.

0 Karma
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!