Mercurial > hg > orthanc-databases

changeset 709:a88a2776fea4 sql-opti

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
get audit-logs
author Alain Mazy <am@orthanc.team>
date Thu, 17 Jul 2025 12:35:36 +0200
parents 43245004c8e2
children 2f2036e0f352
files Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/Plugins/IDatabaseBackend.h Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h Framework/PostgreSQL/PostgreSQLResult.cpp Framework/PostgreSQL/PostgreSQLResult.h PostgreSQL/NEWS PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp Resources/Orthanc/Plugins/OrthancPluginCppWrapper.h
diffstat 11 files changed, 284 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Thu Jul 17 12:35:36 2025 +0200
@@ -29,6 +29,7 @@
 #include "DynamicIndexConnectionsPool.h"
 #include "IndexConnectionsPool.h"
 #include "MessagesToolbox.h"
+#include <Toolbox.h>
 
 #include <OrthancDatabasePlugin.pb.h>  // Include protobuf messages
 
@@ -1547,6 +1548,105 @@
     return OrthancPluginErrorCode_Success;                                     
   }
   
+  void GetAuditLogs(OrthancPluginRestOutput* output,
+                    const char* /*url*/,
+                    const OrthancPluginHttpRequest* request)
+  {
+    OrthancPluginContext* context = OrthancPlugins::GetGlobalContext();
+
+    if (request->method != OrthancPluginHttpMethod_Get)
+    {
+      OrthancPluginSendMethodNotAllowed(context, output, "GET");
+    }
+
+    OrthancPlugins::GetArguments getArguments;
+    OrthancPlugins::GetGetArguments(getArguments, request);
+
+    std::string userIdFilter;
+    std::string resourceIdFilter;
+    std::string actionFilter;
+    uint64_t since = 0;
+    uint64_t limit = 0;
+
+    uint64_t fromTs = 0;
+    uint64_t toTs = 0;
+
+    if (getArguments.find("user-id") != getArguments.end())
+    {
+      userIdFilter = getArguments["user-id"];
+    }
+
+    if (getArguments.find("resource-id") != getArguments.end())
+    {
+      resourceIdFilter = getArguments["resource-id"];
+    }
+
+    if (getArguments.find("action") != getArguments.end())
+    {
+      actionFilter = getArguments["action"];
+    }
+
+    if (getArguments.find("limit") != getArguments.end())
+    {
+      limit = boost::lexical_cast<uint64_t>(getArguments["limit"]);
+    }
+
+    if (getArguments.find("since") != getArguments.end())
+    {
+      since = boost::lexical_cast<uint64_t>(getArguments["since"]);
+    }
+
+    if (getArguments.find("from-timestamp") != getArguments.end())
+    {
+      fromTs = boost::lexical_cast<uint64_t>(getArguments["from-timestamp"]);
+    }
+
+    if (getArguments.find("to-timestamp") != getArguments.end())
+    {
+      toTs = boost::lexical_cast<uint64_t>(getArguments["to-timestamp"]);
+    }
+
+    std::list<IDatabaseBackend::AuditLog> logs;
+
+    BaseIndexConnectionsPool::Accessor accessor(*connectionPool_);
+    accessor.GetBackend().GetAuditLogs(accessor.GetManager(),
+                                       logs,
+                                       userIdFilter,
+                                       resourceIdFilter,
+                                       actionFilter,
+                                       fromTs, toTs,
+                                       since, limit);
+
+    // note: right now, we assume the logData is always Json
+    Json::Value jsonLogs;
+    
+    for (std::list<IDatabaseBackend::AuditLog>::const_iterator it = logs.begin(); it != logs.end(); ++it)
+    {
+      Json::Value serializedAuditLog;
+      serializedAuditLog["Timestamp"] = it->timeStamp;
+      serializedAuditLog["UserId"] = it->userId;
+      serializedAuditLog["ResourceId"] = it->resourceId;
+      serializedAuditLog["ResourceType"] = it->resourceType;
+      serializedAuditLog["Action"] = it->action;
+      
+      if (it->logData.empty())
+      {
+        serializedAuditLog["LogData"] = Json::nullValue;
+      }
+      else
+      {
+        Json::Value logData;
+        Orthanc::Toolbox::ReadJson(logData, it->logData);
+        serializedAuditLog["LogData"] = logData;
+      }
+      
+      jsonLogs.append(serializedAuditLog);
+    }
+
+    OrthancPlugins::AnswerJson(jsonLogs, output);
+
+  }
+
   void DatabaseBackendAdapterV4::Register(IndexBackend* backend,
                                           size_t countConnections,
                                           bool useDynamicConnectionPool,
@@ -1583,6 +1683,7 @@
 
 #if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1
     OrthancPluginRegisterAuditLogHandler(context, AuditLogHandler);
+    OrthancPlugins::RegisterRestCallback<GetAuditLogs>("/plugins/postgresql/audit-logs", true);
 #endif
   }
 
--- a/Framework/Plugins/IDatabaseBackend.h	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/Plugins/IDatabaseBackend.h	Thu Jul 17 12:35:36 2025 +0200
@@ -37,6 +37,32 @@
   class IDatabaseBackend : public boost::noncopyable
   {
   public:
+    struct AuditLog
+    {
+      uint64_t timeStamp;
+      std::string userId;
+      OrthancPluginResourceType resourceType;
+      std::string resourceId;
+      std::string action;
+      std::string logData;
+
+      AuditLog(uint64_t timeStamp,
+               const std::string& userId,
+               OrthancPluginResourceType resourceType,
+               const std::string& resourceId,
+               const std::string& action,
+               const std::string& logData) :
+        timeStamp(timeStamp),
+        userId(userId),
+        resourceType(resourceType),
+        resourceId(resourceId),
+        action(action),
+        logData(logData)
+      {
+      }
+    };
+
+  public:
     virtual ~IDatabaseBackend()
     {
     }
@@ -469,6 +495,16 @@
                                 const std::string& action,
                                 const void* logData,
                                 uint32_t logDataSize) = 0;
+
+    virtual void GetAuditLogs(DatabaseManager& manager,
+                              std::list<AuditLog>& logs,
+                              const std::string& userIdFilter,
+                              const std::string& resourceIdFilter,
+                              const std::string& actionFilter,
+                              uint64_t fromTs,
+                              uint64_t toTs,
+                              uint64_t since,
+                              uint64_t limit) = 0;
 #endif
 
     virtual bool HasPerformDbHousekeeping() = 0;
--- a/Framework/Plugins/IndexBackend.cpp	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Thu Jul 17 12:35:36 2025 +0200
@@ -4722,6 +4722,93 @@
 
       statement.Execute(args);
     }
+
+    void IndexBackend::GetAuditLogs(DatabaseManager& manager,
+                                    std::list<AuditLog>& logs,
+                                    const std::string& userIdFilter,
+                                    const std::string& resourceIdFilter,
+                                    const std::string& actionFilter,
+                                    uint64_t fromTs,
+                                    uint64_t toTs,
+                                    uint64_t since,
+                                    uint64_t limit)
+    {
+      LookupFormatter formatter(manager.GetDialect());
+      std::vector<std::string> filters;
+
+      std::string sql = "SELECT ts, userId, resourceType, resourceId, action, logData FROM AuditLogs ";
+
+      if (!userIdFilter.empty())
+      {
+        filters.push_back("userId = " + formatter.GenerateParameter(userIdFilter));
+      }
+
+      if (!resourceIdFilter.empty())
+      {
+        filters.push_back("resourceId = " + formatter.GenerateParameter(resourceIdFilter));
+      }
+
+      if (!actionFilter.empty())
+      {
+        filters.push_back("action = " + formatter.GenerateParameter(actionFilter));
+      }
+
+      if (fromTs > 0)
+      {
+        filters.push_back("ts >= " + formatter.GenerateParameter(fromTs));
+      }
+
+      if (toTs > 0)
+      {
+        filters.push_back("ts < " + formatter.GenerateParameter(toTs));
+      }
+
+      if (filters.size() > 0)
+      {
+        std::string joinedFilters;
+        Orthanc::Toolbox::JoinStrings(joinedFilters, filters, " AND ");
+        sql += " WHERE " + joinedFilters;
+      }
+
+      if (since > 0 || limit > 0)
+      {
+        sql += formatter.FormatLimits(since, limit);
+      }
+
+      sql += " ORDER BY ts ASC";
+
+      DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
+      statement.SetReadOnly(true);
+
+      statement.Execute(formatter.GetDictionary());
+
+      if (!statement.IsDone())
+      {
+        if (statement.GetResultFieldsCount() != 6)
+        {
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        }
+        
+        statement.SetResultFieldType(0, ValueType_Integer64);
+        statement.SetResultFieldType(1, ValueType_Utf8String);
+        statement.SetResultFieldType(2, ValueType_Integer64);
+        statement.SetResultFieldType(3, ValueType_Utf8String);
+        statement.SetResultFieldType(4, ValueType_Utf8String);
+        statement.SetResultFieldType(5, ValueType_BinaryString);
+
+        while (!statement.IsDone())
+        {
+          logs.push_back(AuditLog(statement.ReadInteger64(0),
+                                  statement.ReadString(1),
+                                  static_cast<OrthancPluginResourceType>(statement.ReadInteger64(2)),
+                                  statement.ReadString(3),
+                                  statement.ReadString(4),
+                                  statement.ReadStringOrNull(5)));
+
+          statement.Next();
+        }
+      }
+    }
 #endif
 
 }
--- a/Framework/Plugins/IndexBackend.h	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/Plugins/IndexBackend.h	Thu Jul 17 12:35:36 2025 +0200
@@ -529,6 +529,17 @@
                                 const std::string& action,
                                 const void* logData,
                                 uint32_t logDataSize) ORTHANC_OVERRIDE;
+
+    virtual void GetAuditLogs(DatabaseManager& manager,
+                              std::list<AuditLog>& logs,
+                              const std::string& userIdFilter,
+                              const std::string& resourceIdFilter,
+                              const std::string& actionFilter,
+                              uint64_t fromTs,
+                              uint64_t toTs,
+                              uint64_t since,
+                              uint64_t limit) ORTHANC_OVERRIDE;
+
 #endif
 
 
--- a/Framework/PostgreSQL/PostgreSQLResult.cpp	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLResult.cpp	Thu Jul 17 12:35:36 2025 +0200
@@ -162,6 +162,13 @@
     return htobe64(*reinterpret_cast<int64_t*>(v));
   }
 
+  int64_t PostgreSQLResult::GetTimestamp(unsigned int column) const
+  {
+    CheckColumn(column, TIMESTAMPOID);
+    assert(PQfsize(reinterpret_cast<PGresult*>(result_), column) == 8);
+    char *v = PQgetvalue(reinterpret_cast<PGresult*>(result_), position_, column);
+    return htobe64(*reinterpret_cast<int64_t*>(v));
+  }
 
   std::string PostgreSQLResult::GetString(unsigned int column) const
   {
@@ -287,6 +294,9 @@
       case VOIDOID:
         return NULL;
 
+      case TIMESTAMPOID:
+        return new Integer64Value(GetTimestamp(column));
+
       default:
         throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
     }
--- a/Framework/PostgreSQL/PostgreSQLResult.h	Tue Jul 15 14:24:14 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLResult.h	Thu Jul 17 12:35:36 2025 +0200
@@ -72,6 +72,8 @@
 
     int64_t GetInteger64(unsigned int column) const;
 
+    int64_t GetTimestamp(unsigned int column) const;
+
     std::string GetString(unsigned int column) const;
 
     void GetBinaryString(std::string& target,
--- a/PostgreSQL/NEWS	Tue Jul 15 14:24:14 2025 +0200
+++ b/PostgreSQL/NEWS	Thu Jul 17 12:35:36 2025 +0200
@@ -22,6 +22,7 @@
   of active connections.
 * Added support for AuditLogs.  The PostgreSQL plugin is listening to audit logs
   and is storing them in the SQL Database.
+  They can be browsed through the API route /plugins/postgresql/audit-logs
 
 Maintenance:
 * Optimized the CreateInstance SQL query.
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql	Tue Jul 15 14:24:14 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql	Thu Jul 17 12:35:36 2025 +0200
@@ -238,6 +238,7 @@
 
 DROP INDEX IF EXISTS AuditLogsUserId;
 DROP INDEX IF EXISTS AuditLogsResourceId;
+DROP INDEX IF EXISTS AuditLogsAction;
 DROP TABLE IF EXISTS AuditLogs;
 
 
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Tue Jul 15 14:24:14 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Thu Jul 17 12:35:36 2025 +0200
@@ -835,6 +835,7 @@
 
 CREATE INDEX IF NOT EXISTS AuditLogsUserId ON AuditLogs (userId);
 CREATE INDEX IF NOT EXISTS AuditLogsResourceId ON AuditLogs (resourceId);
+CREATE INDEX IF NOT EXISTS AuditLogsAction ON AuditLogs (action);
 
 
 
--- a/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp	Tue Jul 15 14:24:14 2025 +0200
+++ b/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp	Thu Jul 17 12:35:36 2025 +0200
@@ -266,6 +266,16 @@
 #endif
 
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 7, 0)
+  void MemoryBuffer::AssignJson(const Json::Value& value)
+  {
+    std::string s;
+    WriteFastJson(s, value);
+    Assign(s);
+  }
+#endif
+
+
   void MemoryBuffer::Assign(OrthancPluginMemoryBuffer& other)
   {
     Clear();
@@ -4112,6 +4122,16 @@
   }
 #endif
 
+  void GetGetArguments(GetArguments& result, const OrthancPluginHttpRequest* request)
+  {
+    result.clear();
+
+    for (uint32_t i = 0; i < request->getCount; ++i)
+    {
+      result[request->getKeys[i]] = request->getValues[i];
+    }    
+  }
+
   void GetHttpHeaders(HttpHeaders& result, const OrthancPluginHttpRequest* request)
   {
     result.clear();
@@ -4212,6 +4232,11 @@
     {
       path_ += "?" + getArguments;
     }
+
+    if (request->bodySize > 0 && request->body != NULL)
+    {
+      requestBody_.assign(reinterpret_cast<const char*>(request->body), request->bodySize);
+    }
   }
 #endif
 
--- a/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.h	Tue Jul 15 14:24:14 2025 +0200
+++ b/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.h	Thu Jul 17 12:35:36 2025 +0200
@@ -180,6 +180,8 @@
 {
   typedef std::map<std::string, std::string>  HttpHeaders;
 
+  typedef std::map<std::string, std::string>  GetArguments;
+
   typedef void (*RestCallback) (OrthancPluginRestOutput* output,
                                 const char* url,
                                 const OrthancPluginHttpRequest* request);
@@ -231,6 +233,10 @@
     void Assign(const std::string& s);
 #endif
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 7, 0)
+    void AssignJson(const Json::Value& value);
+#endif
+
     // This transfers ownership from "other" to "this"
     void Assign(OrthancPluginMemoryBuffer& other);
 
@@ -1427,6 +1433,9 @@
 // helper method to re-serialize the get arguments from the SDK into a string
 void SerializeGetArguments(std::string& output, const OrthancPluginHttpRequest* request);
 
+// helper method to convert Get arguments from the plugin SDK to a std::map
+void GetGetArguments(GetArguments& result, const OrthancPluginHttpRequest* request);
+
 #if HAS_ORTHANC_PLUGIN_WEBDAV == 1
   class IWebDavCollection : public boost::noncopyable
   {