How can we filter our query in days like Monday to Friday and calculate their average value?

[sabasiddiqui]

How can we filter our query in days like Monday to Friday and calculate their average value. For eg, I am getting data through python script which is running every 5 minute. To calculate month to week and month to day data, which requires data from monday to friday. How can we filter or extract data from the same?

[TrangCIC81]

To filter your data to only include weekdays (Monday to Friday) and then calculate their average value, you can use the following steps:

1. Use Splunk's strftime function to extract the day of the week from the timestamp in your data. For example, if your timestamp field is called timestamp, you can extract the day of the week using the following query:

   | eval day=strftime(timestamp, "%w")

   This will create a new field called day that contains the day of the week as a number (0 for Sunday, 1 for Monday, 2 for Tuesday, and so on).

2. Filter your data to only include weekdays (Monday to Friday) by using the where command. For example:

   | where day >= 1 AND day <= 5

   This will filter your data to only include events that occurred on a weekday (Monday to Friday).

3. Calculate the average value of your data by using the stats command. For example, if your value field is called value, you can calculate the average value using the following query:

   | stats avg(value) as avg_value

   This will calculate the average value of your data for the selected time range and weekday filter.

   Putting it all together, your final query might look something like this:

   <your search> | eval day=strftime(timestamp, "%w") | where day >= 1 AND day <= 5 | stats avg(value) as avg_value

   This will filter your data to only include weekdays (Monday to Friday) and calculate their average value. Note that you will need to replace <your search> with your actual search that retrieves the data from your Python script.

   Hope this helps.

[bowesmana]

There are some fields, which are often indexed for a data source called date_* - you can see the docs about these here

https://docs.splunk.com/Documentation/Splunk/9.0.4/Admin/Propsconf#Timestamp_extraction_configuratio...

See the definition of the parameter

ADD_EXTRA_TIME_FIELDS

If these are extracted then you can simply use date_wday, which will be the full name of the day of the week, which means you can query directly for

your query... date_wday IN ("monday","tuesday","wednesday","thursday","friday")
...

and do your splits by this field

[yeahnah]

Nice, I like this method, @bowesmana 

[yeahnah]

Hi @sabasiddiqui 

This is not possible using the Time picker UI.  The easiest way (I'm aware of) is to search all the data for the whole period, then filter out the weekend days.  For example...

... your query ...
| eval day=strftime(_time, "%a")
| where NOT match(day,"Sat|Sun")
| fields - day
| stats avg(x) ... and so on ...

If you provided some examples, this could possibly be optimised a bit, but as a starting point that's how I'd do it.

Hope it helps

[yuanliu]

In the same line,

... your query ...
| where NOT strftime(_time, "%a") IN ("Sat", "Sun")
| stats avg(x) ... and so on ...

String comparison is slightly faster than regex. 

[yeahnah]

Cheers @yuanliu, that's good to know.  Always like efficiency wins