topic Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time? in Splunk Search

how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

pavanae — Tue, 29 Sep 2020 11:52:05 GMT

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.

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

niketn — Wed, 23 Nov 2016 17:05:51 GMT

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.

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

pavanae — Tue, 29 Sep 2020 11:52:08 GMT

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.

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

sundareshr — Wed, 23 Nov 2016 20:37:48 GMT

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

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

pavanae — Wed, 23 Nov 2016 20:46:47 GMT

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."

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

sundareshr — Wed, 23 Nov 2016 20:51:07 GMT

My bad, try the updated search

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

pavanae — Wed, 23 Nov 2016 21:03:49 GMT

thanks again for your time @sundareshr. But didn't seen any results now. just resulted as No results found.

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

sundareshr — Wed, 23 Nov 2016 21:36:01 GMT

do you get data if you remove the where clause? try removing one at a time to see what causes no data

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

pavanae — Tue, 29 Sep 2020 11:55:13 GMT

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

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

sundareshr — Thu, 24 Nov 2016 14:05:33 GMT

🙂 That should be where hod>=18 OR hod<=6. I've update the original as well.

topic Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time? in Splunk Search

how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields **A and B** is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

topic Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time? in Splunk Search

how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?

Re: how can i filter my output to display only the user results which satisfies the condition substraction of the fields A and B is greater than 3 hours. Where A and B are the epoch times converted to human readable time?