Need help creating a custom dataset for Fortianalyzer

[kitkat09811]

FYI, im not a coder of sql expert by any means...

here is my ChatGPT generated Fortianalyzer SQL query:

-- Main query: Count occurrences of srcip, dstport, and policyid
SELECT
    srcip,
    dstport,
    policyid,
    COUNT(*) AS event_count
FROM $log  -- Replace with the appropriate log source
WHERE srcip IS NOT NULL AND dstport IS NOT NULL AND policyid IS NOT NULL
GROUP BY srcip, dstport, policyid
ORDER BY policyid, srcip;  -- Order by policyid and srcip

-- Summary query: Unique dstports per policyid
SELECT 
    CONCAT('PolicyID ', CAST(policyid AS STRING), ' unique dstports') AS srcip,
    STRING_AGG(DISTINCT dstport, ', ') AS unique_dstports,
    policyid,
    NULL AS event_count
FROM $log
WHERE srcip IS NOT NULL AND dstport IS NOT NULL AND policyid IS NOT NULL
GROUP BY policyid
ORDER BY policyid;

Wen i paste this code in the SQL query dataset window, I get this error and don't know how to fix this.

Validate Result
ERROR: 'group by' or 'order by' clause is expected in hcache.

Ultimately, i am trying to do a report that will output a list of policyID's and the unique destination ports being used on each policy in order to clamp down on the service ports required for each policy.

[spoojary]

Have you check this doc: https://docs.fortinet.com/document/fortianalyzer/7.6.0/administration-guide/495456/creating-datasets

[kitkat09811]

i dont need a link to document which I already read. As i stated, im trying to get the SQL to give me the output as described