This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • 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)

  • 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. emoticons_happy.png

  • 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

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

    cheers

  • 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.

  • 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

  • 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

  • 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.