topic Re: X-Axis duration in hours, not seconds in Splunk Search

X-Axis duration in hours, not seconds

pashtet13 — Tue, 29 Sep 2020 10:21:49 GMT

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?

Re: X-Axis duration in hours, not seconds

woodcock — Mon, 25 Jul 2016 15:58:09 GMT

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

Re: X-Axis duration in hours, not seconds

sundareshr — Mon, 25 Jul 2016 16:03:52 GMT

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.

Re: X-Axis duration in hours, not seconds

woodcock — Mon, 25 Jul 2016 16:45:51 GMT

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

Re: X-Axis duration in hours, not seconds

pashtet13 — Mon, 25 Jul 2016 19:53:05 GMT

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

Re: X-Axis duration in hours, not seconds

somesoni2 — Mon, 25 Jul 2016 21:15:45 GMT

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.

Re: X-Axis duration in hours, not seconds

pashtet13 — Mon, 25 Jul 2016 21:48:31 GMT

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

Re: X-Axis duration in hours, not seconds

woodcock — Wed, 27 Jul 2016 14:30:15 GMT

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