Splunk Search

How to automate timechart for multiple users/fields?

mitsost
Path Finder

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:
alt text

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?
alt text

Thank you
Mitsos

1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

View solution in original post

mitsost
Path Finder

Helo @woodcock

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

mitsost
Path Finder
0 Karma

niketnilay
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

woodcock
Esteemed Legend

Also change Yaxis from linear to log.

0 Karma

mitsost
Path Finder

cont=f worked!!
THank you !!

mitsost
Path Finder

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:
alt text

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

mitsost
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

mitsost
Path Finder

Much appreciated!!

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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?

mitsost
Path Finder

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...

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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?

mitsost
Path Finder

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

mitsost
Path Finder

I added the image in the main question...

0 Karma