time and punct compare entries conditions

[cpeteman]

Currently I am using the search over two hours:

<searchterms> earliest=-2h latest=now() | dedup punct,_time| eval TimeInHour=_time%3600 | rex mode=sed "s/ \d{1,2}:\d{1,2}:\d{1,2}//g" | table _raw,_time,TimeInHour,punct | sort TimeInHour,_raw 

To get results:

       _time               TimeInHour     punct
1   7/31/13 2:00:00.000 PM  0   --_::_)_@@@_________:_[_/_]__@_/()_->---@...@:/__/
2   7/31/13 2:00:00.000 PM  0   --_::_...@:/__/__________://__/-
3   7/31/13 1:00:00.000 PM  0   --_::_...@:/__/__________://__/-
4   7/31/13 1:00:00.000 PM  0   --_::_/()_->---@...@:/__/__________://__/-
5   7/31/13 2:00:00.000 PM  0   --_::_:_---:___-_(_...@)__;____________
6   7/31/13 1:00:00.000 PM  0   --_::_:_---:___-_(_...@)__;____________
7   7/31/13 2:00:00.000 PM  0   --_::_:_---:____-_(_...@)
8   7/31/13 1:00:00.000 PM  0   --_::_:_---:____-_(_...@)
9   7/31/13 2:00:00.000 PM  0   --_::_:_-:______-;________.
10  7/31/13 2:00:00.000 PM  0   --_::_:_::(.::())_---:___,_
11  7/31/13 2:00:00.000 PM  0   --_::_:_::(.::())_@@@_________:_[_/_]__@_/()_->---
12  7/31/13 1:00:00.000 PM  0   --_::_:_::(.::())_@@@_________:_[_/_]__@_/()_->---
13  7/31/13 1:00:00.000 PM  0   --_::_:_::(.::())___,__-,__

I want to do is to only get the results that have the same punct and TimeInHour as an entry in a different hour (different _time):

       _time               TimeInHour     punct
2   7/31/13 2:00:00.000 PM  0   --_::_...@:/__/__________://__/-
3   7/31/13 1:00:00.000 PM  0   --_::_...@:/__/__________://__/-
5   7/31/13 2:00:00.000 PM  0   --_::_:_---:___-_(_...@)__;____________
6   7/31/13 1:00:00.000 PM  0   --_::_:_---:___-_(_...@)__;____________
7   7/31/13 2:00:00.000 PM  0   --_::_:_---:____-_(_...@)
8   7/31/13 1:00:00.000 PM  0   --_::_:_---:____-_(_...@)
11  7/31/13 2:00:00.000 PM  0   --_::_:_::(.::())_@@@_________:_[_/_]__@_/()_->---
12  7/31/13 1:00:00.000 PM  0   --_::_:_::(.::())_@@@_________:_[_/_]__@_/()_->---

I think I need to use a "|search" of some sort. Please help

[kristian_kolb]

earliest = -2h@h latest=@h | stats dc values(date_hour) by punct | search c=2 | table date_hour punct

you can then sort on what column you like. If you want a more proper timestamp, you can insert bucket _time span=1h before the stats and use _time instead of date_hour.

[HiroshiSatoh]

Do you no good in this?

TimeInHour=_time%3600 -> TimeInHour=date_hour
sort TimeInHour,_raw -> sort TimeInHour,punct,_time

[HiroshiSatoh]

Excuse me, sir. It was that the wrong answer and not converted time. And what if you only extract multiple data sub-search?

earliest=-2h latest=now()| dedup punct,_time|eval TimeInHour=_time%3600|join [search earliest=-2h latest=now()| dedup punct,_time|eval TimeInHour=_time%3600| stats count by TimeInHour,punct|where count>1] | rex mode=sed "s/ \d{1,2}:\d{1,2}:\d{1,2}//g" | table _raw,_time,TimeInHour,punct | sort TimeInHour,_raw

[cpeteman]

date_hour gives the hour of the day I think. I wanted the seconds of that hour.