Hello everyone,
In my query if my field value(Current_Day,Current_Day_Actual,Current_Day_Average,DifferenceFromAverage) is zero then i am not getting the proper output.
For ex:
This is the result from my query
Hour_Of_Day Current_Day Current_Day_Actual Current_Day_Average DifferenceFromAverage
01 Wed 4 2 2
03 Wed 10 5 5
04 Wed 4 3 1
05 Wed 32 23 9
06 Wed 68 130 -62
For "hour_of_day"=01 i am not getting the output, could any one help me in this.
this the query:
index=*** eventType=*** "target{}.alternateId"=*** earliest=-4w
| rename target{}.alternateId AS "id"
| eval Hour_Of_Day = strftime(_time, "%H")
| eval Week_Day = strftime(_time,"%a")
| eval Today=strftime(now(),"%a")
| eval Current_Day=if(Week_Day=Today,Today,null())
| stats count(id) AS "Total_Login" by Hour_Of_Day,Current_Day
| eval DailyLogins=(Total_Login/4)
| stats values(DailyLogins) AS "Current_Day_Average" by Hour_Of_Day,Current_Day
| eval Current_Day_Average=ceil(Current_Day_Average)
| join Hour_Of_Day
[ search index=*** eventType=***
"target{}.alternateId"=**** earliest=@d
| rename target{}.alternateId AS "id"
| eval Hour_Of_Day = strftime(_time, "%H")
| eval time_date = strftime(_time,"%w")
| stats count(id) AS "Current_Day_Actual" by Hour_Of_Day, time_date
| chart values(Current_Day_Actual) AS Current_Day_Actual by Hour_Of_Day
| table Hour_Of_Day Current_Day_Actual]
| eval DifferenceFromAverage=(Current_Day_Actual-Current_Day_Average)
| table Hour_Of_Day,Current_Day,Current_Day_Actual,Current_Day_Average,DifferenceFromAverage
We cannot help if you do not post sample event data for us to use, preferably with a mockup of the expected output for those events.
Hi punyanit,
at first just some hints:
Anyway, if you run separately your two searches, have you all the values you're waiting for?
Then, explore a new approach, using stats command instead join that's very slow:
(index=auto_prod_okta eventType="user.authentication.sso" "target{}.alternateId"=SmartCash earliest=-4w) OR (index=auto_prod_okta eventType="user.authentication.sso" "target{}.alternateId"=SmartCash earliest=@d)
| rename target{}.alternateId AS "id" date_hour AS Hour_Of_Day date_wday AS Week_Day
| eval Current_Day=if(strftime(_time,"%Y-%m-%d")=strftime(now(),"%Y-%m-%d"),"today","old_time")
| stats count(eval(Current_Day="today") AS Current_Day_Actual count(eval(Current_Day="old_time") AS Current_Day_Average BY Hour_Of_Day
| eval Current_Day_Average=ceil(Current_Day_Average)
| eval DifferenceFromAverage=(Current_Day_Actual-Current_Day_Average)
| table Hour_Of_Day,Current_Day,Current_Day_Actual,Current_Day_Average,DifferenceFromAverage
I cannot test it but it should be correct.
Bye.
Giuseppe
The date_*
fields use the are pre-TZ-adjusted values and almost certainly incorrect.
Hi Giuseppe,
Thank you for your efforts but your query is not giving me expected results:
1.It is giving me output of all 24hrs (From 00-23) , instead of this it should give me from 00 to 09 (in hour of day field) since we have passed only 9 hrs of my current time zone.
2. I am not able use default fields (date_hour and date_wday) because this field is common for -4w and @d,
so it will give me result of all 24hrs available in a day.
Thanks in Advance ,