topic Re: Summarize date per week in Splunk Search

Summarize date per week

JandrevdM — Tue, 27 Aug 2024 09:02:32 GMT

Good day,

I have a query to summarize data per week. Is there a way to display my tables in a better way as my dates for the path month would just be the dates in number format?

I would like to name the table Week 1, Week 2, Week 3 etc if possible.

index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" OR app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base
| eval app=if(url_domain="www.perplexity.ai", url_domain, app)
| table user, app, _time
| stats count by user app _time
| chart count by app _time span=1w
| sort app 0

Re: Summarize date per week

JandrevdM — Tue, 27 Aug 2024 13:41:21 GMT

index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" OR app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base | eval app=if(url_domain="www.perplexity.ai", url_domain, app) | table user, app, _time | eval week_num = "Week Number" . strftime(_time, "%U") | stats count by user app week_num | chart count by app week_num | sort app 0

Re: Summarize date per week

PickleRick — Tue, 27 Aug 2024 11:31:44 GMT

1. Don't put the "table" command in that place. It doesn't do anything useful and (in distributed setup) moves the processing to the SH layer effectively losing the advantage of parallel stats processing on indexers.

2. I can't quite grasp what's the point of that | stats | chart idea. First you count, then you count the counts.

3. There is a timechart command for time series.

4. The overal idea with eval is OK but I'd rather use fieldformat - this way you can freely sort based on actual underlying time data but present the data in a human-readable way.

Re: Summarize date per week

JandrevdM — Tue, 27 Aug 2024 12:27:23 GMT

Hi @PickleRick

Thanks for the support.

The reason for the | stats | chart is to distinct my data by user. If I do not do this then I get multiple entries per user for each url. This allows for a user to only hit one url per week and then count them.

I will try the suggestion. I recently moved from kql to spl and will try and figure out the format for timechart and fieldformat.

Thank you!

Re: Summarize date per week

PickleRick — Tue, 27 Aug 2024 13:34:13 GMT

Yup. That is one of ways to handle it. 🙂