/**
* sim_db_insert_host_os:
*
* Insert 'host os' event in @databse
*/
void
sim_db_insert_host_os (SimDatabase *database,
SimInet *inet,
gchar *date,
SimInet *sensor,
gchar *interface,
gchar *os,
SimUuid *context_id)
{
gchar *query = NULL;
gchar *os_escaped;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_INET (inet));
g_return_if_fail (date);
g_return_if_fail (os);
os_escaped = g_strescape (os, NULL);
query = g_strdup_printf ("INSERT INTO host_os (id, ctx, sensor, date, os, interface) "
"SELECT id, %s, %s, '%s', '%s', '%s' FROM host "
"WHERE ip = %s and ctx = %s LIMIT 1",
sim_uuid_get_db_string (context_id),
sensor ? sim_inet_get_db_string (sensor) : "NULL",
date,
os_escaped,
interface,
sim_inet_get_db_string (inet),
sim_uuid_get_db_string (context_id));
sim_db_execute_query (database, query);
g_free (os_escaped);
}
/**
* sim_db_insert_sensor:
* @database: a #SimDatabase
* @sensor: a #SimSensor
*
* Inserts a new #SimSensor in our database.
*/
void
sim_db_insert_sensor (SimDatabase * database,
SimSensor * sensor)
{
gchar * query;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_SENSOR (sensor));
query = g_strdup_printf ("INSERT INTO sensor (id, name, ip, port, connect, tzone) "
"VALUES (%s, '%s', %s, %d, %d, '%4.2f') "
"ON DUPLICATE KEY UPDATE "
"name = '%s', ip = %s, port = %d, connect = %d, tzone = '%4.2f'",
sim_uuid_get_db_string (sim_sensor_get_id (sensor)),
sim_sensor_get_name (sensor),
sim_inet_get_db_string (sim_sensor_get_ia (sensor)),
sim_sensor_get_port (sensor),
sim_sensor_is_connect (sensor),
sim_sensor_get_tzone (sensor),
sim_sensor_get_name (sensor),
sim_inet_get_db_string (sim_sensor_get_ia (sensor)),
sim_sensor_get_port (sensor),
sim_sensor_is_connect (sensor),
sim_sensor_get_tzone (sensor));
sim_db_execute_query (database, query);
g_free (query);
return;
}
/**
* sim_db_insert_host_service:
*
* Insert 'host service' in @databse
*/
void
sim_db_insert_host_service (SimDatabase *database,
SimInet *inet,
gchar *date,
gint port,
gint protocol,
SimInet *sensor,
gchar *interface,
gchar *service,
gchar *application,
SimUuid *context_id)
{
gchar *query;
gint plugin_id;
struct servent *temp_serv = NULL;
struct protoent *temp_proto = NULL;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_INET (inet));
g_return_if_fail (date);
g_return_if_fail (port >= 0); /* Needed for ints */
g_return_if_fail (protocol >= 0);
g_return_if_fail (sensor);
g_return_if_fail (service);
g_return_if_fail (application);
temp_proto = getprotobynumber (protocol);
if (temp_proto->p_name == NULL)
return; /* Since we don't know the proto we wont insert a service without a protocol */
temp_serv = getservbyport (port, temp_proto->p_name);
query = g_strdup_printf ("INSERT INTO host_services "
"(id, date, port, protocol, service, service_type, version, origin, sensor, interface, ctx) "
"SELECT id, '%s', %u, %u, '%s', '%s', '%s', 0, %s, '%s', %s "
"FROM host WHERE ip = %s and ctx = %s LIMIT 1",
date,
port,
protocol,
(temp_serv != NULL) ? temp_serv->s_name : "unknown",
service,
application,
sim_inet_get_db_string (sensor),
interface,
sim_uuid_get_db_string (context_id),
sim_inet_get_db_string (inet),
sim_uuid_get_db_string (context_id));
sim_db_execute_query (database, query);
g_free (query);
plugin_id = SIM_PLUGIN_SERVICE;
sim_db_insert_host_plugin_sid (database, inet, plugin_id, port, context_id);
}
gchar*
sim_event_get_alarm_insert_clause (SimDatabase *db_ossim,
SimEvent *event,
gboolean removable)
{
gchar time[TIMEBUF_SIZE];
gchar *timestamp=time;
GString *query;
GdaConnection *conn;
gchar *e_alarm_stats = NULL;
g_return_val_if_fail (SIM_IS_EVENT (event), NULL);
conn = sim_database_get_conn (db_ossim);
if(event->time_str)
timestamp=event->time_str;
else
strftime (timestamp, TIMEBUF_SIZE, "%F %T", gmtime ((time_t *) &event->time));
guint efr = event->priority * event->reliability * 2; //this is used for compliance. The "*2" is to take a percentage
if (event->alarm_stats)
e_alarm_stats = sim_str_escape (event->alarm_stats, conn, 0);
ossim_debug ( "%s: risk_c:%f, risk_a:%f", __func__, event->risk_c, event->risk_a);
query = g_string_new ("REPLACE INTO alarm "
"(event_id, backlog_id, corr_engine_ctx, timestamp, plugin_id, plugin_sid, "
"protocol, src_ip, dst_ip, src_port, dst_port, "
"risk, efr, similar, removable, stats) VALUES (");
g_string_append_printf (query, "%s", sim_uuid_get_db_string (event->id));
g_string_append_printf (query, ",%s", sim_uuid_get_db_string (event->backlog_id));
g_string_append_printf (query, ",%s", sim_uuid_get_db_string (sim_engine_get_id (event->engine)));
g_string_append_printf (query, ",'%s'", timestamp);
g_string_append_printf (query, ",%d", event->plugin_id);
g_string_append_printf (query, ",%d", event->plugin_sid);
g_string_append_printf (query, ",%d", event->protocol);
g_string_append_printf (query, ",%s", (event->src_ia) ? sim_inet_get_db_string (event->src_ia) : "NULL");
g_string_append_printf (query, ",%s", (event->dst_ia) ? sim_inet_get_db_string (event->dst_ia) : "NULL");
g_string_append_printf (query, ",%d", event->src_port);
g_string_append_printf (query, ",%d", event->dst_port);
g_string_append_printf (query, ",%d", ((gint)event->risk_a > (gint)event->risk_c) ? (gint)event->risk_a : (gint)event->risk_c);
g_string_append_printf (query, ",%u", efr);
g_string_append_printf (query, ",IF('%s'<>''", (event->groupalarmsha1 != NULL ? event->groupalarmsha1 : ""));
g_string_append_printf (query, ",'%s'", (event->groupalarmsha1 != NULL ? event->groupalarmsha1 : ""));
g_string_append_printf (query, ",SHA1('%s'))", sim_uuid_get_db_string (event->id));
g_string_append_printf (query, ",%d", removable);
g_string_append_printf (query, ",'%s')", e_alarm_stats ? e_alarm_stats : "");
g_free (e_alarm_stats);
return g_string_free (query, FALSE);
}
/**
* sim_db_insert_host_mac:
*
* Insert 'host mac' event in @databse
*/
void
sim_db_insert_host_mac (SimDatabase *database,
SimInet *inet,
gchar *date,
gchar *mac,
gchar *vendor,
gchar *interface,
SimInet *sensor,
SimUuid *context_id)
{
gchar *query;
gchar *vendor_esc;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_INET (inet));
g_return_if_fail (date);
g_return_if_fail (mac);
g_return_if_fail (interface);
g_return_if_fail (sensor);
// we want to insert only the hosts defined in Policy->hosts or inside a network from policy->networks
// if((sim_container_get_host_by_ia(container,ia) == NULL) && (sim_container_get_nets_has_ia(container,ia) == NULL))
// return;
vendor_esc = g_strescape (vendor, NULL);
query = g_strdup_printf ("INSERT INTO host_mac (id, ctx, sensor, date, mac, vendor, interface) "
"SELECT id, %s, %s, '%s', '%s', '%s', '%s' FROM host WHERE ip = %s and ctx = %s LIMIT 1",
sim_uuid_get_db_string (context_id),
sensor ? sim_inet_get_db_string (sensor) : "NULL",
date,
mac,
(vendor_esc) ? vendor_esc : "",
interface,
sim_inet_get_db_string (inet),
sim_uuid_get_db_string (context_id));
g_free (vendor_esc);
ossim_debug ("%s: query: %s", __func__, query);
sim_db_execute_query (database, query);
g_free (query);
}
/**
* sim_db_update_sensor_by_ia:
* @database: a #SimDatabase
* @sensor: a #SimSensor
*
* Updates a #SimSensor using its network address.
*/
void
sim_db_update_sensor_by_ia (SimDatabase * database,
SimSensor * sensor)
{
gchar * query;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_SENSOR (sensor));
query = g_strdup_printf ("UPDATE sensor SET id=%s, name='%s', port=%d, connect=%d "
"WHERE ip=%s",
sim_uuid_get_db_string (sim_sensor_get_id (sensor)),
sim_sensor_get_name (sensor),
sim_sensor_get_port (sensor),
sim_sensor_is_connect (sensor),
sim_inet_get_db_string (sim_sensor_get_ia (sensor)));
sim_db_execute_query (database, query);
g_free (query);
return;
}
/**
* sim_db_insert_host_plugin_sid:
*
* Insert host plugin sid in @database
*/
void
sim_db_insert_host_plugin_sid (SimDatabase *database,
SimInet *inet,
gint plugin_id,
gint plugin_sid,
SimUuid *context_id)
{
gchar *query;
g_return_if_fail (SIM_IS_DATABASE (database));
g_return_if_fail (SIM_IS_INET (inet));
// this is a plugin_sid which comes from an special event, (the plugin_id)
query = g_strdup_printf ("REPLACE INTO host_plugin_sid (host_ip, plugin_id, plugin_sid, ctx) "
"VALUES (%s, %d, %d, %s)",
sim_inet_get_db_string (inet),
plugin_id,
plugin_sid,
sim_uuid_get_db_string (context_id));
sim_db_execute_query (database, query);
g_free (query);
}
void
sim_db_update_host_properties (SimDatabase *database,
SimUuid *context_id,
SimUuid *sensor_id,
SimIdmEntry *entry,
SimIdmEntryChanges *changes,
gboolean is_ip_update)
{
gchar *query;
gchar *values;
gchar *property, *e_property;
const gchar *host_id_str;
const gchar *ip_str;
host_id_str = sim_uuid_get_db_string (sim_idm_entry_get_host_id (entry));
ip_str = sim_inet_get_db_string (sim_idm_entry_get_ip (entry));
// 'host' and 'host_sensor_reference' table
if (changes->host_id)
{
query = g_strdup_printf ("INSERT IGNORE INTO host (id, ctx, asset, threshold_c, threshold_a) VALUES (%s, %s, %d, %d, %d)", host_id_str, sim_uuid_get_db_string (context_id), 2, 30, 30);
sim_database_execute_no_query (database, query);
g_free (query);
query = g_strdup_printf ("INSERT IGNORE INTO host_sensor_reference (host_id, sensor_id) VALUES (%s, %s)", host_id_str, sim_uuid_get_db_string (sensor_id));
sim_database_execute_no_query (database, query);
g_free (query);
}
if (changes->hostname)
{
query = g_strdup_printf ("UPDATE host SET hostname = '%s' WHERE id = %s AND ctx = %s", sim_idm_entry_get_hostname (entry), host_id_str, sim_uuid_get_db_string (context_id));
sim_database_execute_no_query (database, query);
g_free (query);
}
if (changes->fqdns)
{
query = g_strdup_printf ("UPDATE host SET fqdns = '%s' WHERE id = %s AND ctx = %s", sim_idm_entry_get_fqdns (entry), host_id_str, sim_uuid_get_db_string (context_id));
sim_database_execute_no_query (database, query);
g_free (query);
}
query = NULL;
// 'host_ip' table
if (changes->ip || changes->mac)
{
const gchar *mac_text;
gchar *mac_bin;
mac_text = sim_idm_entry_get_mac (entry);
if (is_ip_update)
{
if (mac_text)
{
mac_bin = sim_mac_to_db_string (mac_text);
query = g_strdup_printf ("UPDATE host_ip SET ip=%s, mac=%s "
"WHERE host_id = %s",
ip_str,
mac_bin,
host_id_str);
g_free (mac_bin);
}
else
{
query = g_strdup_printf ("UPDATE host_ip SET ip=%s "
"WHERE host_id = %s",
ip_str,
host_id_str);
}
}
else
{
if (mac_text)
{
mac_bin = sim_mac_to_db_string (mac_text);
query = g_strdup_printf ("REPLACE host_ip (host_id, ip, mac) VALUES (%s, %s, %s)", host_id_str, ip_str, mac_bin);
g_free (mac_bin);
}
else
{
query = g_strdup_printf ("REPLACE host_ip (host_id, ip) VALUES (%s, %s)", host_id_str, ip_str);
}
}
}
if (query)
{
sim_database_execute_no_query (database, query);
g_free (query);
}
// 'host_properties' table
if (changes->username)
{
property = (gchar *)sim_idm_entry_get_username (entry);
/* Delete old usernames */
query = g_strdup_printf ("DELETE FROM host_properties WHERE host_id = %s AND property_ref = %d", host_id_str, SIM_HOST_PROP_USERNAME);
sim_database_execute_no_query (database, query);
g_free (query);
if (property != NULL && strlen(property) > 0)
{
/* Here, I need to SPLIT the user name. I need a row for each one */
gchar **usernames = NULL;
gchar **username_loop= NULL;
usernames = username_loop = g_strsplit (property, ",", -1);
while (*username_loop)
{
e_property = sim_database_str_escape (database, *username_loop, 0);
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%s')", host_id_str, SIM_HOST_PROP_USERNAME, sim_idm_entry_get_source_id (entry), e_property);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (e_property);
username_loop++;
}
g_strfreev (usernames);
}
}
if (changes->os)
{
//ENG-99163 We cannot use replace here, becuase value is part of the primary key.
// We only should allow one os per host_id.
// At this point we know that the revelance of the property>=old property relevance.
e_property = sim_database_str_escape (database, sim_idm_entry_get_os (entry), 0);
query = g_strdup_printf ("DELETE FROM host_properties WHERE host_id = %s and property_ref=%d", host_id_str, SIM_HOST_PROP_OS);
sim_database_execute_no_query (database, query);
g_free (query);
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%s')", host_id_str, SIM_HOST_PROP_OS, sim_idm_entry_get_source_id (entry), e_property);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (e_property);
}
if (changes->cpu)
{
e_property = sim_database_str_escape (database, sim_idm_entry_get_cpu (entry), 0);
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%s')", host_id_str, SIM_HOST_PROP_CPU, sim_idm_entry_get_source_id (entry), e_property);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (e_property);
}
if (changes->memory)
{
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%d')", host_id_str, SIM_HOST_PROP_MEMORY, sim_idm_entry_get_source_id (entry), sim_idm_entry_get_memory (entry));
sim_database_execute_no_query (database, query);
g_free (query);
}
if (changes->video)
{
e_property = sim_database_str_escape (database, sim_idm_entry_get_video (entry), 0);
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%s')", host_id_str, SIM_HOST_PROP_VIDEO, sim_idm_entry_get_source_id (entry), e_property);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (e_property);
}
if (changes->state)
{
e_property = sim_database_str_escape (database, sim_idm_entry_get_state(entry), 0);
query = g_strdup_printf ("REPLACE host_properties (host_id, property_ref, source_id, value) VALUES (%s, %d, %d, '%s')", host_id_str, SIM_HOST_PROP_STATE, sim_idm_entry_get_source_id (entry), e_property);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (e_property);
}
// 'host_services' table
if (changes->service)
{
#if 0
// Currently disabled
query = g_strdup_printf ("DELETE FROM host_services WHERE host_id = %s AND nagios = 0", host_id_str);
sim_database_execute_no_query (database, query);
g_free (query);
#endif
values = sim_idm_entry_service_get_string_db_insert (entry, database);
if (values)
{
query = g_strdup_printf ("INSERT INTO host_services (host_id, host_ip, port, protocol, service, version, source_id) VALUES %s ON DUPLICATE KEY UPDATE service = VALUES(service), source_id = VALUES(source_id)", values);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (values);
}
}
// 'host_software' table
if (changes->software)
{
values = sim_idm_entry_software_get_string_db_insert (entry, database);
if (values)
{
query = g_strdup_printf ("INSERT INTO host_software (host_id, cpe, banner, source_id) VALUES %s ON DUPLICATE KEY UPDATE banner = VALUES(banner), source_id = VALUES(source_id)", values);
sim_database_execute_no_query (database, query);
g_free (query);
g_free (values);
}
}
// Specific code for the web interface
if (changes->ip)
{
// These queries mitigate performance problems with many hosts/nets.
// Probably could be resolved with radix trees in the web
if (is_ip_update)
{
query = g_strdup_printf ("DELETE FROM host_net_reference WHERE host_id = %s",
host_id_str);
sim_database_execute_no_query (database, query);
g_free (query);
}
query = g_strdup_printf ("REPLACE INTO host_net_reference SELECT host.id, net_id FROM host, host_ip, net_cidrs "
"WHERE host.id = host_ip.host_id AND host_ip.ip >= net_cidrs.begin AND host_ip.ip <= net_cidrs.end AND host_id = %s",
host_id_str);
sim_database_execute_no_query (database, query);
g_free (query);
}
if (changes->ip || changes->username || changes->hostname || changes->mac || changes->os || changes->cpu || changes->memory || changes->video || changes->service || changes->software || changes->state)
{
// This query is exclusively used to notify the web server about changes on hosts/nets
//
// This could be executed in fewer cases by not caching some asset trees on the web
sim_database_execute_no_query (database, "REPLACE INTO config (conf, value) VALUES ('latest_asset_change', utc_timestamp())");
}
// Specific code for the web interface
}
gchar *
sim_event_get_insert_clause_values (SimEvent *event)
{
gchar time[TIMEBUF_SIZE];
gchar *timestamp = time;
GString *query;
gchar *values;
gchar *e_rep_act_src = NULL;
gchar *e_rep_act_dst = NULL;
gchar *e_src_hostname = NULL;
gchar *e_dst_hostname = NULL;
gchar *src_mac = NULL, *dst_mac = NULL;
GdaConnection *conn;
g_return_val_if_fail (SIM_IS_EVENT (event), NULL);
conn = sim_database_get_conn (ossim.dbossim);
values = sim_event_get_text_escape_fields_values (event);
// If we already have the timestamp we use it.. else we calculate it
if(event->time_str)
timestamp = event->time_str;
else
strftime (timestamp, TIMEBUF_SIZE, "%F %T", gmtime ((time_t *) &event->time));
if (event->str_rep_act_src)
e_rep_act_src = sim_str_escape (event->str_rep_act_src, conn, 0);
if (event->str_rep_act_dst)
e_rep_act_dst = sim_str_escape (event->str_rep_act_dst, conn, 0);
if (event->src_hostname)
e_src_hostname = sim_str_escape (event->src_hostname, conn, 0);
if (event->dst_hostname)
e_dst_hostname = sim_str_escape (event->dst_hostname, conn, 0);
if (event->src_mac)
src_mac = sim_mac_to_db_string (event->src_mac);
if (event->dst_mac)
dst_mac = sim_mac_to_db_string (event->dst_mac);
query = g_string_new ("");
g_string_append_printf (query, "(%s", sim_uuid_get_db_string (event->id));
g_string_append_printf (query, ",%s", sim_uuid_get_db_string (sim_context_get_id (event->context)));
g_string_append_printf (query, ",'%s'", timestamp);
g_string_append_printf (query, ",%f", event->tzone);
g_string_append_printf (query, ",%s", sim_uuid_get_db_string (event->sensor_id));
g_string_append_printf (query, ",'%s'", (event->interface) ? event->interface : "");
g_string_append_printf (query, ",%d", event->type);
g_string_append_printf (query, ",%d", event->plugin_id);
g_string_append_printf (query, ",%d", event->plugin_sid);
g_string_append_printf (query, ",%d", event->protocol);
g_string_append_printf (query, ",%s", sim_inet_get_db_string (event->src_ia));
g_string_append_printf (query, ",%s", sim_inet_get_db_string (event->dst_ia));
g_string_append_printf (query, ",%s", (event->src_net) ? sim_uuid_get_db_string (sim_net_get_id (event->src_net)) : "NULL");
g_string_append_printf (query, ",%s", (event->dst_net) ? sim_uuid_get_db_string (sim_net_get_id (event->dst_net)) : "NULL");
g_string_append_printf (query, ",%d", event->src_port);
g_string_append_printf (query, ",%d", event->dst_port);
g_string_append_printf (query, ",%d", event->condition);
g_string_append_printf (query, ",%d", event->interval);
g_string_append_printf (query, ",%d", 0); //FIXME event->absolute
g_string_append_printf (query, ",%d", event->priority);
g_string_append_printf (query, ",%d", event->reliability);
g_string_append_printf (query, ",%d", event->asset_src);
g_string_append_printf (query, ",%d", event->asset_dst);
g_string_append_printf (query, ",%d", (gint) event->risk_c);
g_string_append_printf (query, ",%d", (gint) event->risk_a);
g_string_append_printf (query, ",%d", event->alarm);
g_string_append_printf (query, ",%s", values);
g_string_append_printf (query, ",%u", event->rep_prio_src);
g_string_append_printf (query, ",%u", event->rep_prio_dst);
g_string_append_printf (query, ",%u", event->rep_rel_src);
g_string_append_printf (query, ",%u", event->rep_rel_dst);
g_string_append_printf (query, ",'%s'", (e_rep_act_src) ? e_rep_act_src : "");
g_string_append_printf (query, ",'%s'", (e_rep_act_dst) ? e_rep_act_dst : "");
g_string_append_printf (query, ",'%s'", (e_src_hostname) ? e_src_hostname : "");
g_string_append_printf (query, ",'%s'", (e_dst_hostname) ? e_dst_hostname : "");
g_string_append_printf (query, ",%s", (src_mac) ? src_mac : "NULL");
g_string_append_printf (query, ",%s", (dst_mac) ? dst_mac : "NULL");
g_string_append_printf (query, ",%s", (event->src_id) ? sim_uuid_get_db_string (event->src_id) : "NULL");
g_string_append_printf (query, ",%s)", (event->dst_id) ? sim_uuid_get_db_string (event->dst_id) : "NULL");
g_free (values);
return g_string_free (query, FALSE);
}
