Dashboards & Visualizations

Chart count of results per day.

pdjhh
Communicator

Hi,

I'd like to show how many events (logins in this case) occur on different days of the week in total. So (over the chosen time period) there have been 6 total on Sundays, 550 on Mondays, y on Tuesdays etc. So that's a total for each day of the week where my x axis would just be Monday to Sunday with a total for each one. Actually I need a total for each machine type per day of the week where there are 4 machine types.

If I use timechart then it does the job per day for every day back tot he start of the time range but that's not what I'm looking for We wantt o look at usage patterns through the week.

So root search | timechart span=1d count by CPS usenull=0 give me a count per day per login type (CPS)

I want a chart for total numbers of logins per CPS per day of the week since the start of the time range.

Thanks..

0 Karma
1 Solution

pdjhh
Communicator

Thanks for your reply. I get a no results found when I try that, however? If I take the ', date_wday' off I get the counts by CPS so the data is there but the date_wday isn't working it seems.

View solution in original post

0 Karma

somesoni2
Revered Legend

Try this

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | eval weekday=strftime(_time, "%A") | chart count by weekday, CPS usenull=false | eval sort_field=case(weekday=="Monday",1, weekday =="Tuesday",2, weekday =="Wednesday",3, weekday =="Thursday",4, weekday =="Friday",5, weekday =="Saturday",6, weekday =="Sunday",7) | sort by sort_field

esix_splunk
Splunk Employee
Splunk Employee

Try

... basesearch | bin _time span=1d | stats count by CPS _time

That groups all events by time, in 1d buckets. You can visualize the output how you like it...

0 Karma

pdjhh
Communicator

Thanks for your reply. I get a no results found when I try that, however? If I take the ', date_wday' off I get the counts by CPS so the data is there but the date_wday isn't working it seems.

View solution in original post

0 Karma

pdjhh
Communicator

What I had to do in the end, and I find it crazy we have to do this in Splunk as it's normally so clever, is create a lookup table with numbers against the days of the week and sort on that. Why doesn't Splunk just recognise them as days of the week!

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | eval weekday=strftime(_time, "%A") | chart count by weekday, CPS usenull=false | lookup daystonumbers.csv weekday as weekday OUTPUT day_number as day_number | sort day_number | fields - day_number

0 Karma

HeinzWaescher
Motivator

Do you have something like

| fields+ CPS

before the stats command? You would have to add date_wday here as well

0 Karma

pdjhh
Communicator

My search looks lie this:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | timechart span=1d count by CPS usenull=0

CPS is a field in the events.

I tried this then:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | stats count by CPS, date_wday

But, as I say, get no results.

0 Karma

HeinzWaescher
Motivator

You could check whether the field is available, but it should...

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | table date_wday

0 Karma

pdjhh
Communicator

I actually tried that since our last message and got blank and do again here. Looking at this seems to say that if there's been any modifications to the timestamps en route then these fields won't be present:

http://docs.splunk.com/Documentation/Splunk/6.2.5/Knowledge/Usedefaultfields

So that could be my issue..

0 Karma

HeinzWaescher
Motivator

Perhaps a workaround can help:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*"

| eval weekday=strftime(_time, "%A")

| table weekday

0 Karma

pdjhh
Communicator

Getting really close now! That gives me a 1 column table of weekdays. If I add | stats count by weekday to the end I then have a count per weekday. Now I just need a count by CPS by Weekday!

0 Karma

pdjhh
Communicator

This looks alright...| stats count(CPS) by weekday

0 Karma

pdjhh
Communicator

That's a total of all the CPSs by weekday unfortunately. So 1 value per day instead of 1 total value per CPS per day.

0 Karma

pdjhh
Communicator

This is so close now. The following groups them properly by weekday but it doesn't sort the weekdays into order:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | eval weekday=strftime(_time, "%A") | chart count by weekday, CPS

So I tried this one but I think, because I"me using chart, the sort_field field isn't ending up in the cart to be able to be sorted on. If I change it to chart count by sort_field then it works (so the fields are being created properly) but then of course I get numbers 1 to 7 instead of the days of the week...

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | eval weekday=strftime(_time, "%A") | eval sort_field=case(weekday=="Monday",1, weekday =="Tuesday",2, weekday =="Wednesday",3, weekday =="Thursday",4, weekday =="Friday",5, weekday =="Saturday",6, weekday =="Sunday",7) | chart count by weekday, CPS usenull=false | sort by sort_field

0 Karma

HeinzWaescher
Motivator

sounds good! 🙂

0 Karma

pdjhh
Communicator

Ok that was pretty insane I had to go to these lengths to sort on the day of the week. Looked around and saw someone else suggest adding a lookup table so Splunk can get a numeric value for the day of the week (why would something so clever need this?! Recognise it's days of the week and just sort it!). I've added a lookup and sort on that field before hiding it:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | eval weekday=strftime(_time, "%A") | chart count by weekday, CPS usenull=false | lookup daystonumbers.csv weekday as weekday OUTPUT day_number as day_number | sort day_number | fields - day_number

0 Karma

pdjhh
Communicator

Any thoughts on how I can get that sorting by weekday?

0 Karma

HeinzWaescher
Motivator

Your stats command above?

This should give you one total value per CPS per weekday

| chart count by CPS, weekday

0 Karma

pdjhh
Communicator

Yes the below is nearly there..

0 Karma

HeinzWaescher
Motivator

Than this commnd should work now:

index="windows" eventtype=winregistry_windows registry_value_name=viewclient_broker_remote_ip_address registry_key_name="volatile environment" registry_value_data="*" | stats count by CPS, weekday

0 Karma

pdjhh
Communicator

This is working in a tabular format. It is listing all of the 4 CPSs 7 times with a number for the number of logins per day. If I click column chart though it spreads those 28 values across the x axis instead of providing a column per CPS per day across 7 days.

0 Karma