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

database query to get node information under monitored

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

0 Kudos
10 Replies
Level 8

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.

0 Kudos

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.

0 Kudos

Another flavor of this report that works better for large sets of servers is this one

pastedImage_0.png

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

- Marc Netterfield, Github

I really like this one.  Awesome work.  I have built a lot of custom views broken down to specific application/site/ERP.  This looks quite handy for those.

0 Kudos

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.

pastedImage_0.png

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

- Marc Netterfield, Github

Thanks hpstech. I checked the first one you mentioned.  CAST(ROUND(d.FreeSpaceB * 0.000000001, 2) AS DECIMAL(10,2)) AS 'FreeSpace'

is d.FreeSpaceB in bit or byte mode ?  

cheers

HS

0 Kudos
Level 13

A few home grown reports come close, but most do not combine CPU/MEM/DISK trend utilization for all three target objects.

Weekly Server Utilization Report

Server+Usage.xml

Simple Disk Usage Report (v1.01)

0 Kudos

thanks hpstech. For those xml report, how can I run it against the DB ? I know SQL very well but no XML.

cheers

0 Kudos

You have to go to Manage Reports page and import the XML file, it will then show up as a published report that you can make copies of and further customize.

0 Kudos

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

0 Kudos