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.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
Have you check this doc: https://docs.fortinet.com/document/fortianalyzer/7.6.0/administration-guide/495456/creating-datasets
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
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1696 | |
1091 | |
752 | |
446 | |
228 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.