DX NetOps

Expand all | Collapse all

SRM Event count by Date and Time range

Jump to Best Answer
  • 1.  SRM Event count by Date and Time range

    Posted 01-02-2015 10:48 AM

    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 |

    +--------+



  • 2.  Re: SRM Event count by Date and Time range
    Best Answer

    Posted 01-03-2015 01:40 AM

    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



  • 3.  Re: SRM Event count by Date and Time range

    Broadcom Employee
    Posted 01-05-2015 05:46 AM

    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



  • 4.  Re: SRM Event count by Date and Time range

    Posted 01-05-2015 09:50 AM

    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';



  • 5.  Re: SRM Event count by Date and Time range

    Posted 01-07-2015 12:42 PM

    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)