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

[sai_john]

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

[adonio]

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:

hope it helps

[deepashri_123]

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

[sai_john]

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?