Here are some SQL queries as doing weekly Solarwinds Maintenance and creating custom alerts that we as an MSP can't live without. I will most likely update this often but here are some I store in my evernote for now but I will update this frequently when i start looking through everything I use.
This first one isn't a SQL query but good for everyone use FoE to know:
cd C:\Program Files\SolarWinds\FoE\r2\bin\
nfpktfltr getstate
The failover should be set to Filter and the active should be PassThru
The nfpktfltr has a lot of good options to play with including forcing a set for filter or passthru in emergencies
Checking Ghost or Unknown Interfaces
select InterfaceName, Nodes.Caption from Interfaces
inner join Nodes on Nodes .NodeID = Interfaces.NodeID
where Interfaces. Status = 0
Checking Nodes Not in Groups
where
not Caption in (
SELECT distinct FullName
FROM ContainerMemberSnapshots
where EntityDisplayName = 'Node'
)
Checking Total Number of Elements for Custom Value
LEFT join Volumes on Volumes .NodeID = Nodes.NodeID
LEFT join Interfaces on Interfaces .NodeID = Nodes.NodeID
LEFT JOIN APM_Application on APM_Application .NodeID = Nodes.NodeID
where Custom = 'somethinghere'
Getting IP list for Weekly Sonar Discovery by polling engine
where
ObjectSubType = 'SNMP'
and EngineID = _Engine ID Here_
and Status <> '9
To get a list of your current engines with their ID number just simply run
select EngineID,ServerName,IP from Engines
Cleaning up bad hardware alerts manually
SET @NodeID = _NODE ID HERE_
DELETE FROM APM_HardwareInfo WHERE NodeID = @NodeID
DELETE FROM APM_HardwareCategoryStatus WHERE NodeID = @NodeID
DELETE FROM APM_HardwareItem WHERE NodeID = @NodeID
Alerting Variables for Statistical Data
inner join APM_DynamicEvidence_DetailData d on c.ID = d.ColumnSchemaID and c.ComponentID = $ {ComponentID}
where c.ThresholdWarning < d.NumericData group by c.ID, c.Name } is in WARNING at
${SQL:select MAX(d.NumericData) from APM_DynamicEvidenceColumnSchema c
inner join APM_DynamicEvidence_DetailData d on c .ID = d.ColumnSchemaID and c.ComponentID = ${ComponentID}
where c .ThresholdWarning < d.NumericData group by c.ID , c. Name }
Alerting URL on HTTP Components that is being pulled
This can be used to get any of the values simply changing the [Key] condition to whatever your looking for. Also make sure you set your component type in your trigger condition to 6 to avoid non-http applications sending this alert off.
${SQL:select Value from APM_ComponentSetting
where [Key] = 'Url' and ComponentID = '${ComponentID}'}
*****NEW****** Nice Heads up Display Board
The below strictly work under the condition that you have your alerts writing to NetPerfMon and you DO NOT SET the title of the alert action for Write to NetPerfMon and let solarwinds default it to the message you typed in when you save it with a blank title.
Given all the new upgrades and movements of tables and db schema changes recently with NPM and the introduction of SRM I've come up with new Critical and Action item scripts.
I've also moved them to a Stored Procedure as well instead of a view or report this is much faster, more accurate and easily executed.
To add these to a view or resource in your web console you just need set the resource as an Advanced Database Query select SQL for the Query Type and put in the execute for the stored procedure as the screen shot below
This is the script for Critical Items (This also gives a status of how long its been critical)
CREATE procedure [dbo].[sp_GetDownItems]
as
begin
SET NOCOUNT ON;
declare @i int;
declare @numrows int;
declare @curNode int;
declare @curAlert nvarchar(255);
declare @object nvarchar(max);
declare @entity varchar(255);
declare @objectId nvarchar(255);
declare @srmtable varchar(255);
declare @srmCol nvarchar(255);
declare @colParam nvarchar(500);
declare @colquery nvarchar(max);
declare @tblParam nvarchar(500);
declare @tblQuery nvarchar(max);
declare @customerdowns table (
vimg nvarchar(255) null,
simg nvarchar(255) null,
NodeName nvarchar(255) null,
Issue nvarchar(max) null,
IP_Address nvarchar(255) null,
TotalDownTime nvarchar(255) null,
AlertTime datetime null
);
declare @tempTable table (
id int Primary Key identity(1,1),
alertId bigint,
nodeid bigint,
entity nvarchar(255),
objectid nvarchar(255)
);
insert @tempTable select aa.AlertActiveID,ao.RelatedNodeId,ao.EntityType,ao.EntityNetObjectId from AlertActive as aa inner join AlertObjects as ao on aa.AlertObjectID = ao.AlertObjectID where aa.Acknowledged IS NULL
set @numrows = (select COUNT(*) from @tempTable)
set @i = 1
if @numrows > 0
while(@i <= @numrows)
begin
set @curNode = (select nodeid from @tempTable where id = @i)
set @curAlert = (select alertId from @tempTable where id = @i)
if (@curNode IS NOT NULL)
begin
insert @customerdowns
select '<img src=/NetPerfMon/images/Vendors/' + VendorIcon + '/>' as vimg
,'<img src=/NetPerfMon/images/small-' + GroupStatus + '/>' as simg
,'<a href=/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + cast(n.NodeID as varchar(255)) + '>' + n.Caption + '</a>' as NodeName
,SUBSTRING(ahv.Message,charindex(':', ahv.Message)+2,LEN(ahv.Message)) as Issue
,IP_Address,
convert(varchar(10), (DATEDIFF(d,0,GETDATE() - DateAdd(HH,-4,asv.TriggeredDateTime)))) + ' Days ' +
convert(varchar(10), (DATEDIFF(HH,0,GETDATE() - DateAdd(HH,-4,asv.TriggeredDateTime)) % 24 )) + ' Hours '+
convert(varchar(10), (DATEDIFF(mi,0,GETDATE()- DateAdd(HH,-4,asv.TriggeredDateTime)) % 60)) + ' Mins ' as 'Total_Down_Time'
,asv.TriggeredDateTime
from AlertActive as asv WITH(NOLOCK)
inner join (select MAX(CAST(AlertRefID as varchar(255)))AlertRefID,MAX(ObjectType)ObjectType,MAX(AlertHistoryID)AlertHistoryID,MAX(RelatedNodeId)RelatedNodeId,MAX(ActionTypeID)ActionTypeID,MAX(EventTypeWord)EventTypeWord,MAX(Message)Message,MAX(EntityCaption)EntityCaption,AlertActiveID from AlertHistoryView where ActionTypeID = 'WriteToNPMEventLog'
and EventTypeWord = 'ActionSucceeded' Group by AlertActiveID) as ahv on (asv.AlertActiveID = ahv.AlertActiveID)
inner join Nodes as n WITH(NOLOCK) on n.NodeID = ahv.RelatedNodeId
Left outer join APM_HardwareInfo as hw WITH(NOLOCK) on hw.NodeID = n.NodeID
WHERE asv.AlertActiveID = @curAlert
AND (n.Status = 2 AND ahv.Message LIKE '%down%'))
end
if (@curNode IS NULL)
begin
declare @newCol nvarchar(255)
create table #tmp (
colName nvarchar(255) null
)
set @object = (select objectid from @tempTable where id = @i);
set @entity = (select entity from @tempTable where id = @i);
set @objectId = SUBSTRING(@object,CHARINDEX(':',@object)+1,len(@object));
set @srmtable = 'SRM_' + REVERSE(SUBSTRING(REVERSE(@entity),0,CHARINDEX('.',REVERSE(@entity))));
select @colquery = N'select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @srmtable + ''' and ORDINAL_POSITION = 1';
SET @colParam = N'@retvalOUT nvarchar(255) OUTPUT';
insert into #tmp (colName) exec sp_executesql @colquery, @colParam, @retvalOUT=@srmCol OUTPUT;
set @newCol = (select colName from #tmp);
select @tblQuery = N'select n.Vendor as vimg
,''<img src=/Orion/StatusIcon.ashx?entity=Orion.SRM.StorageArrays&status='' + cast(n.Status as varchar(255)) + ''&size=small />'' as simg
,''<a href=/Orion/SRM/ArrayDetailsView.aspx?NetObject=SMSA%3a'' + cast(n.StorageArrayID as varchar(255)) + ''>'' + n.UserCaption + ''</a>'' as NodeName
,SUBSTRING(ahv.Message,charindex('':'', ahv.Message)+2,LEN(ahv.Message)) as Issue
,IPAddresses,
convert(varchar(10), (DATEDIFF(d,0,GETDATE() - DateAdd(HH,-4,asv.TriggeredDateTime)))) + '' Days '' +
convert(varchar(10), (DATEDIFF(HH,0,GETDATE() - DateAdd(HH,-4,asv.TriggeredDateTime)) % 24 )) + '' Hours ''+
convert(varchar(10), (DATEDIFF(mi,0,GETDATE()- DateAdd(HH,-4,asv.TriggeredDateTime)) % 60)) + '' Mins '' as ''Total_Down_Time''
,asv.TriggeredDateTime
from AlertActive as asv WITH(NOLOCK)
inner join (select MAX(CAST(AlertRefID as varchar(255)))AlertRefID,MAX(ObjectType)ObjectType,MAX(AlertHistoryID)AlertHistoryID,MAX(RelatedNodeId)RelatedNodeId,MAX(ActionTypeID)ActionTypeID,MAX(EventTypeWord)EventTypeWord,MAX(Message)Message,MAX(EntityCaption)EntityCaption,AlertActiveID from AlertHistoryView where ActionTypeID = ''WriteToNPMEventLog''
and EventTypeWord = ''ActionSucceeded'' Group by AlertActiveID) as ahv on (asv.AlertActiveID = ahv.AlertActiveID)
inner join ' + @srmtable + ' srmt on srmt.' + @newCol + ' = ''' + @objectId + '''
inner join SRM_StorageArrays n on n.StorageArrayID = srmt.StorageArrayID
inner join SRM_StorageArrayCustomProperties srmsac on srmsac.StorageArrayID = n.StorageArrayID
WHERE asv.AlertActiveID = ' + @curAlert + '
AND ((ahv.AlertRefID = ''69157a99-c259-4353-87f6-8a44b7f08cc9'')
OR (n.Status = 2 AND ahv.Message LIKE ''%down%''))';
set @tblParam = N'@retvalOUT nvarchar(255) OUTPUT';
insert into @customerdowns exec sp_executesql @tblQuery, @tblParam, @retvalOUT=@srmCol OUTPUT;
drop table #tmp;
end
set @i = @i + 1
end
select * from @customerdowns order by AlertTime desc
end
This is the script for Action Items
CREATE procedure [dbo].[sp_GetActionItems]
as
begin
SET NOCOUNT ON;
declare @i int;
declare @numrows int;
declare @curNode int;
declare @curAlert nvarchar(255);
declare @object nvarchar(max);
declare @entity varchar(255);
declare @objectId nvarchar(255);
declare @srmtable varchar(255);
declare @srmCol nvarchar(255);
declare @colParam nvarchar(500);
declare @colquery nvarchar(max);
declare @tblParam nvarchar(500);
declare @tblQuery nvarchar(max);
declare @internalaction table (
vimg nvarchar(255) null,
simg nvarchar(255) null,
NodeName nvarchar(255) null,
Issue nvarchar(max) null,
IP_Address nvarchar(255) null,
AlertTime datetime null
);
declare @tempTable table (
id int Primary Key identity(1,1),
alertId bigint,
nodeid bigint,
entity nvarchar(255),
objectid nvarchar(255),
fixedId bigint
);
insert @tempTable select aa.AlertActiveID,ao.RelatedNodeId,ao.EntityType,ao.EntityNetObjectId,REVERSE(SUBSTRING(REVERSE(ao.RealEntityUri),0,CHARINDEX('=',REVERSE(ao.RealEntityUri)))) as FixID from AlertActive as aa inner join AlertObjects as ao on aa.AlertObjectID = ao.AlertObjectID where aa.Acknowledged IS NULL
set @numrows = (select COUNT(*) from @tempTable)
set @i = 1
if @numrows > 0
while(@i <= @numrows)
begin
set @curNode = (select nodeid from @tempTable where id = @i)
set @curAlert = (select alertId from @tempTable where id = @i)
if (@curNode IS NOT NULL)
begin
insert @internalaction
select '<img src=/NetPerfMon/images/Vendors/' + VendorIcon + '/>' as vimg
,'<img src=/NetPerfMon/images/small-' + GroupStatus + '/>' as simg
,'<a href=/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + cast(n.NodeID as varchar(255)) + '>' + n.Caption + '</a>' as NodeName
,SUBSTRING(ahv.Message,charindex(':', ahv.Message)+2,LEN(ahv.Message)) as Issue
,IP_Address
,DATEADD(HH,-4,TriggeredDateTime) as AlertTime
from AlertActive as asv WITH(NOLOCK)
inner join (select MAX(CAST(AlertRefID as varchar(255)))AlertRefID,MAX(ObjectType)ObjectType,MAX(AlertHistoryID)AlertHistoryID,MAX(RelatedNodeId)RelatedNodeId,MAX(ActionTypeID)ActionTypeID,MAX(EventTypeWord)EventTypeWord,MAX(Message)Message,MAX(EntityCaption)EntityCaption,AlertActiveID from AlertHistoryView where ActionTypeID = 'WriteToNPMEventLog'
and EventTypeWord = 'ActionSucceeded' Group by AlertActiveID) as ahv on (asv.AlertActiveID = ahv.AlertActiveID)
inner join Nodes as n WITH(NOLOCK) on n.NodeID = ahv.RelatedNodeId
Left outer join APM_HardwareInfo as hw WITH(NOLOCK) on hw.NodeID = n.NodeID
WHERE asv.AlertActiveID = @curAlert
AND n.Status NOT IN (2,9)
end
if (@curNode IS NULL)
begin
declare @newCol nvarchar(255)
create table #tmp (
colName nvarchar(255) null
)
set @object = (select objectid from @tempTable where id = @i);
set @entity = (select entity from @tempTable where id = @i);
set @objectId = SUBSTRING(@object,CHARINDEX(':',@object)+1,len(@object));
if (LEN(@objectId) <= 0)
begin
set @objectId = (select fixedId from @tempTable where id = @i);
end
set @srmtable = 'SRM_' + REVERSE(SUBSTRING(REVERSE(@entity),0,CHARINDEX('.',REVERSE(@entity))));
select @colquery = N'select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @srmtable + ''' and ORDINAL_POSITION = 1';
SET @colParam = N'@retvalOUT nvarchar(255) OUTPUT';
insert into #tmp (colName) exec sp_executesql @colquery, @colParam, @retvalOUT=@srmCol OUTPUT;
set @newCol = (select colName from #tmp);
select @tblQuery = N'select n.Vendor as vimg
,''<img src=/Orion/StatusIcon.ashx?entity=Orion.SRM.StorageArrays&status='' + cast(n.Status as varchar(255)) + ''&size=small />'' as simg
,''<a href=/Orion/SRM/ArrayDetailsView.aspx?NetObject=SMSA%3a'' + cast(n.StorageArrayID as varchar(255)) + ''>'' + n.UserCaption + ''</a>'' as NodeName
,SUBSTRING(ahv.Message,charindex('':'', ahv.Message)+2,LEN(ahv.Message)) as Issue
,IPAddresses
,DATEADD(HH,-4,TriggeredDateTime) as AlertTime
from AlertActive as asv WITH(NOLOCK)
inner join (select MAX(CAST(AlertRefID as varchar(255)))AlertRefID,MAX(ObjectType)ObjectType,MAX(AlertHistoryID)AlertHistoryID,MAX(RelatedNodeId)RelatedNodeId,MAX(ActionTypeID)ActionTypeID,MAX(EventTypeWord)EventTypeWord,MAX(Message)Message,MAX(EntityCaption)EntityCaption,AlertActiveID from AlertHistoryView where ActionTypeID = ''WriteToNPMEventLog''
and EventTypeWord = ''ActionSucceeded'' Group by AlertActiveID) as ahv on (asv.AlertActiveID = ahv.AlertActiveID)
inner join ' + @srmtable + ' srmt on srmt.' + @newCol + ' = ''' + @objectId + '''
inner join SRM_StorageArrays n on n.StorageArrayID = srmt.StorageArrayID
inner join SRM_StorageArrayCustomProperties srmsac on srmsac.StorageArrayID = n.StorageArrayID
WHERE asv.AlertActiveID = ''' + @curAlert + '''
AND n.Status NOT IN (2,9)';
set @tblParam = N'@retvalOUT nvarchar(255) OUTPUT';
insert into @internalaction exec sp_executesql @tblQuery, @tblParam, @retvalOUT=@srmCol OUTPUT;
drop table #tmp;
end
set @i = @i + 1
end
select * from @internalaction order by AlertTime desc
end
Good View to display all assets on monitoring including whats in SRM
This we use for our integration to our CRM System, this is can have many applications though. You might not need all the casting we did this for the CRM sync side.
First this view relays on a quick function we created to translate the real bytes to mb to read it better i couldn't find a built in function in solarwinds to do it so here is that function first.
create function [dbo].[convertBtoMb] (@BSize as bigint)
returns bigint
as
begin
declare @MBSize bigint
set @MBSize = @BSize/1024/1024
return @MBSize
end
Now this is our view that uses the above function to translate the real bytes as well.
CREATE view [dbo].[SWAssetSyncView] as (
select
cast(CRM_Account as nvarchar(20)) as AccountNumber,
cast('NPM' as nvarchar(3)) as SourceType,
n.NodeID as MachineID,
cast(n.AssetType as varchar(11)) as AssetType,
cast(SUBSTRING(Caption,CHARINDEX('-',Caption)+1,LEN(Caption)) as nvarchar(80)) as ComputerName,
cast(CASE WHEN si.HardwareSerialNumber IS NOT NULL THEN si.HardwareSerialNumber ELSE hi.ServiceTag END as nvarchar(201)) as SystemSerialNumber,
cast(LEFT(ni.MACAddress,2) + '-' + SUBSTRING(ni.MACAddress,3,2) + '-' + SUBSTRING(ni.MACAddress,5,2) + '-' + SUBSTRING(ni.MACAddress,7,2) + '-' + SUBSTRING(ni.MACAddress,9,2) + '-' + SUBSTRING(ni.MACAddress,11,2) as nvarchar(40)) as MACAddress,
cast(SysName as nvarchar(255)) as dnsComputerName,
cast(si.Domain as nvarchar(64)) as Domain,
cast(IP_Address as nvarchar(20)) as IpAddress,
cast(PublicIP as nvarchar(20)) as PublicIP,
cast(ni.Gateway as nvarchar(20)) as DefaultGateway,
cast(CASE WHEN si.Manufacturer IS NOT NULL THEN si.Manufacturer ELSE CASE WHEN hi.Manufacturer IS NOT NULL THEN hi.Manufacturer ELSE n.Vendor END END as nvarchar(256)) as Manufacturer,
cast(CASE WHEN si.Model IS NOT NULL THEN si.Model ELSE hi.Model END as nvarchar(256)) as ProductName,
dbo.convertBtoMb(si.TotalMemoryB) as MemorySize,
cast(si.MemorySlotsCount as varchar(100)) as MemorySlots,
cast(p.Processor as nvarchar(max)) as Processor,
cast(p.ProcessorType as nvarchar(max)) as ProcessorType,
cast(p.ProcessorVersion as nvarchar(max)) as ProcessorVersion,
cast(p.ProcessorSpeed as varchar(24)) as ProcessorSpeed,
cast(n.Vendor as nvarchar(64)) as OSType,
cast(CASE WHEN si.OperatingSystem IS NOT NULL THEN si.OperatingSystem ELSE n.IOSImage END as nvarchar(150)) as OSInfo,
cast(CASE WHEN si.OSVersion IS NOT NULL THEN si.OSVersion ELSE n.IOSVersion END as nvarchar(132)) as ServicePack,
n.LastSync as LastCheckIn,
cast(si.LastLoggedInUser as nvarchar(100)) as LastLoginName,
dbo.convertBtoMb(h.TotalSize) as TotalSize,
h.HardDriveCount as NumberOfDrives,
cast(si.Manufacturer as nvarchar(200)) as WarrantyProvider,
si.WarrantyDate as WarrantyEndDate
from Nodes n
left join AssetInventory_ServerInformationView si WITH(NOLOCK) on n.NodeID = si.NodeID
left join APM_HardwareInfo hi on hi.NodeID = n.NodeID
left join (select Gateway,UPPER(REPLACE(MACAddress,':','')) as MACAddress,IPAddress from AssetInventory_NetworkInterface WITH(NOLOCK)) ni on ni.IPAddress = n.IP_Address
left join (
select MAX(NodeID) as NodeID,MAX(Caption) as Processor,MAX(Model) as ProcessorType, MAX(Stepping) as ProcessorVersion, CAST(MAX(CAST(SpeedMhz as varchar)) + ' MHz' as varchar) as ProcessorSpeed from AssetInventory_ProcessorView WITH(NOLOCK) group by NodeID
) p on n.NodeID = p.NodeID
left join (
select MAX(NodeID) as NodeID,Count(NodeID) as HardDriveCount,SUM(CapacityB) as TotalSize from AssetInventory_HardDrive WITH(NOLOCK) GROUP BY NodeID
) h on h.NodeID = n.NodeID
where ObjectSubType <> 'ICMP'
AND Status <> 11
UNION
select
cast(CRM_Account as nvarchar(20)) as AccountNumber,
cast('SRM' as nvarchar(3)) as SourceType,
srmn.StorageArrayID as MachineID,
cast(srmc.AssetType as varchar(11)) as AssetType,
cast(SUBSTRING(UserCaption,CHARINDEX('-',UserCaption)+1,LEN(UserCaption)) as nvarchar(80)) as ComputerName,
cast(SerialNumber as nvarchar(201)) as SystemSerialNumber,
cast(NULL as nvarchar(40)) AS MACAddress,
cast(Name as nvarchar(64)) as dnsComputerName,
cast(NULL as nvarchar(64)) AS Domain,
cast(IPAddresses as nvarchar(20)) as IpAddress,
cast(NULL as nvarchar(20)) as PublicIP,
cast(NULL as nvarchar(20)) as Gateway,
cast(Manufacturer as nvarchar(256)) as Manufacturer,
cast(Model as nvarchar(256)) as ProductName,
cast(NULL as bigint) as MemorySize,
cast(NULL as varchar(100)) as MemorySlots,
cast(NULL as nvarchar(max)) AS Processor,
cast(NULL as nvarchar(max)) AS ProcessorType,
cast(NULL as nvarchar(max)) AS ProcessorVersion,
cast(NULL as varchar(24)) AS ProcessorSpeed,
cast(Vendor as nvarchar(64)) AS OSType,
cast(Firmware as nvarchar(150)) as OSInfo,
cast(NULL as nvarchar(132)) AS ServicePack,
LastSync as LastCheckIn,
cast(NULL as nvarchar(100)) AS LastLoginName,
dbo.convertBtoMB(CapacityRawTotal) as TotalSize,
Disks as NumberOfDrives,
cast(NULL as nvarchar(200)) as WarrantyProvider,
cast(NULL as datetime) as WarrantyEndDate
from SRM_StorageArrays srmn
inner join SRM_StorageArrayCustomProperties srmc on srmn.StorageArrayID = srmc.StorageArrayID
)
That's all I can muster at the moment while trying to get some work done but I will try to update this as often as possible.