This is a place-holder for improvements related to Absolute Manage (rebranded as HEAT LANRev), a 3rd party solution for managing and tracking MAC (and PC) systems. I’m going to pull information from this system to supplement the existing PSMANAGE lists.
The first goal is to get missing MAC insight, but there may be Windows details this tool captures that will be useful, so will document anything added here.
For now, I just got a MySQL instance setup since the tool currently doesn’t support any other database for exporting the data it accumulates. I’ll use PowerShell to query that instance during the scheduled running of the server-side scripts.
Some notable tables that I’ll look to gather information from:
SELECT * FROM absolutemanageexport.agent_info WHERE AgentName = ‘GBESSO-LP’;
SELECT * FROM absolutemanageexport.agent_info_server WHERE agent_info_record_id = ‘2012’;
SELECT * FROM absolutemanageexport.asset_modification_info;
SELECT * FROM absolutemanageexport.hardware_battery_info;
SELECT * FROM absolutemanageexport.hardware_info;
SELECT * FROM absolutemanageexport.hardware_memory_info;
SELECT * FROM absolutemanageexport.hardware_poweron_info;
SELECT * FROM absolutemanageexport.installed_configuration_profile_info;
SELECT * FROM absolutemanageexport.installed_software_info;
SELECT * FROM absolutemanageexport.installed_software_titles;
SELECT * FROM absolutemanageexport.missing_patches_info;
SELECT * FROM absolutemanageexport.network_adapter_info;
SELECT * FROM absolutemanageexport.scsi_info;
SELECT * FROM absolutemanageexport.software_info;
SELECT * FROM absolutemanageexport.volumes_info;
SELECT * FROM absolutemanageexport.win_services_info;
SELECT * FROM absolutemanageexport.win_services_displaynames;
SELECT * FROM absolutemanageexport.win_services_servicenames;
The steps I’m taking to get this up and running are:
- Setting up a new MySQL instance somewhere on the network (Absolute Manage does not support any other database currently).
- Creating a new empty AbsoluteManage database, and a new user to connect to it.
- Configuring Absolute Manage to enable ODBC Export using the new MySQL instance.
- Exploring the AbsoluteManage database objects that are created, and identifying what values I’d like to gather.
- Creating the necessary statements in PowerShell to query the data into new PS objects.
- Combining this newly gathered information into the existing SharePoint lists to supplement the existing information.
- For MAC workstations, to gather details that they provided that are not in Active Directory.
- For PC workstations, if there are details in Absolute Manage that are not already gathered.
A full list of the MySQL tables I’m reading from for the Absolute Manage data:
agent_info_server
asset_modification_info
hardware_battery_info
hardware_info
hardware_memory_info
hardware_poweron_info
installed_configuration_profile_info
installed_software_info
installed_software_titles
missing_patches_info
network_adapter_info
scsi_info
software_info
volumes_info
win_services_info
win_services_displaynames
win_services_servicenames
win_services_descriptions
From those tables, a full list of the columns that are being returned, with the table name appended to the column names:
agent_info_server-id
agent_info_server-agent_info_record_id
agent_info_server-last_modified
agent_info_server-ServerAddress
agent_info_server-ServerPort
agent_info_server-HeartbeatInterval
agent_info_server-InventoryPushInterval
agent_info_server-BasicInventoryOnly
agent_info_server-IsPrimary
agent_info_server-ServerUniqueID
agent_info_server-InventoryWithFonts
agent_info_server-InventoryWithPrinters
agent_info_server-InventoryWithStartupItems
agent_info_server-InventoryWithWinServices
agent_info_server-ScanInstallerReceipts
agent_info_server-ScanApplicationFolder
agent_info_server-ScanForMissingPatches
agent_info_server-ApplicationScanOptions
agent_info_server-ScanForMissingThirdPartyPatches
agent_info_server-RowError
agent_info_server-RowState
agent_info_server-Table
agent_info_server-ItemArray
agent_info_server-HasErrors
asset_modification_info-id
asset_modification_info-agent_info_record_id
asset_modification_info-last_modified
asset_modification_info-LastInventoryInfo
asset_modification_info-LastFontInfo
asset_modification_info-LastPrinterInfo
asset_modification_info-LastStartupItemInfo
asset_modification_info-LastInstalledSoftwareInfo
asset_modification_info-LastMissingPatchesInfo
asset_modification_info-LastWinServicesInfo
asset_modification_info-LastMachineTrackingInfo
asset_modification_info-LastProcessInfo
asset_modification_info-LastInventoryUpdate
asset_modification_info-LastCustomFieldInfo
asset_modification_info-LastPowerOnDataInfo
asset_modification_info-RowError
asset_modification_info-RowState
asset_modification_info-Table
asset_modification_info-ItemArray
asset_modification_info-HasErrors
hardware_battery_info-id
hardware_battery_info-agent_info_record_id
hardware_battery_info-last_modified
hardware_battery_info-ACChargerConnected
hardware_battery_info-ACChargerCharging
hardware_battery_info-BatteryInstalled
hardware_battery_info-BatteryFullyCharged
hardware_battery_info-BatteryDesignCapacity
hardware_battery_info-BatteryMaxCapacity
hardware_battery_info-BatteryCurrentCapacity
hardware_battery_info-BatteryCycleCount
hardware_battery_info-BatterySerial
hardware_battery_info-BatteryManufacturer
hardware_battery_info-BatteryManufactureDate
hardware_battery_info-BatteryPercentageOfOriginalCharge
hardware_battery_info-BatteryChemistry
hardware_battery_info-BatteryDeviceName
hardware_battery_info-BatteryTemperature
hardware_battery_info-RowError
hardware_battery_info-RowState
hardware_battery_info-Table
hardware_battery_info-ItemArray
hardware_battery_info-HasErrors
hardware_info-id
hardware_info-agent_info_record_id
hardware_info-last_modified
hardware_info-MachineModel
hardware_info-MachineType
hardware_info-BootRom
hardware_info-CPUCount
hardware_info-CPUSpeed
hardware_info-CPUBusSpeed
hardware_info-CPU_L1_ICacheSize
hardware_info-CPU_L1_DCacheSize
hardware_info-CPU_L2_ICacheSize
hardware_info-CPU_L2_DCacheSize
hardware_info-CPU_L3_CacheSize
hardware_info-CPUVersion
hardware_info-CPUName
hardware_info-CPUVendor
hardware_info-CPUFamily
hardware_info-CPUModel
hardware_info-CPUStepping
hardware_info-CPUHasMMX
hardware_info-CPUHas3DNow
hardware_info-CPUHasSSE
hardware_info-CPUHasSSE2
hardware_info-CPUHyperthreading
hardware_info-CPUDualCore
hardware_info-PrimaryMACAddress
hardware_info-MachineSerialno
hardware_info-MachineProductionDate
hardware_info-MachineProductionFactory
hardware_info-PhysicalMemorySize
hardware_info-MemorySlotCount
hardware_info-MemorySlotsUsed
hardware_info-BootTime
hardware_info-UpTime
hardware_info-SwapSpaceTotal
hardware_info-SwapSpaceUsed
hardware_info-SwapSpaceFree
hardware_info-SwapSpaceEncrypted
hardware_info-CPUCountActive
hardware_info-CPUPhysicalProcessors
hardware_info-CPUHyperthreadingEnabled
hardware_info-CPUHasSSE3
hardware_info-SMCVersion
hardware_info-MachineAge
hardware_info-BIOSDate
hardware_info-BIOSVendor
hardware_info-BIOSVersion
hardware_info-SMBIOSBIOSVersion
hardware_info-MainboardManufacturer
hardware_info-MainboardProductName
hardware_info-MainboardSerialNumber
hardware_info-MainboardType
hardware_info-MainboardVersion
hardware_info-MainboardAssetTag
hardware_info-SystemEnclosureType
hardware_info-SystemEnclosureManufacturer
hardware_info-SystemEnclosureSerialNumber
hardware_info-SystemEnclosureVersion
hardware_info-SystemEnclosureAssetTag
hardware_info-ComputerManufacturer
hardware_info-ComputerModel
hardware_info-ComputerServiceTag
hardware_info-ComputerVersion
hardware_info-AppleProductName
hardware_info-AppleProductConfig
hardware_info-AppleWarrantyEnd
hardware_info-AppleWarrantyStatus
hardware_info-ApplePurchaseDate
hardware_info-AppleRegisterDate
hardware_info-ApplePurchaseCountry
hardware_info-AppleGlobalWarranty
hardware_info-AppleOnSiteWarrantyStart
hardware_info-AppleOnSiteWarrantyEnd
hardware_info-SystemSleepTimer
hardware_info-DiskSleepTimer
hardware_info-DisplaySleepTimer
hardware_info-AutomaticRestartOnPowerLoss
hardware_info-ActionOnPowerButton
hardware_info-WakeOnACChange
hardware_info-WakeOnClamshellOpen
hardware_info-ActionOnClamshellClose
hardware_info-ActionOnSleepButton
hardware_info-DisplaySleepUsesDim
hardware_info-ReduceBrightness
hardware_info-UPSInstalled
hardware_info-HibernationMode
hardware_info-TTYSKeepAwake
hardware_info-SuddenMotionSensor
hardware_info-SystemHibernateTimer
hardware_info-CPU64Bit
hardware_info-CPUIDString
hardware_info-PlatformArchitecture
hardware_info-UniqueComputerID
hardware_info-DellExpressServiceCode
hardware_info-BootDuration
hardware_info-BIOSType
hardware_info-DeviceDescription
hardware_info-DeviceColor
hardware_info-DeviceAssetTag
hardware_info-AppleDeviceModel
hardware_info-RowError
hardware_info-RowState
hardware_info-Table
hardware_info-ItemArray
hardware_info-HasErrors
hardware_memory_info-Count
hardware_memory_info-Length
hardware_memory_info-LongLength
hardware_memory_info-Rank
hardware_memory_info-SyncRoot
hardware_memory_info-IsReadOnly
hardware_memory_info-IsFixedSize
hardware_memory_info-IsSynchronized
hardware_poweron_info-id
hardware_poweron_info-agent_info_record_id
hardware_poweron_info-last_modified
hardware_poweron_info-PowerOnAvgPerDay
hardware_poweron_info-PowerOnAvgPerWeek
hardware_poweron_info-PowerOnAvgPerMonth
hardware_poweron_info-PowerOnAvgPerYear
hardware_poweron_info-PowerOnYesterday
hardware_poweron_info-PowerOnLast7Days
hardware_poweron_info-PowerOnLast30Days
hardware_poweron_info-PowerOnLast365Days
hardware_poweron_info-PowerOnTrackingStart
hardware_poweron_info-RowError
hardware_poweron_info-RowState
hardware_poweron_info-Table
hardware_poweron_info-ItemArray
hardware_poweron_info-HasErrors
installed_software_info-Count
installed_software_info-Length
installed_software_info-LongLength
installed_software_info-Rank
installed_software_info-SyncRoot
installed_software_info-IsReadOnly
installed_software_info-IsFixedSize
installed_software_info-IsSynchronized
network_adapter_info-id
network_adapter_info-agent_info_record_id
network_adapter_info-last_modified
network_adapter_info-ServiceID
network_adapter_info-AdapterName
network_adapter_info-IPConfigType
network_adapter_info-IPAddress
network_adapter_info-SubnetMask
network_adapter_info-RouterAddress
network_adapter_info-MACAddress
network_adapter_info-DNSServers
network_adapter_info-SearchDomains
network_adapter_info-LinkLinkStatus
network_adapter_info-LinkSpeed
network_adapter_info-LinkFullDuplex
network_adapter_info-LinkMTU
network_adapter_info-LinkMediaType
network_adapter_info-Vendor
network_adapter_info-DHCPServerAddress
network_adapter_info-IsPrimary
network_adapter_info-TCPImplementation
network_adapter_info-MacDeviceName
network_adapter_info-MacHardware
network_adapter_info-WinDNSSuffix
network_adapter_info-WinHost
network_adapter_info-WinScopeID
network_adapter_info-WinEnableDNSForWinsRes
network_adapter_info-WinEnableLMHOSTLookup
network_adapter_info-WinWINSResolution
network_adapter_info-WinWINSServer
network_adapter_info-RowError
network_adapter_info-RowState
network_adapter_info-Table
network_adapter_info-ItemArray
network_adapter_info-HasErrors
software_info-id
software_info-agent_info_record_id
software_info-last_modified
software_info-OSVersion
software_info-OSBuildNumber
software_info-OSPlatform
software_info-MacOSDarwinVersion
software_info-ComputerName
software_info-CurrentUser
software_info-CurrentUserIsAdmin
software_info-DST
software_info-GMTDelta
software_info-WOLSupported
software_info-WOLEnabled
software_info-MacFileSharing
software_info-MacWindowsSharing
software_info-MacWebSharing
software_info-MacRemoteLogin
software_info-MacFTPAccess
software_info-MacRemoteAppleEvents
software_info-MacPrinterSharing
software_info-RemoteDesktopAccess
software_info-RemoteAssistance
software_info-RemoteControlViaAssistance
software_info-TB2Access
software_info-VNCAccess
software_info-PCAnywhereAccess
software_info-OSInstallationDate
software_info-OSActivated
software_info-OSActivationGracePeriod
software_info-OSIsVolumeLicense
software_info-OSProductID
software_info-OSSerialnumber
software_info-FirewallEnabled
software_info-SecurityIdentifier
software_info-OSSoftwareUpdatesEnabled
software_info-CurrentLoginUserName
software_info-LastLoggedInShortUserName
software_info-LastLoggedInLongUserName
software_info-AD_MachineName
software_info-AD_MachineOUName
software_info-AD_MachineOUPath
software_info-AD_UserOUName
software_info-AD_UserOUPath
software_info-AD_UserMemberOfList
software_info-OSLanguage
software_info-OSServicePack
software_info-DefenderInstalled
software_info-DefenderEnabled
software_info-DefenderRealTimeProtection
software_info-DefenderAutoScanEnabled
software_info-DefenderEngineVersion
software_info-DefenderDefinitionVersion
software_info-VirtualMachine
software_info-FastUserSwitching
software_info-IdleTime
software_info-RemoteManagement
software_info-TMEnabled
software_info-TMLastBackup
software_info-TMOldestBackup
software_info-TMBackupDisk
software_info-TMCurrentState
software_info-TMBackupDiskSize
software_info-TMBackupDiskFreeSpace
software_info-TMBackupSnapshotCount
software_info-DameWareAccess
software_info-MachineBehindNAT
software_info-AD_MachineMemberOfList
software_info-DiskEncryptionProductName
software_info-DiskEncryptionProductVersion
software_info-DiskEncryptionStatus
software_info-DiskEncryptionAlgorithm
software_info-DiskEncryptionKeySize
software_info-FileVaultSupported
software_info-FileVaultEnabled
software_info-FileVaultAuthenticatedRestartSupported
software_info-FileVaultHasPersonalRecoveryKey
software_info-FileVaultHasInstitutionalRecoveryKey
software_info-FileVaultUnlockedUsingRecoveryKey
software_info-FileVaultRecoveryKeyStoredOnServer
software_info-LocalHostName
software_info-HostName
software_info-iTunesStoreAccountIsActive
software_info-iTunesStoreAccountHash
software_info-IsActivationLockEnabled
software_info-EASDeviceIdentifierEASDeviceIdentifier
software_info-AppleOSUpdateCatalogURL
software_info-AppleOSUpdateIsDefaultCatalog
software_info-AppleOSUpdatePreviousScanDate
software_info-AppleOSUpdatePreviousScanResult
software_info-AppleOSUpdatePerformPeriodicCheck
software_info-AppleOSUpdateAutomaticCheckEnabled
software_info-AppleOSUpdateBackgroundDownloadEnabled
software_info-AppleOSUpdateAutomaticAppInstallationEnable
software_info-AppleOSUpdateAutomaticOSInstallationEnabled
software_info-AppleOSUpdateAutomaticSecurityUpdatesEnabled
software_info-RowError
software_info-RowState
software_info-Table
software_info-ItemArray
software_info-HasErrors
volumes_info-id
volumes_info-agent_info_record_id
volumes_info-last_modified
volumes_info-Name
volumes_info-FileSystem
volumes_info-Size
volumes_info-FreeSpace
volumes_info-FreeSpacePercent
volumes_info-BootVolume
volumes_info-RefNum
volumes_info-CreationDate
volumes_info-ModificationDate
volumes_info-BackupDate
volumes_info-CheckedDate
volumes_info-NumFiles
volumes_info-NumFolders
volumes_info-LockedByHardware
volumes_info-LockedBySoftware
volumes_info-Journaled
volumes_info-CaseSensitive
volumes_info-DriveLetter
volumes_info-Serialno
volumes_info-Compressed
volumes_info-Type
volumes_info-BSDName
volumes_info-RowError
volumes_info-RowState
volumes_info-Table
volumes_info-ItemArray
volumes_info-HasErrors
greg's wordpress... 
When you created the LANrev MySQL database, did you create all of the tables and columns? Or just assign it to a DB with no tables or columns? I’m following your process, but I’m not seeing any columns populate. My DSN ODCB -to- MySQL connection is good when I test it. But LANrev keeps disabling ODBC export by itself for some reason. Also, LANrev is being hosted on the same server as MySQL Server and Workbench. Any help would be appreciated. Thank you.
LikeLike
Hi Josh sorry for the delay. I am dusting off my recollection of that but I most likely did not create any tables/columns at all, just a new empty database. In the example I used, the LanRev components are on a different box than the MySQL but it probably won’t matter as long as there is no port conflict. I have mine on port 3306 (default). For why it is disabling export, it’s possible you’re not saving the settings when you are exiting maybe? I don’t do much with LanRev/Absolute aside from this one connection, not sure what would make it disable/stop working.
LikeLike