Thanks for that Garin, really appreciated!
It gives me a new result, but not 100% sure what it means. Could it be due to blank usertags and origis ?
Or is it an error with something after "version" ?
Output is below. I did remove a couple of entries from the output below, due it including server names etc, but they looked correct.
I have also included the new version of the script below the output as a guide for line counts.
----------- Executing script at 3/28/2019 3:29:13 PM ----------
root:
last_config_reread:0
os_user2:
origin:
os_major:UNIX
ip:<removed>
os_minor:Linux
addr:<removed>
status:2
license:1
last_inst_change:0
created:1531243164
offline:0
os_user1:
last_change:0
lastupdate:1552313259
autoremove:0
os_description:Linux 3.10.0-862.11.6.el7.x86_64 #1 SMP Tue Aug 14 21:49:04 UTC 2018 x86_64
flags:1
metric_id:MB25ECBDC827B51D4EAFB33B4FD4DBDAC
name:<removed>
ssl_mode:0
device_id:D35A92031AC67FF4BFE1C36178E968934
heartbeat:900
port:48000
version:7.80 [Build 7.80.3132, Jun 1 2015]
Error in line 78: attempt to concatenate a nil value
--------------------------------------------
--------------------------------------------
--------------------------------------------
--------------------------------------------
-- This script polls the primary hub for a list of hubs, and polls each hub for its robot and probe information.
-- It then logs the robot and probe lists to tables in the NIS database.
---------- VARIABLES ----------
-- Domain to use for fetching hubs/robots/probes
NIMSOFT_DOMAIN = "UIM"
-- DB tables used for storing results
ROBOT_INVENTORY_TABLE = "Audit_RobotInventory"
PROBE_INVENTORY_TABLE = "Audit_ProbeInventory"
-- use this format to connect to the NIS DB - that same DB that data_engine connects to
DB_CONNECTION_STRING = "provider=nis;database=nis;driver=none"
-- use this format to specify some other (external) database if desired
--DB_CONNECTION_STRING = "Provider=SQLOLEDB;Data Source=DBSERVER;Initial Catalog=DBNAME;User ID=USER;Password=PASSWORD"
function tdump(t)
local function dmp(t, l, k)
if type(t) == "table" then
print(string.format("%s%s:", string.rep(" ", l*2), tostring(k)))
for k, v in pairs(t) do
dmp(v, l+1, k)
end
else
print(string.format("%s%s:%s", string.rep(" ", l*2), tostring(k), tostring(t)))
end
end
dmp(t, 1, "root")
end
-------------------------------
-- initially empty list of hubs (table meant to hold the full list of robots and probes)
hub_robot_probes = {}
-- get list of all hubs using the "gethubs" callback command
hubs = nimbus.request("hub", "gethubs")
if hubs == nil then
return
end
-- argument for the "getrobots" callback command on the hub probe
getrobots_args = pds.create()
pds.putString(getrobots_args, "name", "") -- all robots
pds.putInt(getrobots_args, "detail", 1) -- request detailed (probe) info about each = 1
--validate hubs.hublist
if hubs.hublist == nil then
return
end
-- iterate through all hubs
for hub_index, hub_entry in pairs (hubs.hublist) do
-- make sure we're only checking our domain (avoid visible test/dev domains)
if hub_entry.domain == NIMSOFT_DOMAIN then
-- initialize empty robot-list for this hub
hub_robot_probes[hub_entry.name] = { }
-- request robot/probe list using the hub's address, the command name, and the constructed argument
local robots, hub_stat = nimbus.request(hub_entry.addr, "getrobots", getrobots_args)
-- if request was successful
if hub_stat == 0 then
-- iterate through robot list
for r_row,r_entry in pairs (robots.robotlist) do
if ( r_entry.probelist == nil ) then -- catch questionable data situation
tdump(r_entry) -- print enclosing table data
print(nil .. nil) -- artificial error to stop code running
end
-- add entry into our table for this robot and its status
hub_robot_probes[hub_entry.name][r_entry.name] = { }
hub_robot_probes[hub_entry.name][r_entry.name]["status"] = r_entry.status
hub_robot_probes[hub_entry.name][r_entry.name]["created"] = r_entry.created
hub_robot_probes[hub_entry.name][r_entry.name]["ip"] = r_entry.ip
hub_robot_probes[hub_entry.name][r_entry.name]["addr"] = r_entry.addr
hub_robot_probes[hub_entry.name][r_entry.name]["version"] = r_entry.version
hub_robot_probes[hub_entry.name][r_entry.name]["os_user1"] = r_entry.os_user1
hub_robot_probes[hub_entry.name][r_entry.name]["os_user2"] = r_entry.os_user2
hub_robot_probes[hub_entry.name][r_entry.name]["os_major"] = r_entry.os_major
hub_robot_probes[hub_entry.name][r_entry.name]["os_minor"] = r_entry.os_minor
hub_robot_probes[hub_entry.name][r_entry.name]["os_description"] = r_entry.os_description
hub_robot_probes[hub_entry.name][r_entry.name]["maint_until"] = r_entry.maint_until
hub_robot_probes[hub_entry.name][r_entry.name]["probes"] = { }
-- only pay attention to robots that currently have a normal status
-- (other robots will not have a probe list anyway)
if r_entry.status == 0 then
-- iterate through probe list
for p_row,p_entry in pairs (r_entry.probelist) do
-- add entry into our table for this probe and its status
hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name] = { }
hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name]["active"] = p_entry.active
if (hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name]["pkg_version"] == nil) then
hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name]["pkg_version"] = "-"
end
hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name]["pkg_version"] = p_entry.pkg_version
hub_robot_probes[hub_entry.name][r_entry.name]["probes"][p_entry.name]["last_started"] = p_entry.last_started
-- print(r_entry.addr .. "/" .. p_entry.name .. ": " .. p_entry.active)
end
end
end
end
end
end
-- de-construct this PDS request (argument), as it requires clean-up
pds.delete(getrobots_args)
-- prepare to build query text for inserting of records into the database
robot_insert_queries = { }
probe_insert_queries = { }
robot_insert_query_original = "INSERT INTO " .. ROBOT_INVENTORY_TABLE .. " (Hub, Robot, Created, RobotStatus, IPAddress, RobotAddress, RobotVersion, UserTag1, UserTag2, OSMajor, OSMinor, OSDescription, MaintUntil) VALUES "
robot_insert_query = robot_insert_query_original
probe_insert_query_original = "INSERT INTO " .. PROBE_INVENTORY_TABLE .. " (Hub, Robot, Probe, ProbeActiveState, ProbeVersion, ProbeStarted) VALUES "
probe_insert_query = probe_insert_query_original
-- iterate through our full (now-populated) list (hubs)
for h, rl in pairs(hub_robot_probes) do
-- print(h)
-- iterate through robots
for r, rinfo in pairs(rl) do
--print(r)
-- determine if created value is valid or not, and parse it if so
local robot_created = "NULL"
if (rinfo.created ~= nil and rinfo.created > 0) then
robot_created = "'" .. timestamp.format(rinfo.created, "%c") .. "'"
end
-- If robot is in Maintenance Mode, then format the date and add quotes
local maint_until = "NULL"
if (rinfo.status == 4) then
maint_until = "'" .. timestamp.format(rinfo.maint_until, "%c") .. "'"
end
-- build query for insertion of robot records
robot_insert_query = robot_insert_query .. "('" .. h .. "','" .. r .. "'," .. robot_created .. "," .. rinfo.status .. ",'" .. rinfo.ip .. "','" .. rinfo.addr .. "','" .. rinfo.version .. "','" .. rinfo.os_user1 .. "','" .. rinfo.os_user2 .. "','" .. rinfo.os_major .. "','" .. rinfo.os_minor .. "','" .. rinfo.os_description .. "'," .. maint_until ..")"
-- print(r .. ": " .. rinfo.status)
table.insert (robot_insert_queries, robot_insert_query)
robot_insert_query = robot_insert_query_original
-- Check if this robot was on-line/OK
if rinfo.status == 0 then
-- iterate through probes
for probe,pinfo in pairs (rinfo.probes) do
-- determine if last started value is valid or not, and parse it if so
local probe_started = "NULL"
if (pinfo.last_started ~= nil and pinfo.last_started > 0) then
probe_started = "'" .. timestamp.format(pinfo.last_started, "%c") .. "'"
end
--If Package Version comes back empty, make it a dash
if (pinfo.pkg_version == nil) then
pkg_version = "'-'"
else
pkg_version = pinfo.pkg_version
end
--print(" " .. probe .. ": " .. pinfo)
-- build query for insertion of probe records
probe_insert_query = probe_insert_query .. "('" .. h .. "','" .. r .. "','" .. probe .. "'," .. pinfo.active .. ",'" .. pkg_version .. "'," .. probe_started .. ")"
-- print(" " .. probe .. ": " .. pinfo)
-- add constructed query to list of queries
table.insert (probe_insert_queries, probe_insert_query)
-- reset the probe query so it can be build again for the next robot
probe_insert_query = probe_insert_query_original
end
end
end
end
-- open connection to NIS database
db_stat = database.open(DB_CONNECTION_STRING)
-- Create tables if they don't exist or just truncate them
RobotTable = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '" .. ROBOT_INVENTORY_TABLE .. "') TRUNCATE TABLE " .. ROBOT_INVENTORY_TABLE .. " ELSE BEGIN CREATE TABLE " .. ROBOT_INVENTORY_TABLE .. " (Hub varchar(55), Robot varchar(55), Created datetime, RobotStatus int, IPAddress varchar(55), RobotAddress varchar(100), RobotVersion varchar(55), UserTag1 varchar(55), UserTag2 varchar(55), OSMajor varchar(55), OSMinor varchar(100), OSDescription varchar(100),MaintUntil datetime); END"
ProbeTable = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '" .. PROBE_INVENTORY_TABLE .. "') TRUNCATE TABLE " .. PROBE_INVENTORY_TABLE .. " ELSE BEGIN CREATE TABLE " .. PROBE_INVENTORY_TABLE .. " (Hub varchar(55), Robot varchar(55), Probe varchar(55), ProbeActiveState int, ProbeVersion varchar(55), ProbeStarted datetime); END"
rs = database.query(RobotTable)
rs = database.query(ProbeTable)
-- insert hub/robot list
for i,q in pairs (robot_insert_queries) do
-- insert robot/probe list
rs = database.query(q)
-- print (i .. ": " .. q)
end
-- iterate through robot/probe queries
for i,q in pairs (probe_insert_queries) do
-- insert robot/probe list
rs = database.query(q)
-- print (i .. ": " .. q)
end
-- close database connection
database.close()