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

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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...