What up y'all???
So I got a big environment, so I've been told....
Anyway, so I'm trying to add some servers to a perfstack diagnostics, where are the custom properties for my nodes in order to filter by?
Thanks
Chris
I looked again, I guess it must be something that was over looked in the product, here is my lazy admin solution, this will also create an email, via outlook, to send to someone who wants the last 90 days cpu and memory info for a list of nodes that have a custom property with a specific value:
#Get list of nodes with specific custom property
#Replace the bolded info with what is in your environment
$nodeid = (Invoke-Sqlcmd -Query "SELECT NodeID FROM dbo.NodesCustomProperties where custompropertyname = 'value of custom property'" -ConnectionString "Data Source=sqlserver;Initial Catalog=dbname;User id=dbuser;Password=mypassword").nodeid
$urls = "https://localhost/ui/perfstack/?charts="
foreach ($i in $nodeid)
{
$urls = $urls + "0_Orion.Nodes_" + $i + "-Orion.CPULoad.AvgLoad,0_Orion.Nodes_" + $i + "-Orion.CPULoad.AvgPercentMemoryUsed;"
}
$urls = $urls + "&presetTime=last90Days"
$ol = New-Object -comObject Outlook.Application
#Create the new email
$mail = $ol.CreateItem(0)
#Optional, set the subject
$mail.Subject = "<subject>"
#Optional, set the body
$mail.Body = $urls
#Get the new email object
$inspector = $mail.GetInspector
#Bring the message window to the front
$inspector.Activate()
Let me know if you have any questions and let me know how you go about solving this problem also, I'm sure someone has a better way.
You could also build that as a swql query inside your orion dashboard, if you prefer to do it in the UI instead of scripting.
I went ahead and cannibalized a query I have on my node details page that I embed with links to a few pre-defined prefstack charts for cpu/mem/response/loss to do this:
With a searched value
And if you click the cpu/memory/response/loss buttons it takes you to a perfstack with the relevant bits on it
First box:
--this is basically just a placeholder until you enter a value into the search box
Select top 1 'Generate Perfstack charts for nodes with a specific value in their Comment Custom Property.' as [Search] from orion.nodes
order by caption
Search box:
select distinct
n.caption as [Node]
, n.detailsurl as [_Linkfor_Node]
,isnull(alerts.[Active Alerts],0) as [Active Alerts]
,CASE
when alerts.[active alerts] is null then '/Orion/images/ActiveAlerts/Check.png'
WHEN sev.Severity = 4 THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN sev.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN sev.Severity = 2 THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN sev.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN sev.Severity = 1 THEN '/Orion/images/ActiveAlerts/Notice.png'
END AS [_iconfor_Active Alerts]
,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=last7Days&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]
WHEN cpuload >= htc.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN cpuload >= htc.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN cpuload < htc.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
WHEN cpuload >= n.CpuLoadThreshold.Level2Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN cpuload >= n.CpuLoadThreshold.Level1Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN cpuload < n.CpuLoadThreshold.Level1Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_CPU Load]
,case when percentmemoryused < 0 then 'Not Polled'
else concat(percentmemoryused,'% of ',(round(n.totalmemory/1073741824,0)),' GB')
end as [Memory Used]
,'/ui/perfstack/?presetTime=last7Days&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]
WHEN percentmemoryused >= htm.Level2Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN percentmemoryused >= htm.Level1Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN percentmemoryused < htm.Level1Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Up.gif'
WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN percentmemoryused < n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Memory Used]
WHEN responsetime<0 then 'No Response'
ELSE concat(responsetime,' ms')
END AS [Latency]
,'/ui/perfstack/?presetTime=last7Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Status,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.AvgResponseTime;' as [_linkfor_Latency]
WHEN responsetime >= n.responsetimeThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN responsetime >= n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN responsetime < n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Latency]
,concat(percentloss,'%') as [Packet Loss]
,'/ui/perfstack/?presetTime=last7Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.Availability,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Status,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.PercentLoss;' as [_linkfor_Packet Loss]
WHEN percentloss >= n.percentlossThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'
WHEN percentloss >= n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN percentloss < n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Packet Loss]
--,n.percentmemoryusedThreshold.Level1Value
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 Orion.VIM.HostThresholds htc on htc.host.NodeID=n.NodeID and htc.Name = 'VIM.Hosts.Stats.CPULoad'
left join Orion.VIM.HostThresholds htm on htm.host.NodeID=n.NodeID and htm.Name = 'VIM.Hosts.Stats.MemUsage'
left join (SELECT count(AlertActiveID) as [Active Alerts], aa.AlertObjects.RelatedNodeID
FROM Orion.AlertActive aa
group by aa.AlertObjects.RelatedNodeID) alerts on alerts.relatednodeid=n.nodeid
left join (SELECT aa.AlertObjects.RelatedNodeID
,max(CASE
WHEN aa.AlertObjects.AlertConfigurations.Severity = 2 THEN 4
WHEN aa.AlertObjects.AlertConfigurations.Severity = 3 THEN 3
WHEN aa.AlertObjects.AlertConfigurations.Severity = 1 THEN 2
WHEN aa.AlertObjects.AlertConfigurations.Severity = 4 THEN 1
WHEN aa.AlertObjects.AlertConfigurations.Severity = 0 THEN 0
END) AS [Severity]
group by aa.AlertObjects.RelatedNodeID
) sev on sev.relatednodeid=n.nodeid
where n.CustomProperties.Comments like '%${SEARCH_STRING}%'
order by n.cpuload
Thanks that's excellent!!!
I went ahead and put in a idea for this please vote if you think it would be helpful:
https://thwack.solarwinds.com/ideas/10454
