cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

In case anybody wants to see an example of how you would turn a grid of info like this into a SWQL report this is basically how you would get it done.  I'll caution you this is absolutely not "ready for prod" code since I don't have most of these types of systems in my lab, but just an example of the logic one could write.

select distinct ncp.Site_Name, temp.Temp
, '' as FW1
, '/Orion/images/StatusIcons/Small-' + fw1.statusicon as [_iconfor_FW1]
, '' as FW2
, '/Orion/images/StatusIcons/Small-' + fw2.statusicon as [_iconfor_FW2]
, fwa.Caption as [HA]
, fwa.CPULoad as [CPU]
, '/Orion/images/StatusIcons/Small-' + s.StatusName + '.gif' AS [_IconFor_HBSS]
, '' as HBSS
--smtp1.defs, smtp1.dq, smtp1.dfr
--smtp2.defs, smtp2.dq, smtp2.dfr
--proxy1.status
--proxy2.status
--av1.status
--av2.status

from orion.NodesCustomProperties ncp

--join the undp sensor from the environment monitor for each site, if there are more than one temp sensors then you need to adjust the where condition to isolate the ones we want to use for each location
left join (SELECT max(cpa.CurrentValue) as Temp, ncp.Site_Name
FROM Orion.NPM.CustomPollerAssignmentOnNode cpa
join orion.NodesCustomProperties ncp on ncp.NodeID=cpa.NodeID
where cpa.assignmentname like '%temp%'
group by ncp.Site_Name) temp on temp.site_name = ncp.Site_Name

--up down for firewall1, this light could display the status any kind of monitor relating to fw1 but for the sake of simplicity I went with status
left join (select n.StatusIcon, n.CustomProperties.Site_Name
from orion.nodes n
where n.caption like '%fw1%') fw1 on fw1.site_name = ncp.Site_Name

--up down for firewall2
left join (select n.StatusIcon, n.CustomProperties.Site_Name
from orion.nodes n
where n.caption like '%fw2%') fw2 on fw2.site_name = ncp.Site_Name

-- pulls the caption of whatever firewall node is currently active, you'd need to adjust the cpa.assignmentname like part to whatever you call your asa undp, or look up where this data is kept in the new ASA pieces but I don't have an ASA handy in the lab to confirm
left join (SELECT ncp.node.Caption, ncp.node.cpuload, ncp.Site_Name
FROM Orion.NPM.CustomPollerAssignmentOnNode cpa
join orion.NodesCustomProperties ncp on ncp.NodeID=cpa.NodeID
where cpa.assignmentname like '%ASAHAStatus%' and cpa.CurrentValue = 'Active') fwa on fwa.site_name = ncp.Site_Name

-- made some assumptions about this, but assuming by hbss you mean you are monitoring symantec EPO definitions I would set up a sam template that goes critical if the templates have not been recently updated, by using a max I am basically doing a lazy version of "worst status" of any EPO client at the site, if this was for prod I would probably build in some case logic here to be a bit more accurate
left join (SELECT a.node.CustomProperties.Site_Name, max(A.status) as Status
from orion.apm.Application a
where a.Name = 'SYMANTEC ENDPOINT PROTECTION CLIENT'
group by a.node.CustomProperties.Site_Name) hbss on hbss.site_name = ncp.Site_Name

--this is how I'm converting from the status integer to the name of the icon I want to use, as in status 14 will turned into 'critical'
left join orion.StatusInfo s on s.StatusId=hbss.status

--left join ( smtp1 defs.status, deliveryquue.statistic as dq, deliveryfailurereports.statistic as dfr) smtp1 on smtp1.site_name = ncp.Site_Name
--left join ( smtp2 defs.status, deliveryquue.statistic as dq, deliveryfailurereports.statistic as dfr) smtp2 on smtp2.site_name = ncp.Site_Name
--left join ( proxy1 status) proxy1 on proxy1.site_name = ncp.Site_Name
--left join ( proxy2 status) proxy2 on proxy2.site_name = ncp.Site_Name
--left join ( av1 status) av1 on av1.site_name = ncp.Site_Name
--left join ( av2 status) av2 on av2.site_name = ncp.Site_Name

where site_name is not null

Assuming you get things arranged correctly you would end up with something roughly like this, and you just keep figuring out what to join until you get all the things you want, but this example covers the main types of objects just change the names of the things you are looking for basically.

pastedImage_1.png

With the whole point of the exercise being that as new sites come up, or new sensors/firewalls/proxies whatever come online you can just set their properties and they automatically get added to the table instead of having to load up atlas and redraw them in and try to fuss with getting everything lined up nicely and all that.  The biggest pain point with really elaborate atlas diagrams is when they are no longer accurate and you have to start fixing all the unknown objects and such in them.

- Marc Netterfield, Github