Splunk Search

Not getting proper output of query

punyanit
Path Finder

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

Tags (1)
0 Karma

woodcock
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

gcusello
Legend

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

punyanit
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 (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 ,

0 Karma
Get Updates on the Splunk Community!

Check out this month’s brand new Splunk Lantern articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...