We've run into an issue in our environment where Nodes were imported in large blocks, polling engines were down for a period of time, or other similar situations that caused large groups of Nodes to fire off Asset Inventory discovery in a short period of time. These large Asset Inventory jobs would trigger Windows Updates discovery in our environment and throw a huge amount of unexpected (read: crippling) load onto our SAN and network.
My goal was to fix the Asset Inventory database table so that our 4,500 nodes were evenly distributed across our three day interval for Asset Inventory discovery.
(60seconds * 60minutes * 24hours * 3days) / 4500nodes = 57.6 seconds between each Node
To the SQL database! My first step was to disable Asset Inventory for all Nodes and then wait three days so that the LastPollDateTimeUtc value was > the 3 day Asset Inventory polling interval. It's also possible to update the LastPollDateTimeUtc to an old date, but I wasn't in a rush.
---
--Disable Asset Inventory for All Nodes
---
update solarwindsorion..assetinventory_polling
set [enabled] = 0
---
Three days after running the above, I created a recurring SQL job to select a random Node from the AssetInventory_Polling table which has Asset Inventory disabled, and enable it. This job would run every 55 seconds. For those unfamiliar with schedules SQL jobs:
- Under the Object Explorer, drill down into SQL Server Agent > Right Click Jobs > New Job.
- Give it a name, then click the Steps tab > New.
- Give the Step a name, select the SolarwindsOrion database, and paste the below script into the Command field.
- Press OK.
- Go to the Schedules tab and configure a schedule appropriate to your Node count and Asset Inventory polling interval.
- Save and close.
---
--Enable Asset Inventory for a Random Node
---
update solarwindsorion..assetinventory_polling
set [enabled] = 1
from solarwindsorion..assetinventory_polling a
inner join (
select top 1 newid() as randomizer,*
from solarwindsorion..assetinventory_polling
where [enabled] <> 1
order by randomizer
)b on a.nodeid = b.nodeid
---
You can verify that the job is running properly by right clicking the job and selecting History, or by running this SQL command to see if random Nodes are being re-enabled for Asset Inventory in the appropriate spread. The results of this query should be empty before the scheduled job starts.
---
--Check that SQL job is functioning
--
SELECT TOP 100 NEWID()
,[NodeID]
,[PollingDescriptionName]
,[LastPollDateTimeUtc]
,[Enabled]
FROM [SolarWindsOrion].[dbo].[AssetInventory_Polling]
WHERE Enabled = 1
---
I hope this helps someone out there!
