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

[JudiFulo]

You need to add GROUP BY every field from SELECT which doesn't part of an aggregate function like count(). In your example the GROUP BY of summary query part is missing srcip, unique_dstports and event_count as well

[farhanahmed]

Hi,

It seems that you are trying to create a report for policy hit count including the dstport.
You can refer to the article below and may be modify it to add dstport as well.
https://community.fortinet.com/t5/FortiAnalyzer/Technical-Tip-Policy-Hit-Count-Report/ta-p/316140

SELECT
policyid,
dstport,
count (*) AS policyhit
FROM
$log
WHERE
$filter
GROUP BY
policyid, dstport
ORDER BY
policyhit desc

But for the error you are receiving, @JudiFulo has explained how to fix it.