OSSEC v2.9.2 and Analogi - Database have incorrect schema

[Christopher Cutajar]

Hi,

I've just installed OSSEC Hids v2.9.2 together with Analogi. Before installation I've compiled the source using  make TARGET=server DATABASE=mysql. After the whole installation and configuration of both OSSEC and Analogi, Analogi is prompting that I do not have a correct schema.

One of the github commits done about 1 year ago by  aquerubin was Revert 'NOT NULL' for src_ip and dst_ip. This was mistakenly added in. I've done the required changes and updated the schema but with no success. 

Anyone has been experiencing the same issue? Any suggestions are welcomed.

Thanks and Regards,

Chris

[ngr...@gmail.com]

I've noticed a similar issue. I recently updated from an OSSEC 2.8.x install to a 2.9.x install. With my 2.8.x install, I had been using Analogi for quite some time.

I encountered some issues enabling MySQL support during the update as this feature is not documented well and all the available documentation only applies to 2.8.x installs (but that's a separate problem). 

After the update, some of Analogi's functionality was suddenly broken. In particular, attempting to use the "Detail" dashboard no longer works; no events the occurred after the update will be returned in any searches. The "Index" and "Mass Monitoring" dashboards seem to work, but only partially (it's tough to verify, but in my install it looks like events from some sources don't display, or perhaps only sporadically). I'm not getting the Analogi error you note, but it doesn't appear to be working properly either.

I assume this is because the database schema changed just enough to break Analogi. I recall needing to manually modify the schema of the database I already had; it's possible I didn't do it right but I don't remember what I did anymore. I've also noticed that some parts of the database don't appear to be getting populated (the 'agents' table for example), but OSSEC otherwise works properly, and the OSSEC WUI works. I get the impression that there's not much drive to improve the external database support for OSSEC, so there's not much documentation or support for it. And since development on Analogi appears to have stopped quite some time ago...it may be broken for good.

You might try setting up an older version of OSSEC first, perhaps the last 2.8.x version, since that worked with Analogi without issues for me. You then might be able to do the upgrade to 2.9.x (if you so desired) and try to see where things break down. I'd certainly be interested to know if you're able to figure it out.

[Nate]

I just wanted to reply to this thread since it was related to the issues I ran into upgrading from OSSEC 2.4 to 3.2 (yep i know) - I did a search for all files in analogi with SELECT then filtered by "data." and replaced "data." with "alert." (including that period).

From the analogi root: 

1. File ./php/index_graph.php:

        if(mysql_query("SELECT 1 from agent", $db_ossec)

        && mysql_query("SELECT 1 from alert", $db_ossec)

        && mysql_query("SELECT 1 from category", $db_ossec)

-->   /*&& mysql_query("SELECT 1 from data", $db_ossec) */

        && mysql_query("SELECT 1 from location", $db_ossec)

        && mysql_query("SELECT 1 from server", $db_ossec)

        && mysql_query("SELECT 1 from signature", $db_ossec)

        && mysql_query("SELECT 1 from signature_category_mapping", $db_ossec)){

                $databaseschema="yes";

        }else{

                //$databaseschema="yes";

                $problem=1;

                $databaseschema="no!<br/>";

                $databaseschema.="&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fix - Import the MySQL schema that comes with OSSEC";

        }

        //if(checktable('alert') && checktable('data') && checktable('location') && checktable('signature')){

-->   if(checktable('alert') && checktable('location') && checktable('signature')){

                $anydata="yes";

        }else{

                $problem=1;

                $anydata="no!<br/>";

                $anydata.="&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fix - Ensure agents are logging data.";

        }

2. File ./detail.php in VI: (find and replace 'data.' with 'alert.')

:%s/data\./alert\./g.

I did however completely replace my DB since the schema wasn't updating properly from the installer of 3.2 (bug?)

3. From file: databasetest.php: (comment this all out)

/*$query="SELECT count(id) as res_count

/        FROM data";

if($result=mysql_query($query, $db_ossec)){

        $row = @mysql_fetch_assoc($result);

        if(!$row['res_count']>0){

                echo "

                alert(\"Connected to database ok, but no data found. Ensure OSSEC is logging to your database.\");";

        }

}else{

                echo "

                alert(\"Problems checking database for information\");";

}*/

4. From file: management.php:

Comment out two times:

                //      $query="OPTIMIZE TABLE data;";

                //      mysql_query($query, $db_ossec);

                //      $query="OPTIMIZE TABLE data;";

                //      mysql_query($query, $db_ossec);

Update the delete SQL syntax in Two locations as well:

               /*$querydelete="DELETE alert, data FROM alert

                        LEFT JOIN data ON alert.id=data.id

                        LEFT JOIN signature ON alert.rule_id=signature.rule_id

                        LEFT JOIN location ON alert.location_id=location.id

                        WHERE ".$where;*/

                $querydelete="DELETE alert FROM alert

                        LEFT JOIN signature ON alert.rule_id=signature.rule_id

                        LEFT JOIN location ON alert.location_id=location.id

                        WHERE ".$where;