Some Things Solarwinds Admins Can't Live Without

Version 6

    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:

     

     

    From a command prompt :

    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

     

     

    select Caption from Nodes

     

    where

      not Caption in (

       SELECT distinct FullName

       FROM ContainerMemberSnapshots

       where EntityDisplayName = 'Node'

    )


    Checking Total Number of Elements for Custom Value


     

    select COUNT (*) from Nodes

     

    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

     

    select IP_Address, Caption from Nodes

     

    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

     

    DECLARE @NodeID int

     

    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


     

    ${SQL:select c. Name 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 } 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


    ss.PNG


    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.