Automate

 View Only
Back to discussions
Expand all | Collapse all

Foundation 12.x - SQL query to extract Studio script last executed information

  • 1.  Foundation 12.x - SQL query to extract Studio script last executed information

    Posted 04-12-2022 11:42

    SQL query to run against Foundation 12 SQL databases to find the last executed date for Studio scripts is below.  Replace "SERVER_NAME" with the  Foundation server name.

     

    select * from [SERVER_NAME].[dbo].[TransactionLog]
    select ScriptUrl from [WinshuttleServer_2000].[dbo].[ServiceData] order by ScriptUrl
    select * from [SERVER_NAME].[dbo].[TransactionLog]
    select * from [SERVER_NAME].[dbo].[QueryLog]
    SELECT AllScripts.ScriptUrl,AllScripts.ServiceName
     FROM [WinshuttleServer_2000].[dbo].[ServiceData] as AllScripts
         where AllScripts.ScriptUrl NOT In
          (SELECT distinct fr.ScriptUrl
          FROM [WinshuttleServer_2000].[dbo].[ServiceData] fr
          JOIN [SERVER_NAME].[dbo].[TransactionLog] txr ON fr.SiteIdentifier = txr.ReferenceId
          where txr.TxrTitle like CONCAT('%', fr.ScriptUrl, '%')
           and DATEDIFF(MINUTE,txr.ActivityDateTime, GETUTCDATE()) >0)
    AND AllScripts.ScriptUrl NOT In
          (SELECT distinct fr.ScriptUrl
          FROM [WinshuttleServer_2000].[dbo].[ServiceData] fr
          JOIN [SERVER_NAME].[dbo].[QueryLog] qr ON fr.SiteIdentifier = qr.ReferenceId
          where qr.QueryFileName like CONCAT('%', fr.ScriptUrl, '%')
           and DATEDIFF(MINUTE,qr.DateTimeOfRun, GETUTCDATE()) >0)



    ------------------------------
    Tammy Lake | Sr. Sales Engineer
    Precisely | 281-787-7012
    ------------------------------