Usefull_scripts/Ansible/p6-log-cleanup.yaml

- hosts: all
  tasks: 
  - name: Cleanup P6 logs
    community.general.mssql_script:
      login_user: "{{ database_admin_user }}"
      login_password: "{{ database_admin_password }}"
      login_host: "{{ database_host }}"
      db: "{{ db_name }}"
      script: |
        use {{ db_name }}
        IF EXISTS (select p.pk_p6sessionid, p.createdate, p.modifydate, h.modifydate, getdate() from rsy.tblP6Session p join rsy.tblP6SessionHeartbeat h on p.pk_p6sessionid = h.fk_p6sessionid where p.createdate > DATEADD(HOUR, -6, GETDATE()) and h.modifydate > DATEADD(HOUR, -2, GETDATE())) 
        BEGIN 
        SELECT 'Possible active Sessions';
        select p.pk_p6sessionid, p.createdate, p.modifydate, h.modifydate, getdate() from rsy.tblP6Session p 
        join rsy.tblP6SessionHeartbeat h on p.pk_p6sessionid = h.fk_p6sessionid
        where p.createdate > DATEADD(HOUR, -6, GETDATE()) and h.modifydate > DATEADD(HOUR, -2, GETDATE())
        END ELSE
        BEGIN
        SELECT 'do cleanup'
        BEGIN TRAN  
        SELECT TOP (1) 1 FROM [rsy].[tblP6Session] WITH (TABLOCKX);
        truncate table [rsy].[tblP6SessionHeartbeat];		
        truncate table [rsy].[tblP6SessionLog];		
        truncate table [rsy].[tblP6SessionMilestone];		
        truncate table [rsy].[tblP6SessionPlancard];		
        truncate table [rsy].[tblP6SessionRelation];	
        alter table [rsy].[tblP6Session] disable trigger all;		
        delete from [rsy].[tblP6Session];
        alter table [rsy].[tblP6Session] enable trigger all;
        COMMIT TRAN;	   
        END
    register: db_output
  - debug: var=db_output