diff options
author | Harald Welte <laforge@gnumonks.org> | 2012-11-15 00:12:56 +0100 |
---|---|---|
committer | Harald Welte <laforge@gnumonks.org> | 2015-01-01 13:03:03 +0100 |
commit | dc9b4e9ebf64e0227c85ebbb870254a1de6058d4 (patch) | |
tree | 87da43185fddbe0eb8f5d198b0a71f2042325791 /openbsc/src/utils | |
parent | 7465e4c2e19f25bad3c92288e323d4d0d9f7baf1 (diff) |
meas_feed: Add tool to read meas_feed PCAP and write it to sqlite3
In fact, the DB write code has been split out so we could later also
have a binary that would listen to realtime meas_feed packets and stuff
them into a database without any intermediate PCAP step.
The database schema contains a couple of convenience wrapper views, most
notably the "overview" view.
Diffstat (limited to 'openbsc/src/utils')
-rw-r--r-- | openbsc/src/utils/Makefile.am | 10 | ||||
-rw-r--r-- | openbsc/src/utils/meas_db.c | 323 | ||||
-rw-r--r-- | openbsc/src/utils/meas_db.h | 17 | ||||
-rw-r--r-- | openbsc/src/utils/meas_pcap2db.c | 141 |
4 files changed, 489 insertions, 2 deletions
diff --git a/openbsc/src/utils/Makefile.am b/openbsc/src/utils/Makefile.am index 015419969..3fd7e100c 100644 --- a/openbsc/src/utils/Makefile.am +++ b/openbsc/src/utils/Makefile.am @@ -2,10 +2,12 @@ AM_CPPFLAGS = $(all_includes) -I$(top_srcdir)/include -I$(top_builddir) AM_CFLAGS=-Wall $(LIBOSMOCORE_CFLAGS) $(LIBOSMOGSM_CFLAGS) $(LIBOSMOVTY_CFLAGS) $(LIBOSMOABIS_CFLAGS) $(COVERAGE_CFLAGS) AM_LDFLAGS = $(COVERAGE_LDFLAGS) +noinst_HEADERS = meas_db.h + if HAVE_LIBCDK -bin_PROGRAMS = bs11_config isdnsync meas_vis +bin_PROGRAMS = bs11_config isdnsync osmo-meas-pcap2db meas_vis else -bin_PROGRAMS = bs11_config isdnsync +bin_PROGRAMS = bs11_config isdnsync osmo-meas-pcap2db endif if BUILD_SMPP @@ -27,3 +29,7 @@ smpp_mirror_LDADD = $(top_builddir)/src/libcommon/libcommon.a \ meas_vis_SOURCES = meas_vis.c meas_vis_LDADD = $(LIBOSMOCORE_LIBS) $(LIBOSMOGSM_LIBS) -lcdk -lncurses meas_vis_CFLAGS = $(LIBOSMOCORE_CFLAGS) $(LIBOSMOGSM_CFLAGS) + +osmo_meas_pcap2db_SOURCES = meas_pcap2db.c meas_db.c +osmo_meas_pcap2db_LDADD = $(LIBOSMOCORE_LIBS) $(LIBOSMOGSM_LIBS) -lpcap -lsqlite3 +osmo_meas_pcap2db_CFLAGS = $(LIBOSMOCORE_CFLAGS) $(LIBOSMOGSM_CFLAGS) diff --git a/openbsc/src/utils/meas_db.c b/openbsc/src/utils/meas_db.c new file mode 100644 index 000000000..9fe269bf9 --- /dev/null +++ b/openbsc/src/utils/meas_db.c @@ -0,0 +1,323 @@ +/* Routines for storing measurement reports in SQLite3 database */ + +/* (C) 2012 by Harald Welte <laforge@gnumonks.org> + * + * All Rights Reserved + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation; either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * + */ + +#include <stdint.h> +#include <errno.h> +#include <string.h> + +#include <sqlite3.h> + +#include <osmocom/core/talloc.h> +#include <osmocom/core/utils.h> +#include <openbsc/meas_rep.h> + +#include "meas_db.h" + +#define INS_MR "INSERT INTO meas_rep (time, imsi, name, scenario, nr, bs_power, ms_timing_offset, fpc, ms_l1_pwr, ms_l1_ta) VALUES (?,?,?,?,?,?,?,?,?,?)" +#define INS_UD "INSERT INTO meas_rep_unidir (meas_id, rx_lev_full, rx_lev_sub, rx_qual_full, rx_qual_sub, dtx, uplink) VALUES (?,?,?,?,?,?,?)" +#define UPD_MR "UPDATE meas_rep SET ul_unidir=?, dl_unidir=? WHERE id=?" + +struct meas_db_state { + sqlite3 *db; + sqlite3_stmt *stmt_ins_ud; + sqlite3_stmt *stmt_ins_mr; + sqlite3_stmt *stmt_upd_mr; +}; + +/* macros to check for SQLite3 result codes */ +#define _SCK_OK(db, call, exp) \ + do { \ + int rc = call; \ + if (rc != exp) { \ + fprintf(stderr,"SQL Error in line %u: %s\n", \ + __LINE__, sqlite3_errmsg(db)); \ + goto err_io; \ + } \ + } while (0) +#define SCK_OK(db, call) _SCK_OK(db, call, SQLITE_OK) +#define SCK_DONE(db, call) _SCK_OK(db, call, SQLITE_DONE) + +static int _insert_ud(struct meas_db_state *st, unsigned long meas_id, int dtx, + int uplink, const struct gsm_meas_rep_unidir *ud) +{ + unsigned long rowid; + + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 1, meas_id)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 2, + rxlev2dbm(ud->full.rx_lev))); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 3, + rxlev2dbm(ud->sub.rx_lev))); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 4, ud->full.rx_qual)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 5, ud->sub.rx_qual)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 6, dtx)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 7, uplink)); + + SCK_DONE(st->db, sqlite3_step(st->stmt_ins_ud)); + + SCK_OK(st->db, sqlite3_reset(st->stmt_ins_ud)); + + return sqlite3_last_insert_rowid(st->db); +err_io: + exit(1); +} + +/* insert a measurement report into the database */ +int meas_db_insert(struct meas_db_state *st, const char *imsi, + const char *name, unsigned long timestamp, + const char *scenario, + const struct gsm_meas_rep *mr) +{ + int rc; + sqlite3_int64 rowid, ul_rowid, dl_rowid; + + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 1, timestamp)); + + if (imsi) + SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 2, + imsi, -1, SQLITE_STATIC)); + else + SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 2)); + + if (name) + SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 3, + name, -1, SQLITE_STATIC)); + else + SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 3)); + + if (scenario) + SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 4, + scenario, -1, SQLITE_STATIC)); + else + SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 4)); + + + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 5, mr->nr)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 6, mr->bs_power)); + + if (mr->flags & MEAS_REP_F_MS_TO) + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 7, + mr->ms_timing_offset)); + else + SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 7)); + + if (mr->flags & MEAS_REP_F_FPC) + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 1)); + else + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 0)); + + if (mr->flags & MEAS_REP_F_MS_L1) { + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 9, + mr->ms_l1.pwr)); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 10, + mr->ms_l1.ta)); + } + + SCK_DONE(st->db, sqlite3_step(st->stmt_ins_mr)); + SCK_OK(st->db, sqlite3_reset(st->stmt_ins_mr)); + + rowid = sqlite3_last_insert_rowid(st->db); + + /* insert uplink measurement */ + ul_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_UL_DTX, + 1, &mr->ul); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 1, ul_rowid)); + + /* insert downlink measurement, if present */ + if (mr->flags & MEAS_REP_F_DL_VALID) { + dl_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_DL_DTX, + 0, &mr->dl); + SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 2, dl_rowid)); + } else + SCK_OK(st->db, sqlite3_bind_null(st->stmt_upd_mr, 2)); + + /* update meas_rep with the id's of the unidirectional + * measurements */ + SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 3, rowid)); + SCK_DONE(st->db, sqlite3_step(st->stmt_upd_mr)); + SCK_OK(st->db, sqlite3_reset(st->stmt_upd_mr)); + + return 0; + +err_io: + return -EIO; +} + +int meas_db_begin(struct meas_db_state *st) +{ + SCK_OK(st->db, sqlite3_exec(st->db, "BEGIN", NULL, NULL, NULL)); + + return 0; + +err_io: + return -EIO; +} + +int meas_db_commit(struct meas_db_state *st) +{ + SCK_OK(st->db, sqlite3_exec(st->db, "COMMIT", NULL, NULL, NULL)); + + return 0; + +err_io: + return -EIO; +} + +static const char *create_stmts[] = { + "CREATE TABLE IF NOT EXISTS meas_rep (" + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "time TIMESTAMP," + "imsi TEXT," + "name TEXT," + "scenario TEXT," + "nr INTEGER," + "bs_power INTEGER NOT NULL," + "ms_timing_offset INTEGER," + "fpc INTEGER NOT NULL DEFAULT 0," + "ul_unidir INTEGER REFERENCES meas_rep_unidir(id)," + "dl_unidir INTEGER REFERENCES meas_rep_unidir(id)," + "ms_l1_pwr INTEGER," + "ms_l1_ta INTEGER" + ")", + "CREATE TABLE IF NOT EXISTS meas_rep_unidir (" + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "meas_id INTEGER NOT NULL REFERENCES meas_rep(id)," + "rx_lev_full INTEGER NOT NULL," + "rx_lev_sub INTEGER NOT NULL," + "rx_qual_full INTEGER NOT NULL," + "rx_qual_sub INTEGER NOT NULL," + "dtx BOOLEAN NOT NULL DEFAULT 0," + "uplink BOOLEAN NOT NULL" + ")", + "CREATE VIEW IF NOT EXISTS path_loss AS " + "SELECT " + "meas_rep.id, " + "datetime(time,'unixepoch') AS timestamp, " + "imsi, " + "name, " + "scenario, " + "ms_timing_offset, " + "ms_l1_ta, " + "fpc, " + "ms_l1_pwr, " + "ud_ul.rx_lev_full AS ul_rx_lev_full, " + "ms_l1_pwr-ud_ul.rx_lev_full AS ul_path_loss_full, " + "ud_ul.rx_lev_sub ul_rx_lev_sub, " + "ms_l1_pwr-ud_ul.rx_lev_sub AS ul_path_loss_sub, " + "ud_ul.rx_qual_full AS ul_rx_qual_full, " + "ud_ul.rx_qual_sub AS ul_rx_qual_sub, " + "bs_power, " + "ud_dl.rx_lev_full AS dl_rx_lev_full, " + "bs_power-ud_dl.rx_lev_full AS dl_path_loss_full, " + "ud_dl.rx_lev_sub AS dl_rx_lev_sub, " + "bs_power-ud_dl.rx_lev_sub AS dl_path_loss_sub, " + "ud_dl.rx_qual_full AS dl_rx_qual_full, " + "ud_dl.rx_qual_sub AS dl_rx_qual_sub " + "FROM " + "meas_rep, " + "meas_rep_unidir AS ud_dl, " + "meas_rep_unidir AS ud_ul " + "WHERE " + "ud_ul.id = meas_rep.ul_unidir AND " + "ud_dl.id = meas_rep.dl_unidir", + "CREATE VIEW IF NOT EXISTS overview AS " + "SELECT " + "id," + "timestamp," + "imsi," + "name," + "scenario," + "ms_l1_pwr," + "ul_rx_lev_full," + "ul_path_loss_full," + "ul_rx_qual_full," + "bs_power," + "dl_rx_lev_full," + "dl_path_loss_full," + "dl_rx_qual_full " + "FROM path_loss", +}; + +static int check_create_tbl(struct meas_db_state *st) +{ + int i, rc; + + for (i = 0; i < ARRAY_SIZE(create_stmts); i++) { + SCK_OK(st->db, sqlite3_exec(st->db, create_stmts[i], + NULL, NULL, NULL)); + } + + return 0; +err_io: + return -EIO; +} + + +#define PREP_CHK(db, stmt, ptr) \ + do { \ + int rc; \ + rc = sqlite3_prepare_v2(db, stmt, strlen(stmt)+1, \ + ptr, NULL); \ + if (rc != SQLITE_OK) { \ + fprintf(stderr, "Error during prepare of '%s': %s\n", \ + stmt, sqlite3_errmsg(db)); \ + goto err_io; \ + } \ + } while (0) + +struct meas_db_state *meas_db_open(void *ctx, const char *fname) +{ + int rc; + struct meas_db_state *st = talloc_zero(ctx, struct meas_db_state); + + if (!st) + return NULL; + + rc = sqlite3_open_v2(fname, &st->db, + SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, + NULL); + if (rc != SQLITE_OK) { + fprintf(stderr, "Unable to open DB: %s\n", + sqlite3_errmsg(st->db)); + goto err_io; + } + + rc = check_create_tbl(st); + + PREP_CHK(st->db, INS_MR, &st->stmt_ins_mr); + PREP_CHK(st->db, INS_UD, &st->stmt_ins_ud); + PREP_CHK(st->db, UPD_MR, &st->stmt_upd_mr); + + return st; +err_io: + talloc_free(st); + return NULL; +} + +void meas_db_close(struct meas_db_state *st) +{ + sqlite3_finalize(st->stmt_ins_mr); + sqlite3_finalize(st->stmt_ins_ud); + sqlite3_finalize(st->stmt_upd_mr); + sqlite3_close_v2(st->db); + + talloc_free(st); + +} diff --git a/openbsc/src/utils/meas_db.h b/openbsc/src/utils/meas_db.h new file mode 100644 index 000000000..889e9022f --- /dev/null +++ b/openbsc/src/utils/meas_db.h @@ -0,0 +1,17 @@ +#ifndef OPENBSC_MEAS_DB_H +#define OPENBSC_MEAS_DB_H + +struct meas_db_state; + +struct meas_db_state *meas_db_open(void *ctx, const char *fname); +void meas_db_close(struct meas_db_state *st); + +int meas_db_begin(struct meas_db_state *st); +int meas_db_commit(struct meas_db_state *st); + +int meas_db_insert(struct meas_db_state *st, const char *imsi, + const char *name, unsigned long timestamp, + const char *scenario, + const struct gsm_meas_rep *mr); + +#endif diff --git a/openbsc/src/utils/meas_pcap2db.c b/openbsc/src/utils/meas_pcap2db.c new file mode 100644 index 000000000..1022d4a31 --- /dev/null +++ b/openbsc/src/utils/meas_pcap2db.c @@ -0,0 +1,141 @@ +/* read PCAP file with meas_feed data and write it to sqlite3 database */ + +/* (C) 2012 by Harald Welte <laforge@gnumonks.org> + * + * All Rights Reserved + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation; either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * + */ + +#include <string.h> +#include <errno.h> +#include <unistd.h> +#include <stdlib.h> +#include <stdio.h> + +#include <netinet/in.h> +#include <netinet/ip.h> +#include <netinet/udp.h> + +#include <cdk/cdk.h> + +#include <osmocom/core/socket.h> +#include <osmocom/core/utils.h> +#include <osmocom/core/msgb.h> +#include <osmocom/core/select.h> +#include <osmocom/core/talloc.h> + +#include <osmocom/gsm/gsm_utils.h> + +#include <openbsc/meas_feed.h> + +#include <pcap/pcap.h> + +#include "meas_db.h" + +static struct meas_db_state *db; + +static void handle_mfm(const struct pcap_pkthdr *h, + const struct meas_feed_meas *mfm) +{ + const char *scenario; + + if (strlen(mfm->scenario)) + scenario = mfm->scenario; + else + scenario = NULL; + + meas_db_insert(db, mfm->imsi, mfm->name, h->ts.tv_sec, + scenario, &mfm->mr); +} + +static void pcap_cb(u_char *user, const struct pcap_pkthdr *h, + const u_char *bytes) +{ + const char *cur = bytes; + const struct iphdr *ip; + const struct udphdr *udp; + const struct meas_feed_meas *mfm; + uint16_t udplen; + + if (h->caplen < 14+20+8) + return; + + /* Check if there is IPv4 in the Ethernet */ + if (cur[12] != 0x08 || cur[13] != 0x00) + return; + + cur += 14; /* ethernet header */ + ip = (struct iphdr *) cur; + + if (ip->version != 4) + return; + cur += ip->ihl * 4; + + if (ip->protocol != IPPROTO_UDP) + return; + + udp = (struct udphdr *) cur; + + if (udp->dest != htons(8888)) + return; + + udplen = ntohs(udp->len); + if (udplen != sizeof(*udp) + sizeof(*mfm)) + return; + cur += sizeof(*udp); + + mfm = (const struct meas_feed_meas *) cur; + + handle_mfm(h, mfm); +} + +int main(int argc, char **argv) +{ + char errbuf[PCAP_ERRBUF_SIZE+1]; + char *pcap_fname, *db_fname; + pcap_t *pc; + int rc; + + if (argc < 3) { + fprintf(stderr, "You need to specify PCAP and database file\n"); + exit(2); + } + + pcap_fname = argv[1]; + db_fname = argv[2]; + + pc = pcap_open_offline(pcap_fname, errbuf); + if (!pc) { + fprintf(stderr, "Cannot open %s: %s\n", pcap_fname, errbuf); + exit(1); + } + + db = meas_db_open(NULL, db_fname); + if (!db) + exit(0); + + rc = meas_db_begin(db); + if (rc < 0) { + fprintf(stderr, "Error during BEGIN\n"); + exit(1); + } + + pcap_loop(pc, 0 , pcap_cb, NULL); + + meas_db_commit(db); + + exit(0); +} |