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
Builder

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.

alt text

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.

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

0 Karma

sundareshr
Legend

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

View solution in original post

0 Karma

pavanae
Builder

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

0 Karma

sundareshr
Legend

My bad, try the updated search

0 Karma

pavanae
Builder

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

0 Karma

sundareshr
Legend

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

0 Karma

pavanae
Builder

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

0 Karma

sundareshr
Legend

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

0 Karma

niketnilay
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

pavanae
Builder

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.