FortiSIEM
FortiSIEM provides Security Information and Event Management (SIEM) and User and Entity Behavior Analytics (UEBA)
alebay
Staff
Staff
Article Id 198481
Description
This article describes how to troubleshoot the ProfileDB error "sql prepare error 1: no such column".
Scope

In FortiSIEM logs, the error “PROFILE_DB: sql prepare error 1: no such column” like the one below might be triggered when a rule has been incorrectly modified:

[phLogDetail]=PROFILE_DB: sql prepare error 1: no such column: srcIpPort: select avgTotFlows from profile_104 where custId=1 and srcIpPort=25587 and destIpPort=443 and ipProto=6 and profDateType=1 and hourOfDay=11 and numPoints>=2 


Solution
Here is a step by step guide: 

1. Check the "from" output from the phoenix log. Taking from the above log example we can see the error has originated from “profile_104"

2. Find the Rule associated with number  104 which is causing this issue.

3. Login to super as root 

4. Go to the following directory 
cd /opt/phoenix/data-definition/rule 

5. Enter the following to find the rule. 
[root@VA81 rules]# grep \:104 *.xml 
STAT_RULES.xml: STAT_STDDEV(SUM(totFlows):104) > 0 AND 
STAT_RULES.xml: STAT_STDDEV(SUM(totBytes):104) > 0 

6. In STAT_RULES search for 104 and find the rule. 
<Name>Sudden Increase in Firewall Permitted Inbound Traffic To A Specific TCP/UDP port</Name>

7. Search for this rule in GUI and also any clones from this rule and ensure that the conditions are same as it was in default. From past experience this is usual an additional condition in the groupby which causes this error.

8.Warning: adding additional group by in that system rule, this could trigger lot of errors in phoenix logs. 


Contributors