Splunk Search

Counting the Total Number of Days for all Time

TJemisonIpacc
Explorer

Hello.

I'm trying to create a search that averages a sum of payments and counts the total number of days for all time(starts at the first event and keeps going until the last). The problem is the second part. My current search only returns how many events took place on each day, which gives me huge numbers. I'm only looking for the total number of each day of the week over a time period.

This is what I have right now:

base search | rex field=_raw "(?P<paymentAmount>\w+) date_wday: (?P<time>\d+)ms" | eval date_wday=strftime(_time,"%w-%A")| stats sum(paymentAmount), count(date_wday)  by date_wday| eval date_wday=replace(date_wday,"(\d+-)(\w+)","\2")

Any help would be much appreciated.

Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

First of all: beware the built-in date_wday (I see you are making your own, which is VERY GOOD). In most Splunk datasets the date_* fields almost always exist and because of this you may not notice that sometimes they don’t! Most people naturally assume these fields are default fields (like _time) that are always there but these fields are actually pre-TZ-normalization side-effects from Splunk’s timestamping process. If Splunk does not (have to) parse an event to set its timestamp then the date_* fields will not exist. Windows event logs, for example, now come in via a modular input that is designed to use the pre-parsed time as it comes from the Windows event log APIs, obviating the need for Splunk to do any timestamp parsing, therefore the date_* fields are not created, do not exist, and are unavailable for our use. Did you catch that they are pre-TZ-normalization? That means that if you modified the TZ, then these fields DID NOT GET MODIFIED and ARE NOT CORRECT.

Anyway, your search is pretty much OK if you are trying to group all Fridays together and all Saturdays together, etc:

 base search | rex "(?<paymentAmount>\w+) date_wday: (?<time>\d+)ms" | eval date_wday=strftime(_time,"%A") | stats sum(paymentAmount), count BY date_wday

But, another way to read your post is that you'd like a summary for each day, which is different:

 base search | rex "(?<paymentAmount>\w+) date_wday: (?<time>\d+)ms" | bucket _time span=1d | stats sum(paymentAmount), count BY _time

Is there a reason that you are capturing field time but not using it?

View solution in original post

stephanefotso
Motivator

Hello! As woodcock is saying, you are not using the time you have extracted. Also, i think you don't need the by clause herere. I propose that you opdate your query like this:

 base search | rex field=_raw "(?P<paymentAmount>\w+) date_wday: (?P<time>\d+)ms" | eval date_wday=strftime(time,"%w-%A")| stats sum(paymentAmount), count(date_wday)  | eval date_wday=replace(date_wday,"(\d+-)(\w+)","\2")

Thanks

SGF
0 Karma

TJemisonIpacc
Explorer

I tried this out using the code you posted and the code I posted in reponse to woodcock's comment. The search returned zero as the value for count. Is there a reason you wanted me to remove the by clause? I'm fairly new to Splunk, but I think I'll need it if I want the total number of days for each day of the week.

0 Karma

stephanefotso
Motivator

Sorry, but i don't understand when you say, you want the total number of days for each day of the week and that is why i'v said you don't need the by date_wday clause here. I'm really sorry.

Thanks

SGF
0 Karma

TJemisonIpacc
Explorer

If I wanted data from last May, I would want Splunk to tell me that there were 4 Mondays , 4 Tuesdays, 4 Wednesdays, 4 Thursdays, 5 Fridays, 5 Saturdays, and 5 Sundays in that month. Does that clear things up?

0 Karma

stephanefotso
Motivator

wo wo wo. Thanks. That is verry clear now. Sorry

Thanks again!

SGF
0 Karma

woodcock
Esteemed Legend

First of all: beware the built-in date_wday (I see you are making your own, which is VERY GOOD). In most Splunk datasets the date_* fields almost always exist and because of this you may not notice that sometimes they don’t! Most people naturally assume these fields are default fields (like _time) that are always there but these fields are actually pre-TZ-normalization side-effects from Splunk’s timestamping process. If Splunk does not (have to) parse an event to set its timestamp then the date_* fields will not exist. Windows event logs, for example, now come in via a modular input that is designed to use the pre-parsed time as it comes from the Windows event log APIs, obviating the need for Splunk to do any timestamp parsing, therefore the date_* fields are not created, do not exist, and are unavailable for our use. Did you catch that they are pre-TZ-normalization? That means that if you modified the TZ, then these fields DID NOT GET MODIFIED and ARE NOT CORRECT.

Anyway, your search is pretty much OK if you are trying to group all Fridays together and all Saturdays together, etc:

 base search | rex "(?<paymentAmount>\w+) date_wday: (?<time>\d+)ms" | eval date_wday=strftime(_time,"%A") | stats sum(paymentAmount), count BY date_wday

But, another way to read your post is that you'd like a summary for each day, which is different:

 base search | rex "(?<paymentAmount>\w+) date_wday: (?<time>\d+)ms" | bucket _time span=1d | stats sum(paymentAmount), count BY _time

Is there a reason that you are capturing field time but not using it?

TJemisonIpacc
Explorer

Yes, I'm trying to group all Fridays, Saturdays, Mondays, etc. together. I still think the search is returning the wrong results, however. Right now it is telling me that there are only two Sundays, which isn't true. The events go back to January, so there should be roughly 25 or so Sundays. Another issue is that I actually overlooked the fact that there was already a date_wday field for the reported evetns. I'm not sure if it would cause some kind of overlap issue, so I changed it to this:

 base search | rex "(?<paymentAmount>\w+) days: (?<time>\d+)ms" | eval days=strftime(_time,"%A") | stats sum(paymentAmount), count BY days

No, there isn't a reason I captured the time field. Should I put something else in its place?

0 Karma

woodcock
Esteemed Legend

It is counting ONLY those Sundays which have events so the search is correct. If you'd like to count all Sundays, then (first say so) and then do this:

 base search | rex "(?<paymentAmount>\w+) date_wday: (?<time>\d+)ms" | timechart span=1d sum(paymentAmount) AS paymentAmount count | eval date_wday=strftime(_time,"%A") | stats sum(paymentAmount), sum(count) AS count BY date_wday

The difference is that timechart creates 0-value rows but stats does not.

0 Karma

TJemisonIpacc
Explorer

Sorry to keep bothering you, but could you give an example of how I would tell Splunk to count all Sundays (the step I need to do before using the code you posted)?

0 Karma

woodcock
Esteemed Legend

The code that I just posted is the complete answer and already does that; the explanation below it was bonus.

0 Karma

TJemisonIpacc
Explorer

It worked. Thank you!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...