Partager via

Créer des requêtes liées à l’automatisation avec Fabric

Note

Exclusion : les scénarios, les exemples de requête et les données utilisés dans ce didacticiel sont fictifs, peuvent contenir des erreurs et des inefficiences et sont destinés exclusivement à des fins de démonstration.

Le tableau suivant répertorie les tables liées à l’automatisation fréquemment utilisées pour la création de rapports et l’observabilité.

Display name Object name Finalité
Journal de flux flowlog Contient une grande variété de journaux, tels que les journaux personnalisés, les journaux d’action de flux de bureau V2, les journaux de file d’attente d’exécution de la machine, les demandes/réponses d’auto-réparation sans surveillance, les journaux de traitement de la file d’attente de travail, etc. Les données sont stockées dans une table élastique Dataverse et, selon le type de journal, peuvent être configurées avec leur propre paramètre de durée de vie (TTL) dans la table Organisation (FlowLogsTtlInMinutes et DesktopFlowQueueLogsTtlInMinutes), qui définit quand les enregistrements doivent être automatiquement supprimés de la table.
Machine de flux flowmachine Contient des informations relatives à la machine et à la machine hébergée.
Groupe de machines de flux flowmachinegroup Contient des informations relatives au groupe de machines et au groupe de machines hébergées.
Flux d’exécution flowrun Contient des données relatives à l’exécution de flux de cloud, telles que le début, la fin, la durée, le contexte du flux parent, etc.
Session de flux flowsession Contient des données relatives à l’exécution de flux de bureau, telles que le début, les durées, le statut, la machine, le compte de robot, le contexte du flux parent, etc.
Process workflow Contient des flux de bureau et des flux de cloud basés sur des solutions (ainsi que d’autres types de workflow).
User systemuser Représente l’utilisateur Dataverse.
File d’attente de travail workqueue Représente une instance de l’exécution d’un workflow.
Élément de la file d’attente de travail workqueueitem Contient des informations sur chaque exécution d’un workflow.

Diagramme simplifié des relations de table

L’image montre uniquement les relations de table pertinentes pour l’automatisation.

Capture d’écran d’un schéma des relations d’entité montrant les relations de table liées à l’automatisation.

Créer votre première requête dans Fabric

Suivez ces étapes pour créer un exemple de requête SQL sur le point de terminaison analytique SQL dans Fabric pour la lakehouse contoso_westus_accounts_payable.

  1. Ouvrez votre navigateur web, accédez au portail Microsoft Fabric (https://powerbi.com) et connectez-vous avec vos informations d’identification.

  2. Sélectionnez l’espace de travail où se trouve votre lakehouse, puis sélectionnez le point de terminaison analytique SQL souhaité (un sous-nœud de votre lakehouse).

  3. Dans le point de terminaison analytique SQL, sélectionnez Nouvelle requête SQL pour ouvrir l’éditeur de requête SQL.

  4. Dans l’éditeur de requête SQL, entrez votre requête SQL et sélectionnez Exécuter. L’exemple de requête suivant récupère toutes les exécutions de flux de bureau (sessions de flux) associées à un flux de bureau spécifique et à un ID de machine qui a échoué au cours des sept derniers jours.

       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. Voici une liste des raisons de statut disponibles (statuscode) pour la table Flow Sessions (exécutions de flux de bureau).

    Raison du statut active
    Suspendu 1
    Exécution en cours 2
    En attente 3
    Succès 4
    Ignorée 5
    Suspendu 6
    Annulé 7
    Échoué 8
    Erreur 9
    TimedOut 10
    Abandonnée 11
    Ignoré 12

  6. Examinez les résultats de la requête pour vous assurer qu’ils répondent à vos besoins.

    Capture d’écran d’une requête SQL en cours d’exécution dans un outil de gestion de base de données.

  7. (Facultatif) Ouvrez une requête en direct avec les résultats dans Excel en mettant en surbrillance la requête SQL et en sélectionnant Ouvrir dans Excel dans la section de sortie de la requête. Cela génère et télécharge un fichier Excel avec une requête en direct sur le point de terminaison SQL Analytics pour une analyse plus approfondie.

    Capture d’écran d’une requête SQL en cours d’exécution dans un volet de requête de base de données dans Fabric.

  8. (Facultatif) Pour stocker la requête SQL en vue d’une utilisation ultérieure, sélectionnez Enregistrer la requête.

Requêtes de flux de base

Récupérer les flux de cloud avec leurs informations de propriétaire

Cette requête renvoie tous les flux de cloud avec leurs informations de propriétaire.

Nonte

Seuls les flux de cloud faisant partie d’une solution Dataverse sont disponibles dans 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)

Récupérer les flux de bureau avec leurs informations de propriétaire

Cette requête renvoie tous les flux de bureau avec leurs informations de propriétaire.

    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)

Cette requête récupère l’écart minimal, moyen, maximal et standard des temps d’exécution pour les exécutions de flux de bureau (sessions de flux) d’un flux de bureau spécifié, les temps d'exécution étant convertis de millisecondes arrondis à la seconde entière la plus proche. La requête regroupe les résultats par ID de machine et inclut des détails tels que les noms des machines, les types de gestion, le nombre maximal de machines hébergées, la capacité de la session et la date de la dernière activité des tables Groupe de machines et Machines.

    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;

Capture d’écran d’une requête SQL en cours d’exécution dans Fabric avec une fenêtre de résultats.

Cette requête identifie les problèmes de capacité liés à la machine et aux licences pour un flux de bureau spécifique afin d’optimiser l’allocation des ressources et de résoudre les contraintes de performances.

    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;

Rechercher des scripts qui incluent des mots de passe en texte brut dans les connexions

Cette requête recherche tous les flux de bureau qui utilisent des chaînes de connexion de base de données (OLEDB) configurées pour utiliser un mot de passe en texte brut.

    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=%');

Risque potentiel d’injection SQL

La requête détecte les flux de bureau contenant des scripts potentiellement vulnérables pour l’injection SQL en recherchant l’utilisation de database.executesqlstatement.execute dans les définitions de flux. Prenons un scénario où, au lieu d’écrire directement le code SQL dans l’action Exécuter l’instruction SQL, le script utilise une variable d'entrée Power Automate bureau (par exemple, %LetsDeleteAllGeneralLedgerEntriesFromDB%) fournie au script pendant l’exécution.

Capture d’écran de la boîte de dialogue de configuration « Exécuter l’instruction SQL » dans Power Automate bureau. 

    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%';

Utilisation de requête d’API avancée

Cette requête récupère les flux de bureau qui utilisent des méthodes de requête d’API avancées, telles que curl, Invoke-RestMethod et d’autres requests pour identifier la connectivité à des services ou des services web externes.

    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%'
        );

Utilisation des points de terminaison Web et des raccourcis d’URL

Cette requête détecte les flux de bureau contenant des scripts qui référencent des raccourcisseurs d’URL pour évaluer les risques potentiels d’utilisation restreinte des 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%'
        );

Absence de gestion des erreurs dans les scripts

Cette requête recherche des flux de bureau qui ne disposent pas de mécanismes de gestion des erreurs, par exemple block error ou on error, afin de garantir la robustesse et la fiabilité de l’exécution des scripts.

    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%');

Nonte

Avant de continuer avec cette section, assurez-vous que les Journaux de flux de bureau V2 ont été activés dans votre environnement et que vous avez des exécutions de flux de bureau existantes.

Exécutions de flux de bureau avec un accès restreint aux URL

Cette requête recherche les appels de service web (action Appeler le service web) dans un flux de bureau spécifique au cours des trois dernières semaines. Ce résultat est utile pour identifier et analyser les points de terminaison potentiellement suspects ou les appels d’API restreints.

    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%'  
        );

Exécutions de flux de bureau avec code cryptographique

Cette requête analyse les exécutions de flux de bureau à la recherche d’actions de script PowerShell qui comprenaient du code cryptographique au cours des sept derniers jours.

    -- 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

Exécutions de flux de bureau avec utilisation de pro-code

Cette requête est un peu plus avancée. Il identifie et comptabilise les exécutions de flux de bureau distinctes (sessions de flux) avec des parties de codage professionnel (telles que VBScript, PowerShell, JavaScript, .NET ou Python) au cours des sept derniers jours, et regroupe les résultats par flux de bureau.

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;

Top 10 des actions de flux de bureau défaillantes

Cette requête renvoie les 10 actions ayant échoué le plus en fonction du nombre d’erreurs au cours des sept derniers jours.

    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 principaux codes d’erreur avec le nombre

    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;