hi there:
We have over 100 nodes under monitoring and info has been stored in the backend DB of SAM. Does anyone have tried pull those info using customized sql query?
The info I am currently interested is Disk space, CPU&MEM utilization.
Thanks
HS
hi there:
We have over 100 nodes under monitoring and info has been stored in the backend DB of SAM. Does anyone have tried pull those info using customized sql query?
The info I am currently interested is Disk space, CPU&MEM utilization.
Thanks
HS
A few home grown reports come close, but most do not combine CPU/MEM/DISK trend utilization for all three target objects.
well, those data must be stored somewhere in the DB. That's how the UI picks up and have it displayed . Since there are so many nodes, click each one is tedious and not realistic. The best way might be simply retrieving from the back. cheers
Is that info in the database? Yes
Can it be retrieved? Yes
How are you trying to view it?
If you are looking for trouble/issues, you can customize your page and add "Top XX" widgets for all of that info. Or you can use canned/custom alerts to notify you of issues. Or schedule reports that include that information as hpstech mentions.
Your question cannot be more accurately answered without knowing what you are trying to accomplish with your quesiton above.
Hes asking for a report that has been asked in many threads.
A report that shows trends of CPU/DISK/MEM for a group of servers.
It has not been pulled together in a clean way since disks are difficult to list at the server level along with CPU/MEM.
There is no report available that does this in a clean way for a big group of servers.
The dream utilization report for Linux and Windows servers alludes us...still.
I will just throw this out there, I have posted the foundations of such a report using SWQL and/or SQL in several threads but I think that uptake has been marginal so I haven't spent further time on it. This morning I fleshed it out a bit further but I have been very busy and haven't been able to allocate more than 20-30 minutes at a time to it (usually while I wait for a config wizard somewhere). Anyway this is the current iteration of it, I have a template laid out for the next phase of it that will union in the cpu/memory/capacity forecasts/etc but you know, only so many hours in the day to spend on Thwack ideas.
SELECT
n.caption as Node,
'/netperfmon/images/vendors/' + n.VendorIcon as [_IconFor_Node],
n.DetailsUrl as [_LinkFor_Node],
n.ipaddress as IP_Address,
n.ObjectSubType as Polling_Method,
n.StatusDescription as Status,
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Status],
'' as [ ],
n.nodeid as [_linkfor_ ]
FROM orion.nodes n
UNION ALL (
SELECT
(' - '+ I.Caption) as Interface,
'/netperfmon/images/interfaces/' + i.InterfaceIcon AS [_IconFor_Interface],
i.DetailsUrl as [_LinkFor_Interface],
CASE WHEN i.IPAddress.IPAddress is not null then i.IPAddress.IPAddress
ELSE ip.IPAddress
END AS IP_Address,
NULL as Polling_Method,
concat(tostring(InPercentUtil),'% RX Utilization, ',tostring(OutPercentUtil),'% TX Utilization') as Status,
'/Orion/images/StatusIcons/Small-' + i.Statusicon AS [_IconFor_Status],
'' as [ ],
i.nodeid as [_linkfor_ ]
FROM Orion.NPM.Interfaces I
left join orion.NodeIPAddresses ip on ip.NodeID=i.NodeID and ip.IPAddressType='IPv4' and ip.interfaceindex is null
)
UNION ALL (
SELECT
(' - '+V.Caption) as Volume,
'/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Volume],
v.DetailsUrl as [_LinkFor_Volume],
NULL as IP_Address,
NULL as Polling_Method,
concat(round(v.VolumePercentUsed,0),'% of ',round(v.volumespaceavailable/1073741824,0),' GB total') as Status,
'/Orion/images/StatusIcons/Small-' + v.StatusLED AS [_IconFor_Status],
'' as [ ],
v.nodeid as [_linkfor_ ]
FROM Orion.Volumes V
)
UNION ALL (
SELECT
(' - '+A.Name) as Application,
'/orion/apm/images/statusicons/Small-App-Blank.gif' AS [_IconFor_Application],
a.DetailsUrl as [_LinkFor_Volume],
NULL as IP_Address,
NULL as Polling_Method,
a.StatusDescription as Status,
'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Status],
'' as [ ],
a.nodeid as [_linkfor_ ]
FROM Orion.APM.Application a
)
ORDER BY [_linkfor_ ], node desc
Another flavor of this report that works better for large sets of servers is this one
select
n.caption as [Server Name]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Server Name]
, n.detailsurl as [_linkfor_Server Name]
,case when n.cpuload < 0 then 'Not Polled'
when n.host.nodeid is not null and n.host.cpucorecount is not null then concat(round(n.host.cpuload,0),'% of ',n.host.CpuCoreCount,' CPU')
when cpu.[cpu count] is not null then concat(cpuload,'% of ',cpu.[cpu count],' CPU')
else 'Polling Error'
end as [CPU Load]
,'/ui/perfstack/?presetTime=last24Hours&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.AvgLoad,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.MaxLoad,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts;' as [_linkfor_CPU Load]
,CASE
WHEN n.cpuload >= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN n.cpuload >= n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN peak.cpu >= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Up-Down.gif'
WHEN peak.cpu >= n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up-Warning.gif'
WHEN n.cpuload < n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_CPU Load]
,case when n.percentmemoryused < 0 then 'Not Polled'
else concat(n.percentmemoryused,'% of ',(round(n.totalmemory/1073741824,0)),' GB')
end as [Memory Used]
,'/ui/perfstack/?presetTime=last24Hours&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.AvgPercentMemoryUsed,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.TotalMemory,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts;' as [_linkfor_Memory Used]
,CASE
WHEN n.percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN n.percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN peak.mem >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Up-Down.gif'
WHEN peak.mem >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up-Warning.gif'
WHEN n.percentmemoryused < n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Memory Used]
,case
when vol.high is null then 'None Monitored'
when voldown.down is not null then ((tostring(voldown.down)+' Down of ')+(tostring(vol.[# of Volumes])))
else (tostring(vol.[# of Volumes])+' Total')
end as [Volumes Status]
,'/Orion/DetachResource.aspx?ResourceID=4910&NetObject=N:' + tostring(n.nodeid) as [_linkfor_Volumes Status]
,CASE WHEN voldown.down is not null then '/Orion/images/StatusIcons/Small-Down.gif'
ELSE '/Orion/images/StatusIcons/Small-Up.gif'
end as [_iconfor_Volumes Status]
,case
when vol.high is null then 'N/A'
else (tostring(vol.high) + ' %')
end as [Fullest Disk]
,CASE
WHEN vol.[High] > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN vol.[High] > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN vol.[High] >= 0 THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Fullest Disk]
,'/Orion/DetachResource.aspx?ResourceID=4737&NetObject=N:' + tostring(n.nodeid) as [_linkfor_Fullest Disk]
FROM orion.nodes n
left join (SELECT count(NodeID) as [CPU Count], nodeid
FROM Orion.CPUMultiLoadCurrent
group by nodeid) cpu on cpu.nodeid=n.nodeid
left join (SELECT nodeid, count(volumeid) as [# of Volumes], round(max(volumepercentused),0) as [High] FROM Orion.Volumes where volumetype like 'fixed%' or volumetype like 'mount%' or volumetype like 'network%'
group by nodeid) vol on vol.nodeid=n.nodeid
left join (SELECT nodeid, count(volumeid) as [down]
FROM Orion.Volumes where status=2 and volumetype like 'fixed%' or volumetype like 'mount%' or volumetype like 'network%'
group by nodeid) voldown on voldown.nodeid=n.nodeid
left join (SELECT NodeID, max(AvgLoad) as [CPU], max(AvgPercentMemoryUsed) as [Mem]
FROM Orion.CPULoad
where hourdiff(datetime,getutcdate())<12
group by nodeid) Peak on peak.nodeid=n.NodeID
order by n.caption
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.