Complex lag windows function help needed
0
Im racking my brain about how to do this and I dont think its possible but not sure what to even search for. So the below is a subset of the data (using example numbers)
I am trying to use the lag function to populate the flag column.
Status Flag
Connected -> Exiting Maillink
Exiting -> Not equal to Connected OnCorp ( All rows stay OnCorp until
we see Connected)
Connected -> Reconnecting Mailink (It should stay Mailink for
all rows until we see "Exiting"
status )
Is it possible in Sql?
Using lag function in case statement to compare previous state is causing error
Sql Code (But erroring out ) -:
WITH base as (
select o.computername,
o.currentuser,
o.datetime,
message,
CASE
WHEN
LEN(split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1)) <> 0
THEN split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1)
WHEN (message like 'WARNING%' OR message like 'OpenVPN%') THEN 'Start'
WHEN message = 'SIGTERM[hard,] received, process exiting' THEN 'Exit1'
WHEN message = 'Closing TUN/TAP interface' THEN 'Exit2'
ELSE 'NO Status' END State1,
CASE WHEN State1 = 'Start' THEN 1
WHEN State1 = 'RESOLVE' THEN 2
WHEN State1 = 'WAIT' THEN 3
WHEN State1 = 'AUTH' THEN 4
WHEN State1 = 'GET_CONFIG' THEN 5
WHEN State1 = 'ASSIGN_IP' THEN 6
WHEN State1 = 'ADD_ROUTES' THEN 7
WHEN State1 = 'CONNECTED' THEN 8
WHEN State1 = 'EXITING' THEN 9
END orderofoperation
--row_number() over (partition by o.computername,o.currentuser,DATE(o.datetime) order by o.computername,o.currentuser,o.datetime) as rownumber
from maillink_openvpn_logs_ext_schema.open_vpn_filtered o
where o.message != 'message'
and currentuser = 'wuellie'
--and State1 in ('EXITING' ,'OpenVPN','RESOLVE','WAIT','AUTH','ASSIGN_IP','GET_CONFIG','ADD_ROUTES')
order by o.datetime,orderofoperation
)
select final.*,
case when final.previous_record_state is null then ‘oncorp’
when final.currentstate = ‘CONNECTED’ then ‘maillink’
when final.previous_record_state is not null and final.previous_record_state not in (‘CONNECTED’)
then lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime
when previous_record_state in (‘EXITING’) and lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime = ‘maillink’
then ‘oncorp’
else lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime
end as flag
from
(
select b.computername,b.currentuser,b.State1 currentstate,b.datetime,
lag(State1) over (partition by b.computername,b.currentuser,(b.datetime) ::DATE order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as previous_record_state
--lag(datetime) over (partition by b.computername,b.currentuser,b.datetime ::DATE order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as nextdatetime
from base b
order by b.datetime,orderofoperation
)final
Computername username State datetime onCorp/mailink
xyx ads start 2022-07-06T06:45:51 ONCORP
xyx ads RESOLVE 2022-07-06T07:06:45 ONCORP
xyx ads WAIT 2022-07-06T07:06:45 ONCORP
xyx ads AUTH 2022-07-06T07:07:00 ONCORP
xyx ads GET_CONFIG 2022-07-06T07:07:00 ONCORP
xyx ads ADD_ROUTES 2022-07-06T07:07:01 ONCORP
xyx ads CONNECTED 2022-07-06T07:07:01 MAILINK
xyx ads EXITING 2022-07-06T07:07:01 MAILINK
xyx ads RESOLVE 2022-07-06T07:07:46 ONCORP
xyx ads WAIT 2022-07-06T07:07:46 ONCORP
xyx ads AUTH 2022-07-06T07:07:50 ONCORP
xyx ads GET_CONFIG 2022-07-06T07:07:51 ONCORP
xyx ads ADD_ROUTES 2022-07-06T07:07:51 ONCORP
xyx ads CONNECTED 2022-07-06T07:07:52 MAILINK
xyx ads RECONNECTED 2022-07-06T07:08:01 MAILINK
xyx ads WAIT 2022-07-06T07:08:02 MAILINK
xyx ads AUTH 2022-07-06T07:08:09 MAILINK
xyx ads RECONNECTED 2022-07-06T07:08:10 MAILINK
xyx ads CONNECTED 2022-07-06T07:08:15 MAILINK
xyx ads RECONNECTED 2022-07-06T07:08:20 MAILINK
xyx ads CONNECTED 2022-07-06T07:09:01 MAILINK
xyx ads EXITING 2022-07-06T07:10:50 MAILINK
xyx ads START 2022-07-06T07:11:50 ONCORP
preguntada hace 2 años75 visualizacioneslg...
No hay respuestas
- Más nuevo
- Más votos
- Más comentarios
Contenido relevante
- OFICIAL DE AWSActualizada hace 3 años
- OFICIAL DE AWSActualizada hace un año