1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
select '217.15.90.30' as ip,login, datemax, timemax, req1.pppd
from
(select MAX(date) as datemax,time as timemax,msg,substr(msg,position('pppd' in msg),position(':' in msg) - position('pppd'in msg)) as pppd
from logs
group by time,msg) as req1,
(select replace(substr(msg,position('for' in msg),position('@' in msg) - position('CHAP peer authentication succeeded for' in msg)),'for',' ') as login,
substr(msg,position('pppd' in msg),position(':' in msg) - position('pppd'in msg)) as pppd
from logs
where msg like '%peer authentication succeeded%'
group by login,pppd) as req2
where req1.pppd = req2.pppd
and msg like '%IPCP ConfAck id=%'
and msg like '%ddr%' and msg like '%217.15.90.30%'
and msg like '%sent%'
and '217.15.90.30'= substr(msg,position('217.15.90.30' in msg),position('>' in msg) - position('217.15.90.30' in msg))
order by datemax, timemax
DESC |
