Client Management Suite

 View Only

  • 1.  Configuration Request Table

    Posted Mar 12, 2014 10:59 AM

    Good morning everyone,

      We use a report that includes the table Evt_NS_Client_Config_Request in an automation policy to automatically set Active computers that have not requested a configuration in 30 days to missing and generate a service desk ticket. Using the default data purge policy we run into issues where we have more configuration requests then the table is set to hold by default. Normally when an asset is checking in properly this isn't a problem as old config request events get purged and the recent ones are kept. In the case of missing computers there have been times where an asset never goes missing because the last time it checked in has been purged before the job ran to mark it as missing and it doesn't show on the report. Initally my thought was to just set the purge setting to not purge data out for 45 days. This makes sure the data exists in the table so the asset can be marked as missing. While this does technically solve the problem we have almost 3 million rows in the Evt_NS_Client_Config_Request table.  

     

    So I had a thought, why not manually purge out this table using a scheduled job in SQL so that only the most recent config request is kept in the database, I can then run this once a day and ultimately solve the problem.

     

    My SQL is pretty basic for this:

    Delete from Evt_NS_Client_Config_Request where _id not in (
    select temp.ID from (select max(_id) as ID, resourceguid from Evt_NS_Client_Config_Request
    group by ResourceGuid) as temp)
     
    On the surface this seems to fit my need. It also seems like it would help optimize performance as well,  as we're not keeping track of every single time the agent chcked in. This would especially be the case when and end user clicked update configuration a dozen times waiting for a policy to download.  Is there anything I'm missing? Is there some reason that I haven't thought of that we'd want to keep track of the dozens of times an agent requests a configuration update? To my math if we have 3700 machines and each machine is only on 8 hours (more then likely longer then this) a day, checks in once an hour we'd have at mimium 29600 rows per day in that table. 
     
    Thanks in advance!
     
    Craig


  • 2.  RE: Configuration Request Table

    Posted Mar 12, 2014 04:20 PM

    Why are you including the ID column in the query, when you can simply use the ResourceGuid column on its own?

    One thing to be aware of is that every row that is deleted will be one record in the transaction log, which means that deleting very large amounts of rows will make the t-log grow large, causing possible performance issues.

    It is much better to transfer the data you want to keep to a temp table, truncate the target table, and then repopulate it with the data from the temp table, and then drop the temp table.



  • 3.  RE: Configuration Request Table

    Posted Mar 12, 2014 05:57 PM
    I'm using the id column so I can group them and remove all but the most recent record for each resourceguid. The Id is set to auto increment so I basically select the highest id for each resource guid and drop the rest of the ids for each resourceguid. I will rewrite to use a temp table though, that's a great suggest I hadn't thought of. Do you think there's any risk of removing the extra rows (config requests) from the table? Thanks, Craig


  • 4.  RE: Configuration Request Table

    Posted Mar 13, 2014 01:02 AM
    Resource Manager uses that table in order to display the last config request for the machine in queation. This shouldnt be affected. There are a couple of reports that also use that table, one of which is a performance report I believe, so if you dont use those reports you should be alright.