SQL Voodoo (dynamic asset groups)

[adam_jongewaard]

So I have done a bit of work with SQL and a mass deployment of Lansweeper servers globally. We have a centralized database of sites, subnets, etc and I wrote a powershell script that extracts the site and subnet data from that central database, connects to a site LS database and dumps the IP Scan Ranges, the Dynamic Asset Groups and the Dynamic Asset Filters.

The one thing that I'm finding is that there are plenty of assets that show up in the dynamic groups that you would expect them to be in, but many assets that just end up living under Undefined even though there is a dynamic group that they should be in.

Does anyone know why this might be? Am I missing a database entry somewhere that would impact this?

The guts of my code are in the spoiler below. There is a function to actually make the SQL connections and queries, and another to expand the IP addresses used in the Dynamic Groups that aren't shown here (eg: 12.123.24.1 is expanded to 012123024001)

# This first query simply joins a couple tables to get me all my subnets by site.  This DB is mine and is not a LS DB.
$DataSet = Get-SQLDataSet "centraldb.server.com" "SiteVLANs" "SELECT [LS_Servers].[SiteCode],[LS_Servers].[IPAddress] As LS_IP,[VLANs].[VLAN],[VLANs].[AboveBelow],[VLANs].[Description],[VLANs].[StartingIP],[VLANs].[EndingIP] FROM [Plant_VLANs].[dbo].[VLANs] INNER JOIN [Plant_VLANs].[dbo].[LS_Servers] ON LS_Servers.SiteCode=VLANs.SiteCode WHERE [LS_Servers].[Accessible]=1 ORDER BY LS_Servers.SiteCode"

foreach($row in $Dataset.Tables[0])
{
    If ($SubnetSiteCode -ne $row["SiteCode"]) {
        $SubnetSiteCode = $row["SiteCode"]
        $SubnetLSServer = $row["LS_IP"]
    }

    $SubnetIPStart = $row["StartingIP"]
    $SubnetExIPStart = Get-ExpandedIP $SubnetIPStart
    $SubnetIPEnd = $row["EndingIP"]
    $SubnetExIPEnd = Get-ExpandedIP $SubnetIPEnd
    $SubnetDesc = $row["SiteCode"] + "-" + $row["AboveBelow"] + "-VLAN" + $row["VLAN"] + " - " + $row["Description"]

    $SQLText_General = $row["SiteCode"] + "-" + $row["AboveBelow"] + "-VLAN" + $row["VLAN"]


############### Add Subnet to IP Scan Range

    # Get server name
    $SQLText_ServerName = "Select Servername from [lansweeperdb].[dbo].[tsysASServers]"
    $IDS_ServerName = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_ServerName

    # INSERTs Subnet into IP Scan table
    foreach($row in $IDS_ServerName.Tables[0])
    {
        $SQLText_IPScan = "INSERT INTO [lansweeperdb].[dbo].[tsysIPScanRanges] ([Servername],[Ipstart], [Ipend], [Enabled], [PingTimeout], [IPIgnoreWindows], [DontPing], [Day1], [Day2], [Day3], [Day4], [Day5], [Day6], [Day7], [Day1time], [Day2time], [Day3time], [Day4time], [Day5time], [Day6time], [Day7time], [NoSSH], [Recurring], [Minutes], [Waittime], [SSHport], [SavePingedIP], [IPIgnoreKnownWindows], [Description], [SIPport], [ScanNow]) VALUES ('" + $row["Servername"] + "','" + $SubnetIPStart + "','" + $SubnetIPEnd + "', 0, 2, 0, 0, 1, 1, 1, 1, 1, 1, 1, '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', '1900-01-01 12:00:00.000', 0, 0, 1, 1, 22, 1, 0, '" + $SubnetDesc + "', '5060', 0)"
        write-host Writing IP Subnet Scan - $SQLText_General
        $IDS_IPScan = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_IPScan
    }
    $row = 0


############### IP Location Stuff
    # Query sets the IP Location for each subnet
    $SQLText_IPLocation = "INSERT INTO [lansweeperdb].[dbo].[tsysIPLocations] ([StartIP],[EndIP],[IPLocation],[Realstart],[Realend],[PackageShare],[ShareUsername],[SharePassword]) VALUES ('" + $SubnetExIPStart + "','" + $SubnetExIPEnd + "','" + $SubnetDesc + "','" + $SubnetIPStart + "','" + $SubnetIPEnd + "','','','')"
    write-host Writing to IP Location - $SQLText_General
    $IDS_IPLoc = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_IPLocation


############### Group Filters
    # Query gets the max value of Location ID to set the next Location ID
    $SQLText_AssetGroupID = "Select max(AssetGroupID) + 1 As Next_AssetGroupID from [lansweeperdb].[dbo].[tblAssetGroups]"
    $AG_InternalDataSet = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_AssetGroupID
    $IDS_AssetGroupID = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_AssetGroupID

    # Query sets the Asset Group for the subnet
    $SQLText_AssetGroup_1 = "INSERT INTO [lansweeperdb].[dbo].[tblAssetGroups] ([AssetGroupID],[AssetGroup],[Builtin],[Dynamic]) VALUES ("
    $SQLText_AssetGroup_2 = ",'" + $SubnetDesc + "',0,1)"

    # Query sets the Asset Group Filter for the subnet
    $SQLText_AssetGroupF_1 = "INSERT INTO [lansweeperdb].[dbo].[tsysAssetGroupFilter] ([AssetGroupID],[Compare],[Operator],[Comparevalue],[VersionCompareValue]) VALUES ("
    $SQLText_AssetGroupF_2 = ",5,1,'" + $SubnetDesc + "','')"


############### Execute Dynamic Asset Things

    foreach($row in $IDS_AssetGroupID.Tables[0])
    {
        write-host Writing Filter Group $row["Next_AssetGroupID"] - $SQLText_General
        $SQLText_AssetGroup = $SQLText_AssetGroup_1 + $row["Next_AssetGroupID"] + $SQLText_AssetGroup_2
        $SQLText_AssetGroupF = $SQLText_AssetGroupF_1 + $row["Next_AssetGroupID"] + $SQLText_AssetGroupF_2
        $IDS_AssetGroup = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_AssetGroup
        $IDS_AssetGroupF = Get-SQLDataSet $SubnetLSServer "lansweeperdb" $SQLText_AssetGroupF
    }
    $row = 0
}