By using AWS re:Post, you agree to the Terms of Use

Complex lag windows function help needed


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

Sample Data

Sql Code (But erroring out ) -:

      WITH base as (
                  select o.computername,
                                     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

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

    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

asked 2 months ago39 views