Archive
Highlighted

Not getting proper output of query

Path Finder

Hello everyone,

In my query if my field value(CurrentDay,CurrentDayActual,CurrentDayAverage,DifferenceFromAverage) is zero then i am not getting the proper output.
For ex:
This is the result from my query
Hour
OfDay CurrentDay CurrentDayActual CurrentDayAverage 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 "hourofday"=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 HourOfDay = strftime(time, "%H")
| eval Week
Day = strftime(time,"%a")
| eval Today=strftime(now(),"%a")
| eval Current
Day=if(WeekDay=Today,Today,null())
| stats count(id) AS "Total
Login" by HourOfDay,CurrentDay
| eval DailyLogins=(Total
Login/4)
| stats values(DailyLogins) AS "CurrentDayAverage" by HourOfDay,CurrentDay
| eval Current
DayAverage=ceil(CurrentDayAverage)
| join Hour
OfDay
[ search index=*** eventType=***
"target{}.alternateId"=**** earliest=@d
| rename target{}.alternateId AS "id"
| eval Hour
OfDay = strftime(time, "%H")
| eval timedate = strftime(time,"%w")
| stats count(id) AS "CurrentDayActual" by HourOfDay, timedate
| chart values(Current
DayActual) AS CurrentDayActual by HourOfDay
| table Hour
OfDay CurrentDayActual]
| eval DifferenceFromAverage=(Current
DayActual-CurrentDayAverage)
| table Hour
OfDay,CurrentDay,CurrentDayActual,CurrentDayAverage,DifferenceFromAverage

Tags (1)
0 Karma
Highlighted

Re: Not getting proper output of query

Legend

Hi punyanit,
at first just some hints:

  • you don't need of strftime to extract HourOfDay and WeekDay because you can use some automaic fields: datehour and date_wday;
  • to compare values of today and values of four days ago, you can use the command wimewrap.

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

0 Karma
Highlighted

Re: Not getting proper output of query

Path Finder

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 (datehour and datewday) 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 ,

0 Karma
Highlighted

Re: Not getting proper output of query

Esteemed Legend

The date_* fields use the are pre-TZ-adjusted values and almost certainly incorrect.

0 Karma
Highlighted

Re: Not getting proper output of query

Esteemed Legend

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.

0 Karma