topic Re: Not getting proper output of query in Splunk Search

Not getting proper output of query

punyanit — Wed, 30 Sep 2020 02:25:13 GMT

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

Re: Not getting proper output of query

gcusello — Wed, 30 Sep 2020 02:25:19 GMT

Hi punyanit,
at first just some hints:

you don't need of strftime to extract Hour_Of_Day and Week_Day because you can use some automaic fields: date_hour 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

Re: Not getting proper output of query

punyanit — Wed, 30 Sep 2020 02:25:28 GMT

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 ,

Re: Not getting proper output of query

woodcock — Wed, 09 Oct 2019 14:02:02 GMT

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

Re: Not getting proper output of query

woodcock — Wed, 09 Oct 2019 14:03:13 GMT

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.