1 Reply Latest reply on Dec 15, 2011 10:51 PM by Catalysttgj

    Alert logs entries not deleted from database

    Rookie

      Does anyone knows how and when Landesk deletes alert records from the database when they are marked for purging?

      Our Landesk database contains currently almost 8 million alert records. Purged field is on all set to 1 and I would expect that Landesk deletes them during the daily maintenance, but it doesn't. Is there a way to delete them manually from the database. (I'm not really familiar with SQL statements)

        • 1. Re: Alert logs entries not deleted from database
          Catalysttgj Expert

          I see exactly the same thing in our alertlog table as well. The purge values are set to 1 on a lot of the rows, so what does that mean exactly? Since one would tend to assume that this would mean they're marked for deletion on the next maintenance pass. It begs the question, is this by design or is there a flaw somewhere? During some checking I discovered that we have a lot more entries in the table than the console will actually display. I experimented with looking at a month of time at a time by changing the start and end date values from 1 month to the next going further back in time. It seems that it stops showing information somewhere in may or june of this year, yet if i look in the database table I can see many entries in there going back into mid 2010. I don't understand this at all. Since I'm filtering a month at a time, I know i'm not overwhelming the memory for the console, so why it won't show the really old stuff just doesn't make any sense.

           

          At any rate, I played around with some SQL code to delete some information from the alertlog, and discovered that there is a relationship between this table and the AlertParameters table. In order to delete from alertlog you must first remove the rows in the Alertparameters that belong to the rows you want to remove in the alertlog table. This is typical, so no surprise there. This is what i came up with, but warning here, before you use this you really should be certain that its a good idea to use it at all. You might want to talk to a landesk support person before pulling the trigger on something like this since its going to be erasing information. At a minimum, back up that database first!

           

          This is what i came up with:

           

           

          use databasenamehere
          go
          delete ap from alertparameters as ap inner join
          alertlog as al on al.alertlog_idn = ap.alertlog_idn
          where al.alerttime < '2010-04-02'
          delete al from alertlog as al where al.alerttime < '2010-04-02'

           

          The first delete statement knocks out the alertparameters which is using the inner join statement to tie to the alertlog table and match on the same alerttime rule. and of course the second delete takes out the entries in alertlog using that same alerttime rule. You'll change the date in the single quotes to whatever date you want to use as your cutoff point, and its going to take out everything less than that.

           

          Hope thats simple enough to understand, but be careful. If you get sideways on this, get some help.

           

          Here is a nondestructive select statement to list everything you'd nuke with the above stuff:

           

          select * from alertlog as al inner join alertparameters as ap on ap.alertlog_idn = al.alertlog_idn where al.alerttime < '2010-04-02'

           

           

          If you haven't already read this other thread on alert logs, i'd suggest checking it out. It might prove very useful:

          http://community.landesk.com/support/message/11999#11999

           

          Hope that helps ya.

          Good luck!