Hi everyone,
Since I dont have much knowledge on Splunk query language. I am struggling for the past one week to sort out the results from a splunk query which is described as below.
I want to define the "normal" time a user is working on Yesterday. This time-interval has to be between 18:00 day1 and 06:00 day2. Similarly for 7 consequent days I have to calculate his average working time range for the last 7 days which should be between 18:00 day1 and 06:00 day2 like we trying for yesterday.
And display them both.
I have acheived somewhat with the help of some of the splunksters in this site with the following Splunk Query
For some reason I couldn't able post my complete query so pasting a picture. please check the picture for my splunk query.
how can i filter my output to display only the user results which satisfies the condition substraction of the fields a_e and y_e is greater than 3 hours.
Where a_e and y_e are the epoch times converted to human readable time.
I know that I have been posting simillar questions for the past 1 week but due to my Unformulated English I failed to explain what I'm exactly looking for.
Try this. The only changes to your original query are the | where strftime(_time, "%H") >=18 OR hod<=6
AND | where (a_e-y_e)>=1800
rest of your query remains the same. I have mocked up your query with ...
inserting only the changes at relevant places in your query.
earliest=-7d@d
| eval hod=strftime(_time, "%H")
| where hod>=18 OR hod<=6
| eval days=...
| eval when=...
| stats ...
| stats ...
| where (a_e-y_e)>=1800
| convert ctime(a*) ...
| rest of your original query here
Try this. The only changes to your original query are the | where strftime(_time, "%H") >=18 OR hod<=6
AND | where (a_e-y_e)>=1800
rest of your query remains the same. I have mocked up your query with ...
inserting only the changes at relevant places in your query.
earliest=-7d@d
| eval hod=strftime(_time, "%H")
| where hod>=18 OR hod<=6
| eval days=...
| eval when=...
| stats ...
| stats ...
| where (a_e-y_e)>=1800
| convert ctime(a*) ...
| rest of your original query here
Thank you very much @sundareshr. Got an error while executing the search with your suggested changes.
"Error in 'where' command: Typechecking failed. The '>=' operator received different types."
My bad, try the updated search
thanks again for your time @sundareshr. But didn't seen any results now. just resulted as No results found.
do you get data if you remove the where
clause? try removing one at a time to see what causes no data
After removing the following lines I was able to see the data. But thank you the condition "| where (a_e-y_e)>=1800" worked as expected. @sundareshr
| eval hod=strftime(_time, "%H")
| where hod>=18 AND hod<=6
🙂 That should be where hod>=18 OR hod<=6
. I've update the original as well.
If you give an example of output from current query and some sample of how you want it to be arranged, it will be useful for others to assist.
The output I'm getting currently with the above query is as below.
user_id yesterday average_of_last_7days standard_deviation_of_last_7days
A 06:16--05:13 14:04--03:40 02:45--04:57
B 07:08--05:20 14:06--03:51 03:04--04:54
C 13:45--05:24 17:57--04:00 01:33--04:54
D 05:29--05:28 20:23--14:39 07:27--07:34
E 05:10--05:10 05:10--05:10 01:00--01:00
F 12:11--12:11 12:11--12:11 01:00--01:00
I am expection the output should contain only the time in between 19:00 PM to 6:00 AM of next day and also satifying the condition on my question.