Chart count of results per day.

[pdjhh]

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

[somesoni2]

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]

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

[pdjhh · Accepted solution]

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.

[pdjhh]

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

[HeinzWaescher]

Do you have something like

| fields+ CPS

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

[pdjhh]

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.

[HeinzWaescher]

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

[pdjhh]

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

[HeinzWaescher]

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

[pdjhh]

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!

[pdjhh]

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

[pdjhh]

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.

[pdjhh]

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

[HeinzWaescher]

sounds good! 🙂

[pdjhh]

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

[pdjhh]

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

[HeinzWaescher]

Your stats command above?

This should give you one total value per CPS per weekday

| chart count by CPS, weekday

[pdjhh]

Yes the below is nearly there..

[HeinzWaescher]

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

[pdjhh]

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.