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.

Is there a way to query/find all the charts that do not contain data?

tigger2 over 9 years ago

Sometimes the views in NPM have charts that indicate "Data is not Available". There is a link to determine "why" this may be occurring, and the help behind the link is somewhat helpful for some cases. We know some of the views have no data because we have not activated something that allows data to be collected, but other times data *should* be collected and it is not. The ultimate issue is we'd like to clean up all the views we are using so data in charts in those views (chartable data, not list data) should *always* be populated and then have a process to able to detect if we have missing data (because there is a misconfiguration somewhere, or our account permissions used to collect data were revoked/changed, etc). It would be nice if a query/queries could be run to flag this condition and we could schedule it to run on a daily basis. I'm not looking to generate alerts/events on something like this.

Has anyone done this or have any input on the swis/SQL tables that might need to be queried? I have not attempted this yet but I imagine one way would be, for NPM/SAM data only

A: To catch missing SAM data

- query the [APM_Component] table for all components that are not disabled, join to [APM_Application] to get the nodeID the application is running on for applications that are not "unmanaged", join against [APM_CurrentComponentStatus] to get ErrorCode and NULL in the "StatisticData" field (i.e. no data is being collected), and join against [Nodes] for all existing nodes that are "up"

B: TO catch general data collection failure on nodes.

- Query [CPULoad_Detail] for unique NodeId's for 24 hours ago and join against [Nodes] for all existing nodes that are "up". Look for any node with no daily data available.

- Do the same query for the [InterfaceAvailability_Detail],[ResponseTime_Detail],[VolumePerformance_Detail] tables.

Other possible options (if anyone has any input):

- Turn up xyz NPM/SAM/etc. logging and search the logs for data collection failures... if this has been done before any info on what logging to turn on and what to look for?

- Monitor Orion in some other way (event logs/perf counters/etc. xyz Thwack pack).

If you have any other suggestions I'm all ears !

Message was edited by: tigger 2 -> CHanged table names from [xyz_Daily] to [xyz_Detail]

0 tigger2 over 9 years ago

Follow up, in case anyone wants it: for Part B above, this may suffice using CPULoad_Detail. It is a SQL query against the NPM database that returns data from the Nodes table that might be useful in tracking down why the node is missing data. Selects "last day" of data, not 24 hours but works if you run it after each device *should have* polled for data already that day.

select nodes.Caption,nodes.ObjectSubType,nodes.IP_Address,nodes.Vendor,nodes.MachineType,nodes.SysObjectID,nodes.Description from [SolarWindsOrion].[dbo].[Nodes] nodes

where

nodes.[ObjectSubType] != 'ICMP' /* ICMP will not provide detailed data */

AND nodes.UnManaged = 0         /* ignore unmanaged devices */

AND nodes.status != 2                  /* Dont select DOWN nodes */

AND (                                            /* These are likely to be able to have CPULoad_Detail data. Make exceptions if change the CPULoad_Detail table below*/

nodes.Vendor like '%Windows%'

OR nodes.Vendor like '%Linux%'

OR nodes.Vendor like '%Unknown%'

OR nodes.Vendor like '%nix%'

)

AND nodes.Caption NOT IN (

/* Get list of node.caption. Select only data points where data exists for today */

SELECT n.Caption

FROM (

    /* get last data point for each device that has a data point */

    select c.NodeId

,max(c.DateTime) as LastDate FROM [SolarWindsOrion].[dbo].[CPULoad_Detail] c

    group by c.NodeId

) cpu

join [SolarWindsOrion].[dbo].[Nodes] n on n.NodeID=cpu.NodeID

where DATEDIFF(DAY, DATEADD(day, -1, cpu.LastDate), GETDATE()) = 1

)

order by nodes.ObjectSubType,nodes.Caption

My SQL and grasp of the NPM database is not the greatest so I'm sure there are much better ways to do this.