SRM Event count by Date and Time range

[Anon Anon]

Is there a way to list the number of events in the reporting (SRM) DB for a certain date and time range.

 

For DDB db I use below and looking for equivalent for SRM db.

 

SELECT count(*) as cnt from ddmdb.event where utime > UNIX_TIMESTAMP('2014-07-25 00:00:00') and utime < UNIX_TIMESTAMP('2014-07-25 23:59:59');

+--------+

| cnt    |

+--------+

| 375109 |

+--------+

[Anon Anon]

Hello Saurabh,

 

Check this

 

SELECT COUNT(*) FROM event WHERE time >= '2013-08-01 00:00:00' and time <= '2013-08-10 00:00:00' group by type\G

 

Regards

Kalyan

[Joerg Liesenfeld]

Hi Saurabh,

background for this non_working syntax when working with Spectrum mysql database tables is the different layout for both. The "ddmdb" and the "reporting" databases are not with same declaration. You may use the "explain" function at native mysql-level to see the layout.

 

Like - when logged in to the mysql service - run it for the ddmdb and the reporting - sample here:

mysql> explain ddmdb.event;                                   

+----------------+----------------------+------+-----+---------+-------+

| Field          | Type                 | Null | Key | Default | Extra |

+----------------+----------------------+------+-----+---------+-------+

| model_h        | int(10) unsigned     | NO   | PRI | NULL    |       |

| utime          | int(11)              | NO   | PRI | NULL    |       |

| counter        | int(10) unsigned     | NO   | PRI | NULL    |       |

| clk_seq        | smallint(5) unsigned | NO   | PRI | NULL    |       |

| version        | tinyint(4)           | NO   | PRI | NULL    |       |

| node_id        | char(6)              | NO   | PRI | NULL    |       |

| user_key       | int(10) unsigned     | NO   |     | NULL    |       |

| type           | int(10) unsigned     | NO   | MUL | NULL    |       |

| severity       | int(10) unsigned     | NO   |     | NULL    |       |

| vardata_string | text                 | YES  |     | NULL    |       |

+----------------+----------------------+------+-----+---------+-------+

 

Cheers, Joerg

[Issac08]

1) Open a bash shell (bash -login)

 

2) Go to $SPECROOT/mysql/bin directory

 

3) Run:

    ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb

 

 

4)  select count(*) from event where time >= '2014-12-05 00:00:00' and time < '2015-01-05 00:00:00';

[Anon Anon]

All thanks for posting the answers.

 

I ended up using the below, converting the type to hex, for better readability.

 

SELECT HEX(type), COUNT(*) as cnt FROM event WHERE time >= '2014-12-27 00:00:00' and time <= '2014-12-28 00:00:00' group by type order by cnt desc limit 10;

 

+-----------+--------+

| HEX(type) | cnt    |

+-----------+--------+

| 4CD1243   | 258854 |

| 4CD1242   | 258844 |

| 10D67     |  78808 |

| 4CD02C4   |  78549 |

| 10D66     |  78462 |

| 4CD02C5   |  76526 |

| 1030A     |  74812 |

| 10801     |  71704 |

| 220002    |  69008 |

| 220004    |  68888 |

+-----------+--------+

10 rows in set (5.87 sec)