topic Re: How to automate timechart for multiple users/fields? in Splunk Search

How to automate timechart for multiple users/fields?

mitsost — Tue, 29 Oct 2019 20:25:21 GMT

Greetings all,
Noob here.

I have the following timechart:

index=fileshare user_login=john_doe@mycompany.com (event_type=SHARE OR event_type=EDIT OR event_type=UPLOAD OR event_type=DOWNLOAD OR event_type=DELETE) 
    | timechart span=1mon@mon count by event_type

Time range is 4 months

I would like to do the same for about 100 users (i.e. 100 "user_login" addresses).

Basically, I would like something like this but with multiple rows:

Do you have any ideas on how I can automate it?
Maybe a trellis chart?

I tried
index=fileshare created_by_login IN (john_doe@mycompany.com, jane_smith@mycompany.com... etc etc ) (event_type=SHARE OR event_type=EDIT OR event_type=UPLOAD OR event_type=DOWNLOAD OR event_type=DELETE) | stats count by created_by_login, date_month, event_type

But the separate colors for events are not shown in each month - any ideas?

Thank you
Mitsos

Re: How to automate timechart for multiple users/fields?

skoelpin — Tue, 29 Oct 2019 22:05:18 GMT

What exactly are you trying to do here? How are you going to interpret the data? What's the purpose of wanting to run this on a per user basis? Whats wrong with adding the user_login as an additional feature after the by and changing it to a stats?

Re: How to automate timechart for multiple users/fields?

mitsost — Wed, 30 Oct 2019 01:23:18 GMT

Thanks for replying @skoelpin

The idea is to have a dashboard with a stack of charts in alphabetical order (of email) so that one can scroll and see the usage trends of any one of the set of users.

I tried adding an image here to show you but I couldn't - will try in a regular reply...

Re: How to automate timechart for multiple users/fields?

mitsost — Wed, 30 Oct 2019 01:29:08 GMT

I added the image in the main question...

Re: How to automate timechart for multiple users/fields?

woodcock — Wed, 30 Oct 2019 01:52:40 GMT

You need the trellis feature:
https://docs.splunk.com/Documentation/Splunk/latest/Viz/VisualizationTrellis

Re: How to automate timechart for multiple users/fields?

mitsost — Thu, 31 Oct 2019 02:13:00 GMT

Hi @woodcock

I tried the following:
index=fileshare created_by_login IN (john_doe@mycompany.com, jane_smith@mycompany.com) (event_type=SHARE OR event_type=EDIT OR event_type=UPLOAD OR event_type=DOWNLOAD OR event_type=DELETE) | stats count by created_by_login, date_month, event_type

But the trellis visualization does not show all the different events by month... See above - I will add the image there

Re: How to automate timechart for multiple users/fields?

skoelpin — Thu, 31 Oct 2019 14:24:16 GMT

I think you're going down the wrong path here.. You should not have 100 panels in a dashboard, this is not a scalable solution and is difficult to interpret for a human. Why not have a single graph with 100 lines? Or perhaps even group them.

What exactly are you looking to interpret from this information? Are you looking for anomalous file-sharing usage per person?

Re: How to automate timechart for multiple users/fields?

mitsost — Thu, 31 Oct 2019 14:45:06 GMT

Hi @skoelpin - yes, I think I agree with you in terms of the number of panels in the dashboard.

The goal is for upper management to see that these VIPs have started using the file sharing solution more and more as time goes by. Especially the "share" action which means they created a link to a file (which, one can assume, means they did not attach the file as an email attachment--we are trying to move away from email attachments). So, these individuals are high enough in the company that their behavior can trickle down to others... So they have gone through trainings and we are hoping to see an increase in their use of the file-sharing tool. It may not be 100, it can be 10 at each dashboard...

Thanks again for the input!!

Re: How to automate timechart for multiple users/fields?

skoelpin — Thu, 31 Oct 2019 15:16:44 GMT

Gotcha, so the end result you want is the ability to show growth in usage on a per user basis relative to time. Perhaps a better way would be to schedule a daily or weekly report which will list each user per row and show the increase in usage relative to the previous week. An example would look like this

User         | Percent Increase in Usage

John Doe | +30%
Jane Doe | +13%
Steve Jobs | -4%

You could even sort it from least to greatest if you want a quick overview of the people who are not trending in the right direction. Overtime as more people get used to the new way of using the file share, you will see these numbers level out and get closer to zero, similarly how'd you see the bar graphs level out in a dashboard view. You could even add a secondary dashboard view which has one panel with 100 lines where each line represents each user and get a visual that way. If you want to go this direction, I can certainly help with that

Re: How to automate timechart for multiple users/fields?

woodcock — Thu, 31 Oct 2019 21:13:36 GMT

Do not use the date_* fields; do it like this:

index="fileshare" AND created_by_login IN ("john_doe@mycompany.com", "jane_smith@mycompany.com") AND (event_type IN("SHARE", "EDIT, "UPLOAD", "DOWNLOAD", "DELETE")
| bin _time span=1m
| timechart count(eval(event_type="SHARE")) AS SHARE count(eval(event_type="EDIT")) AS EDIT count(eval(event_type="UPLOAD")) AS UPLOAD count(eval(event_type="=DOWNLOAD")) AS DOWNLOAD count(eval(event_type="DELETE")) AS DELETE BY created_by_login

Then select trellis by created_by_login and enjoy!
Here is a run-anywhere example:

index=_* 
| bin _time span=1m
| rename host AS created_by_login, sourcetype AS event_type 
| eval sourcetype = case(
   sourcetype=="splunkd",               "SHARE",
   sourcetype=="splunk_resource_usage", "EDIT",
   sourcetype=="splunkd_access",        "UPLOAD",
   sourcetype=="threatintel:manager",   "DOWNLOAD", 
   true(),                              "DELETE")
| timechart count(eval(event_type="SHARE")) AS SHARE count(eval(event_type="EDIT")) AS EDIT count(eval(event_type="UPLOAD")) AS UPLOAD count(eval(event_type="=DOWNLOAD")) AS DOWNLOAD count(eval(event_type="DELETE")) AS DELETE BY created_by_login

Re: How to automate timechart for multiple users/fields?

mitsost — Thu, 31 Oct 2019 21:13:37 GMT

Hi @skoelpin - thanks for replying
The idea is to track the usage of a file-sharing solution for these 100 (approx. number) individuals over the past four months.

So I would like a dashboard that has multiple rows like this, sorted alphabetically - so it can give a snapshot for each user:

Re: How to automate timechart for multiple users/fields?

woodcock — Sat, 02 Nov 2019 19:28:08 GMT

See my new answer; I built out the entire solution for you.

Re: How to automate timechart for multiple users/fields?

woodcock — Sat, 02 Nov 2019 19:28:51 GMT

This is exactly what my solution does; have you tried it yet?

Re: How to automate timechart for multiple users/fields?

mitsost — Tue, 05 Nov 2019 15:53:11 GMT

Helo @woodcock

Thank you VERY much for the help, that is awesome...
Is there a way to make the bars thicker?

Re: How to automate timechart for multiple users/fields?

mitsost — Tue, 05 Nov 2019 15:58:45 GMT

Here is what it looks like now:
https://www.dropbox.com/s/vonbwnsgwnbb5y6/Image%20092.png?dl=0

Re: How to automate timechart for multiple users/fields?

niketn — Tue, 05 Nov 2019 16:12:52 GMT

As your data span is monthly, either try

| bin _time span=1mon

Or within the timechart command pipe put the following argument at the end.

 cont=f

Re: How to automate timechart for multiple users/fields?

mitsost — Tue, 05 Nov 2019 18:04:28 GMT

cont=f worked!!
THank you !!

Re: How to automate timechart for multiple users/fields?

mitsost — Tue, 05 Nov 2019 18:05:23 GMT

Much appreciated!!

Re: How to automate timechart for multiple users/fields?

woodcock — Tue, 05 Nov 2019 22:15:47 GMT

You can select small, medium or large panels and that it about it, as far as I know.

Re: How to automate timechart for multiple users/fields?

woodcock — Tue, 05 Nov 2019 23:43:49 GMT

Also change Yaxis from linear to log.