Splunk Search

Stacked Chart totaling all instances of an event.


I'm having a bit of trouble finishing up a report

I'm trying to give a report of how long users were logged into a system over the course of a day. Sometimes a user logs in multiple times. I want to have a stacked chart showing each time a user logged in added together.

I have:

source="*usermonitor*.log" "usermanager reports user * logged" | rex "usermanager reports user (?P<USER>[^\.]*) logged " |search USER="*"  | transaction USER startswith="logged in, reply " endswith="logged out, reply "   | eval Hours=round(duration/3600,2) |chart values(Hours) by USER

The results show exactly what I want. A list like:

User1     2.5
User2     3.1
User3     2.8
User4     7.4


Now when I "Show Report", I only see values on the chart for Users with only 1 entry and I can't make it a "Stacked" chart to total them all.

Of course, doing a "|chart sum(Hours) by USER" gives me the correct totals for all of the users, but I lose the individual values.

Any Suggestions?

Tags (2)
0 Karma

Re: Stacked Chart totaling all instances of an event.

Splunk Employee
Splunk Employee

The problem here is that the charting subsystem requires that stacked elements be in different series in the table. Your search has to yield something like:

User  1   2
----- --- ---
User1 2.5
User2 3.1
User3 2.8 1.5
User4 7.4

You can convert your table to one like this by renaming values(Hour) to vals and adding at the end:

 | mvexpand vals | eval counter=1 | streamstats sum(counter) as instance by clientip | chart first(vals) as val by USER instance

Here we break each user into their individual values, use streamstats to accumulate an instance count, and reformulate the chart using the USER and instance.