Splunk Search

How to find difference between (Today's logins) and (Average logins of that particular week day for last 30days) by hour?

sai_john
New Member

I need to calculate difference between (TodayLogins-AverageLogins of that particular weekday). For that I have calculated Today's logins and (Average logins of all weekdays for last 30days) by hour separately by below searches and joined results of both using join.Can someone help with correctness of below search?
Here is the search i am trying to get difference:

index="_internal" "login" earliest=-30d |search id=""| eval time_hour = strftime(_time, "%H") |eval DayOfWeek = strftime(now(),"%w") | stats dc(id) AS "Total_Login" by time_hour, DayOfWeek| chart avg(Total_Login) AS AverageLogins over time_hour by DayOfWeek limit=0 | table time_hour AverageLogins
|join time_hour [search index="_internal" "*login
" earliest=-24h |search id="*" | eval time_hour = strftime(_time, "%H") |eval DayOfWeek = strftime(_time,"%w") |stats dc(id) AS "TodayLogins" by time_hour, DayOfWeek |chart values(TodayLogins) AS TodayLogins by time_hour limit=0 |table time_hour TodayLogins] |eval Diff=(TodayLogins-AverageLogins) | table time_hour TodayLogins AverageLogins Diff

Tags (1)
0 Karma

adonio
Ultra Champion

hello there,

try this search out:
from answer by @HeinzWaescher
https://answers.splunk.com/answers/253934/compare-result-to-other-results-on-same-day-of-the.html
index="_internal" "login"
| bucket _time span=1d
| stats count as Total_Logins by _time host
| sort 1-_time
| eval weekday=strftime(_time,"%a")
| eval today=strftime(now(), "%a")
| eval sameDay=if(today=weekday,1,0)
| search sameDay=1
| streamstats avg(Total_Logins) AS avg BY host
| streamstats current=f global=f window=1 latest(avg) as last_avg by host

see screenshot:
alt text

hope it helps

0 Karma

deepashri_123
Motivator

Hey@sai_john,

Can you try something like this

index="_internal" "login" earliest=-30d|search id="" |stats dc(id) AS "Total_Login" by date_hour, date_wday|chart avg(Total_Login) AS AverageLogins over date_hour by date_wday limit=0 | table date_hour AverageLogins |join date_hour [search index="_internal" "login" earliest=-24h |search id="" | stats dc(id) AS "TodayLogins" by date_hour, date_wday |chart values(TodayLogins) AS TodayLogins by date_hour limit=0 |table date_hour TodayLogins] |eval Diff=(TodayLogins-AverageLogins) | table date_hour TodayLogins AverageLogins Diff

Let me know if this helps!!

0 Karma

sai_john
New Member

I have noticed that average logins are not pulled up with correct results. i am looking to to get average of that particular day for last 30 days. for suppose, if you run the search on Monday, you need to get average of all mondays within last 30 days.
Is there a way to get correct average for only weekdays and then get difference with above search?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...