使用 Fabric 创建与自动化相关的查询

备注

免责声明：本教程中使用的场景、查询示例和数据均为虚构，可能包含错误和低效，仅供演示之用。

自动化相关表的列表

下表列出了经常用于报告和可观察性的自动化相关表。

Display name	Object name	目的
流日志	flowlog	包含各种日志，如自定义日志、桌面流操作日志 V2、机器运行队列日志、无人值守自愈请求/响应和工作队列处理日志等。数据存储在 Dataverse 弹性表中，根据日志类型的不同，可在组织表（FlowLogsTtlInMinutes 和 DesktopFlowQueueLogsTtlInMinutes）中配置自己的生存时间（TTL）设置，该设置定义了记录从表中自动删除的时间。
流计算机	flowmachine	包含计算机和托管计算机相关信息。
流计算机组	flowmachinegroup	包含计算机组和托管计算机组相关信息。
流运行	flowrun	包含云端流运行相关数据，如开始、结束、持续时间、父流上下文等。
流会话	flowsession	包含桌面流运行相关数据，如开始、持续时间、状态、机器、机器人账户、父流上下文等。
流程	工作流	包含桌面流和基于解决方案的云端流（以及其他工作流类型）。
User	systemuser	代表 Dataverse 用户。
工作队列	工作队列	表示工作流执行的实例。
工作队列项	workqueueitem	包含有关工作流每次运行的信息。

简化的表格关系图

该图像仅显示自动化的相关表关系。

在 Fabric 中创建您的第一个查询

请按照以下步骤在 Fabric 中的 SQL 分析端点上为 contoso_westus_accounts_payable Lakehouse 创建示例 SQL 查询。

1. 打开网络浏览器，转到 Microsoft Fabric 门户 (https://powerbi.com) 并使用您的凭据登录。

2. 选择 Lakehouse 所在的工作区，然后选择所需的 SQL 分析端点（Lakehouse 的子节点）。

3. 在 SQL 分析端点中，选择新 SQL 查询打开 SQL 查询编辑器。

4. 在 SQL 查询编辑器中输入 SQL 查询，然后选择运行。 以下示例查询检索与特定桌面流关联的所有桌面流运行（流会话）以及过去 7 天内失败的计算机 ID。

      SELECT   
          flowsessionid,  
          statuscode,  
          startedon, 
          completedon,
          errorcode,  
          errormessage,  
          sessionusername,  
          runexecutionduration,  
          runduration,  
          runwaitduration,  
          context
      FROM   
          flowsession  
      WHERE   
          regardingobjectid = '[specific_flow_id]' -- Replace with the actual flow ID  
          AND machineid = '[specific_machine_guid]'  -- Replace with the actual machine ID  
          AND statuscode = 8 -- 'Failed' sessions  
          AND createdon >= DATEADD(day, -7, GETDATE())  
      ORDER BY   
          createdon DESC;  
   

5. 下面是 Flow Sessions（桌面流运行）表的可用状态原因（状态代码）列表。

   状态描述	价值
   已暂停	1
   跑步	2
   等待中	3
   已成功	4
   已跳过	5
   暂停	6
   已取消	7
   Failed	8
   已出错	9
   TimedOut	10
   已中止	11
   已忽略	12

6. 查看查询结果以确保它们满足您的需求。

   

7. （可选）选中 SQL 查询并在查询输出部分选择在 Excel 中打开，在 Excel 中打开带结果的实时查询。 这样就会生成一个包含实时查询结果的 Excel 文件并下载到 SQL Analytics 端点，以便进一步分析。

   

8. （可选）要存储 SQL 查询以供将来使用，请选择保存查询。

基本流查询

检索云端流及其所有者信息

此查询将返回所有云端流及其所有者信息。

备注

只有属于 Dataverse 解决方案的云端流才能在 Fabric 中使用。

    SELECT   
        w.name AS 'Cloud flow',  
        w.workflowid AS 'Cloud flow Id',  
        w.createdon AS 'Created on',
        w.modifiedon AS 'Last modified on',
        w.clientdata AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 5;  -- Only consider solution-cloud flows (category 5)  

检索包含所有者信息的桌面流

此查询返回所有桌面流及其所有者信息。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',
        w.modifiedon AS 'Last modified on',
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6;  -- Only consider desktop flows (category 6)  

性能相关查询示例

此查询将检索指定桌面流的桌面流运行（流会话）的运行时间最小值、平均值（平均）、最大值和标准偏差，运行时间从毫秒转换为四舍五入到最接近的整秒。 查询按机器 ID 对结果进行分组，包括机器名称、管理类型、最大托管机器数、会话容量以及机器组和机器表中的最后一次心跳日期等详细信息。

    SELECT   
        f.machineid,  
        fm.name AS machine_name,  
        CASE   
            WHEN mg.managementtype = 0 THEN 'Regular Machine (Group)'  
            ELSE 'Hosted Machine (Group)'  
        END AS managementtype,  
        mg.maxmanagedmachinecount AS maxmanagedmachinecount,  
        fm.lastheartbeatdate AS last_heartbeat_date,  
        fm.sessioncapacity AS 'Max Parallel Sessions',    
        CEILING(MIN(f.runduration) / 1000.0) AS min_runtime,  
        CEILING(AVG(f.runduration) / 1000.0) AS mean_runtime,  
        CEILING(MAX(f.runduration) / 1000.0) AS max_runtime,  
        CEILING(STDEV(f.runduration) / 1000.0) AS stdev_runtime
    FROM   
        flowsession f  
    JOIN   
        flowmachinegroup mg ON f.machinegroupid = mg.flowmachinegroupid  
    JOIN   
        flowmachine fm ON f.machinegroupid = fm.flowmachinegroupid  
    WHERE   
        f.regardingobjectid = '[specific_flow_id]' -- Replace with the actual flow ID
    GROUP BY   
        f.machineid, fm.name, mg.managementtype, mg.maxmanagedmachinecount, fm.lastheartbeatdate, fm.sessioncapacity  
    ORDER BY   
        mean_runtime DESC;  

与机器和许可容量相关的查询

此查询可识别特定桌面流的计算机和许可相关容量问题，以帮助优化资源分配和解决性能限制。

    SELECT   
        f.machineid,  
        fm.name AS machine_name,  
        CASE   
            WHEN mg.managementtype = 0 THEN 'Regular Machine (Group)'  
            ELSE 'Hosted Machine (Group)'  
        END AS managementtype,  
        mg.maxmanagedmachinecount AS maxmanagedmachinecount,  
        fm.lastheartbeatdate AS last_heartbeat_date,  
        fm.sessioncapacity AS 'Max Parallel Sessions',  
        fm.overcapacitysince,  
        CASE   
            WHEN fm.overcapacitysince IS NOT NULL THEN 'Over Capacity'  
            ELSE 'Within Capacity'  
        END AS capacity_status  
    FROM   
        flowsession f  
    JOIN   
        flowmachinegroup mg ON f.machinegroupid = mg.flowmachinegroupid  
    JOIN   
        flowmachine fm ON f.machinegroupid = fm.flowmachinegroupid
    WHERE   
        f.regardingobjectid = '[specific_flow_id]' -- Replace with the actual flow ID
    GROUP BY   
        f.machineid, fm.name, mg.managementtype, mg.maxmanagedmachinecount, fm.lastheartbeatdate, fm.sessioncapacity, fm.overcapacitysince  
    ORDER BY   
        capacity_status DESC, fm.lastheartbeatdate DESC;  

桌面流的治理相关查询示例

查找连接中包含纯文本密码的脚本

此查询查找使用（OLEDB）数据库连接字符串的所有桌面流，这些连接字符串配置为使用纯文本密码。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6  
        AND w.definition IS NOT NULL  
        AND (LOWER(w.definition) LIKE '%;password=%');

潜在的 SQL 注射风险

该查询通过搜索流定义中 database.executesqlstatement.execute 的使用情况，检测包含可能易受 SQL 注入影响的脚本的桌面流。 假设脚本没有直接在执行 SQL 语句操作中编写 SQL 代码，而是使用了在运行期间提供给脚本的 Power Automate 桌面输入变量（例如 %LetsDeleteAllGeneralLedgerEntriesFromDB%）。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6  
        AND w.definition IS NOT NULL  
        AND LOWER(w.definition) LIKE '%database.executesqlstatement.execute%';
    

高级 API 请求使用情况

此查询可检索使用高级 API 请求方法（如 curl、Invoke-RestMethod 和其他 requests）的桌面流，以确定与外部网络服务或服务的连接。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6  
        AND w.definition IS NOT NULL  
        AND (
            LOWER(w.definition) LIKE '%curl%' OR 
            LOWER(w.definition) LIKE '%invoke-restmethod%' OR 
            LOWER(w.definition) LIKE '%invoke-webrequest%' OR 
            LOWER(w.definition) LIKE '%httpclient%' OR 
            LOWER(w.definition) LIKE '%requests.get%' OR 
            LOWER(w.definition) LIKE '%requests.post%' OR 
            LOWER(w.definition) LIKE '%fetch%' OR 
            LOWER(w.definition) LIKE '%axios%' OR 
            LOWER(w.definition) LIKE '%.ajax%'
        );
    

Web 终结点和 URL 快捷方式用法

此查询检测包含引用 URL 缩短器的脚本的桌面流，以评估受限 URL 使用的潜在风险。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6  
        AND w.definition IS NOT NULL  
        AND (
            LOWER(w.definition) LIKE '%bit.ly%' OR 
            LOWER(w.definition) LIKE '%linkedin.com%' OR 
            LOWER(w.definition) LIKE '%aka.ms%' OR 
            LOWER(w.definition) LIKE '%tinyurl.com%' OR 
            LOWER(w.definition) LIKE '%goo.gl%' OR 
            LOWER(w.definition) LIKE '%t.co%' OR 
            LOWER(w.definition) LIKE '%fb.me%' OR 
            LOWER(w.definition) LIKE '%is.gd%' OR 
            LOWER(w.definition) LIKE '%buff.ly%'
        );
    

脚本中缺少错误处理

此查询可检索缺乏任何错误处理机制（如 block error 或 on error 上的错误处理机制）的桌面流，以确保脚本执行的稳健性和可靠性。

    SELECT   
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM   
        workflow w  
    JOIN   
        systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.category = 6  
        AND w.definition IS NOT NULL  
        AND NOT (LOWER(w.definition) LIKE '%on block error%' OR LOWER(w.definition) LIKE '%on error%');
    

V2 操作日志的治理相关查询示例

备注

在继续本节内容之前，请确保在您的环境中已启用桌面流日志 V2 并有现有的桌面流运行。

使用受限 URL 访问的桌面流运行

此查询查找过去三周内特定桌面流中的 Web 服务调用（调用 Web 服务操作）。 此结果可用于识别和分析潜在的可疑端点或受限的 API 调用。

    SELECT   
        JSON_VALUE(f.data, '$.name') AS ActionName,  
        f.data AS 'Action log',  
        f.parentobjectid AS 'Parent object id',  
        f.createdon AS 'Log created on',
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM  
        [flowlog] f  
    JOIN  flowsession fs ON f.parentobjectid = fs.flowsessionid         
    JOIN  workflow w ON fs.regardingobjectid = w.workflowid  
    JOIN  systemuser s ON w.ownerid = s.systemuserid  
    WHERE   
        w.workflowid = '[specific_flow_id]' -- Replace with the actual flow ID
        AND f.createdon >= DATEADD(day, -21, GETDATE())
        AND JSON_VALUE(f.data, '$.name') = 'Invoke web service'  
        AND (  
            f.data LIKE '%contoso-default.crm.dynamics.com/api%'  
            OR f.data LIKE '%api.second-restricted-url.net%'  
            OR f.data LIKE '%api.third-restricted-url.de%'  
            OR f.data LIKE '%api.phishing-example.com%'  
        );

带有加密代码的桌面流运行

此查询会扫描过去七天内包含加密代码的 PowerShell 脚本操作的桌面流运行。

    -- Queries actions logs named 'Run PowerShell script' that contain code that that uses cryptographic libraries 
    -- and terms such as "AES", "RSA", "encryption", or "decryption," which may indicate risky operations
    SELECT top(1)
        JSON_VALUE(data, '$.name') AS ActionName,
        JSON_VALUE(data, '$.inputs') AS Inputs,
        JSON_VALUE(data, '$.outputs') AS Outputs
    FROM 
        [flowlog]
    WHERE
        JSON_VALUE(data, '$.name') = 'Run PowerShell script'
        AND createdon >= DATEADD(day, -7, GETDATE())
        AND (
            JSON_VALUE(data, '$.inputs') LIKE '%AES%'
            OR JSON_VALUE(data, '$.inputs') LIKE '%RSA%'
            OR JSON_VALUE(data, '$.inputs') LIKE '%encryption%'
            OR JSON_VALUE(data, '$.inputs') LIKE '%decryption%'
        )
    ORDER BY
        ActionName
    

使用专业代码的桌面流运行

此查询更高级一些。 该查询可识别并统计过去七天内包含专业代码部分（如 VBScript、PowerShell、JavaScript、.NET 或 Python）的不同桌面流运行（流会话），并按桌面流对结果进行分组。

WITH ProCodingSessions AS (  
    SELECT   
        fs.flowsessionid,  
        f.data AS 'Action log',  
        f.parentobjectid AS 'Parent object id',  
        f.createdon AS 'Log created on',  
        w.name AS 'Desktop flow',  
        w.workflowid AS 'Desktop flow Id',  
        w.createdon AS 'Created on',  
        w.modifiedon AS 'Last modified on',  
        w.definition AS 'Script',  
        w.ownerid AS 'Owner Id',  
        s.fullname AS 'Owner name',  
        s.internalemailaddress AS 'Owner email'  
    FROM [flowlog] f  
    JOIN flowsession fs ON f.parentobjectid = fs.flowsessionid  
    JOIN workflow w ON fs.regardingobjectid = w.workflowid  
    JOIN systemuser s ON w.ownerid = s.systemuserid  
    WHERE f.createdon >= DATEADD(day, -7, GETDATE())  
    AND (  
        LOWER(w.definition) LIKE '%runvbscript%' OR  
        LOWER(w.definition) LIKE '%runpowershellscript%' OR  
        LOWER(w.definition) LIKE '%runjavascript%' OR  
        LOWER(w.definition) LIKE '%rundotnetscript%' OR  
        LOWER(w.definition) LIKE '%runpythonscript%'  
    )  
),  
FlowCounts AS (  
    SELECT  
        p.[Desktop flow],  
        p.[Desktop flow Id],  
        p.[Created on],  
        p.[Last modified on],  
        p.[Script],  
        p.[Owner Id],  
        p.[Owner name],  
        p.[Owner email],  
        COUNT(DISTINCT p.flowsessionid) AS ProCodingSessionCount  
    FROM ProCodingSessions p  
    GROUP BY  
        p.[Desktop flow],  
        p.[Desktop flow Id],  
        p.[Created on],  
        p.[Last modified on],  
        p.[Script],  
        p.[Owner Id],  
        p.[Owner name],  
        p.[Owner email]  
)  
SELECT  
    f.[Desktop flow],  
    f.[Desktop flow Id],  
    f.[Created on],  
    f.[Last modified on],  
    f.[Script],  
    f.[Owner Id],  
    f.[Owner name],  
    f.[Owner email],  
    f.ProCodingSessionCount AS 'Runs with pro-code' 
FROM FlowCounts f  
ORDER BY f.ProCodingSessionCount DESC;  

V2 操作日志的错误和性能相关查询

十大失败的桌面流操作

此查询返回前 10 个失败操作（按过去 7 天中的错误数）。

    SELECT TOP(10)   
        JSON_VALUE(data, '$.name') AS ActionName,  
        SUM(CASE WHEN JSON_VALUE(data, '$.status') = 'Failed' THEN 1 ELSE 0 END) AS ErrorCount  
    FROM [flowlog]  
    WHERE createdon >= DATEADD(day, -7, GETDATE())  
    GROUP BY JSON_VALUE(data, '$.name')  
    HAVING SUM(CASE WHEN JSON_VALUE(data, '$.status') = 'Failed' THEN 1 ELSE 0 END) > 0  
    ORDER BY ErrorCount DESC;  

前 10 个带计数的错误代码

    SELECT TOP(10)  
        JSON_VALUE(data, '$.errorCode') AS ErrorCode,  
        COUNT(*) AS OccurrenceCount  
    FROM [flowlog]  
    WHERE createdon >= DATEADD(day, -7, GETDATE())  
      AND JSON_VALUE(data, '$.status') = 'Failed'  
    GROUP BY JSON_VALUE(data, '$.errorCode')  
    ORDER BY OccurrenceCount DESC;