How To - Query the CommServ DB for Event Information

Last post 09-23-2010, 5:27 AM by RALovell. 14 replies.
Sort Posts: Previous Next
  • How To - Query the CommServ DB for Event Information
    Posted: 07-01-2010, 4:39 PM

    We've got a group of views on the CommServ database with the names dbo.CommCell% that allow us to query various activities and report on them via tools other than the CommCell reporting module. 

    One of the things I am having difficulty with is determining how to query for specific Event information.  I am trying to query for situations where a database has been converted from FULL to SIMPLE recovery model and the Event Viewer is displaying the following message: "The [Transaction Log] Backup of [%d] is being converted to a Differential Database Backup because [SIMPLE] Recovery Model is enabled."

    I need to find these events using a query against the CommServ database, can that be accomplished via the views available in the DB?

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-01-2010, 5:07 PM

    Hi DB Dave,

    Check out htis link

    Here is the error code associated with the statement you are trying to draw on.

    http://documentation.commvault.com/commvault/release_8_0_0/books_online_1/english_us/error_codes/error_codes1.htm

    30:262 The [<String>] Backup of [<String>] is being converted to a Differential Database Backup because [<String>] Recovery Model is enabled.

     

    As you can see the error code is generated from several components in the DB drawing on the specifics of the job and components used in the backup.

    Working from this info.  Please elaborate on what you need.  Also look at the CommCell Explorer Views in BOL link below and the tool if you are licensed for it for data entry points.

    http://documentation.commvault.com/commvault/release_8_0_0/books_online_1/english_us/prod_info/commcell_explorer.htm

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-01-2010, 5:11 PM

    I'm trying to use the CommCell Explorer views to query Events looking specifically for the error code you mention.  I can't determine how to accomplish this with the CommCell Explorer views.

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-01-2010, 5:28 PM

    Hi DB Dave,

    THe error code exists in two pieces and is put together at time of error generation.

    I am not a SQL DB guru and will have to defer to others to help you get to where you want to go.

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-02-2010, 10:06 AM

    I think 'CNeventview' provides this information, assuming you know the messageID of the event.  If you have one recently you can easily track it down via jobID which the view provides.

     

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-02-2010, 12:26 PM

    That view is not documented in the CommCell Explorer Books Online so I don't fully understand all the data in the view.  The MessageID you mention is not what is displayed in the CommCell GUI, I only have the Event Code available, can you describe how to map the Event code displayed in the GUI to the MessageID in the view?

    Also, there are very few records in the CNEventView, what is the maximum length of time I can expect to find an event in this view from when the event is generated to when it is removed from the view?

    Is there anyplace I can view the events generated over hte last 24 hours, similar to the default Events report provided in the CommCell GUI?

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-02-2010, 1:28 PM

    The error you specified  has a messageID of 503316742. 

    Since the view is locked theres not much more I can supply. 

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-13-2010, 8:20 PM

    DBA Dave:

    Is there anyplace I can view the events generated over hte last 24 hours, similar to the default Events report provided in the CommCell GUI?

    Excuse my shocking t-sql but I just whipped this up, let me know if it does what you're after - you would probably need to throw in a join to translate the messageId to text, will give this a go when I get a chance...

    USE commserv;

    SELECT * FROM evMsg
    WHERE lastOccurrence > DATEDIFF(second, {d '1970-01-01'}, DATEADD("d", -1, GETDATE())) 
    ORDER BY id DESC 


    Cheers,
    Luke
    (@cv_skywalker)
  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-13-2010, 8:44 PM

    This seemed to work for me.

    -- Show events over last 24 hours.  Assumes en-us.

    SELECT e.id, e.timeSource, e.severity, e.timeServer, e.type,  e.jobId_h, e.jobId_l, e.client, e.subsystem,  e.lastOccurrence, emc.Message

    FROM evMsg e
    LEFT JOIN evMsgCache emc ON emc.EvMsgId = e.id

    WHERE lastOccurrence > DATEDIFF(second, {d '1970-01-01'}, DATEADD("d", -1, GETDATE()))
    AND emc.LocaleId = 0
    ORDER BY lastOccurrence DESC;


    Cheers,
    Luke
    (@cv_skywalker)
  • Re: How To - Query the CommServ DB for Event Information
    Posted: 07-14-2010, 12:18 PM

    That query is perfect!

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 09-22-2010, 10:43 AM

    Hi

    Sorry I am no DBA but I am the Simpana admin on my site.  My question is, can I run the SQL above against my own database without modification?

    Because I have exactly the same requirement as the original poster here, probably for the same reason - my reason being, I suspect there are many more Simple Recovery mode clients' backup jobs being converted than I know about, which are taking up more maglib space than they are entitled to!

     

    Thanks

    Rob

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 09-22-2010, 12:14 PM

    The query should work without modification for you as well.

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 09-23-2010, 5:00 AM

    Thanks Dave!  I will give it a go, this appears to be extremely useful to me.

     

    You mentioned that you have 'some' queries that you use - got any other good ones..? Wink

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 09-23-2010, 5:24 AM

    Hm..thanks for the code but when I ran it I got

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'evMsg'.

    Any ideas? (without spending too much time on it, not looking for free support)

  • Re: How To - Query the CommServ DB for Event Information
    Posted: 09-23-2010, 5:27 AM

    Oops disregard that,Embarassed user error, I had neglected to selct the correct database

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy