9 Replies Latest reply on Aug 12, 2013 8:07 AM by mharvey

    Report for historcal node count

    bpeyton17

      Does anybody have a custom query or report that they have built that will show a historical trend in the number of nodes monitored by orion over an extended period of time. I have been asked to show trend in the number of nodes monitored over the past 6 months, and cannot figure out how/ where to pull that data?

        • Re: Report for historcal node count
          mharvey

          Is there any statistic you are trying to trend out like number of nodes added over the past 6 months?  Or something specific you are monitoring from them?

            • Re: Report for historcal node count
              bpeyton17

              I Just need to see the number of devices that were monitored in June vs July vs August etc... I attempted to filter the message center for "node added" but that did not yield the results I was looking for

                • Re: Report for historcal node count
                  mharvey

                  Let me see what I can put together.  Since there is no Date Added column in the nodes table, this may not be completely possible, but I'll see what I can put together.

                    • Re: Report for historcal node count
                      mharvey

                      Working with some of my colleagues we were able to come up with this.  This goes as far back as August.  Now, please note, this will only work for as long as you've been on 10.4 as I believe that's when the auditing events for node added were created.

                       

                      Select

                      Count(*) AS Now_Value,

                      (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                      Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '12/1/2012' AND '12/31/2012') ) AS December,

                      (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                      Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '11/1/2012' AND '11/28/2012') ) AS November,

                      (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                      Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '10/1/2012' AND '10/31/2012') ) AS October,

                      (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                      Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '9/1/2012' AND '9/30/2012') ) AS September,

                      Count(*) - (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                      Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '8/1/2012' AND '8/31/2012') ) AS August

                       

                       

                      From Nodes

                       

                      Just copy the SQL and paste it into a Custom SQL report in the Report Writer.

                       

                      Regards,

                      Matthew Harvey

                        • Re: Report for historcal node count
                          mharvey

                          Correction, it needs to be:

                           

                          Select

                          Count(*) AS Now_Value,

                          (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                          Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '12/1/2012' AND '12/31/2012') ) AS December,

                          (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                          Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '11/1/2012' AND '11/28/2012') ) AS November,

                          (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                          Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '10/1/2012' AND '10/31/2012') ) AS October,

                          (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                          Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '9/1/2012' AND '9/30/2012') ) AS September,

                          Count(*) - (Select COUNT(*) As Monthly_Addition from Nodes Join AuditingEvents on Nodes.NodeID = AuditingEvents.NetworkNode

                          Where AuditingEvents.NetworkNode IN (Select COUNT(*) from AuditingEvents where ActionTypeID = 24 AND TimeloggedUtc between '10/1/2012' AND '10/31/2012') ) AS August

                           

                           

                          From Nodes

                            • Re: Report for historcal node count
                              zzz

                              Since I don't think many people were on 10.4 in August, one workaround is to see what are the datetimes of the first recorded entry in the nodetable

                               

                              Below is a example for NodeID X to Y. The restriciton isn't really needed as it shouldn't take too long to run this even for the entire databse... but still I just did it to be safe.

                              Creating one to see only the nodes for month X will be somewhat more difficult. Easiest would be to create the below as a temp table A, then use Select * from A where dateadded is date to date.

                               

                              EDIT: Oh this will only be accurate as the data in the NodeTable. So depending on retention times, it'll probably only be accurate to daily, but as data by default is deleted if it older than a year- nodes older than that will show them being 1 year old.

                               

                              SELECT

                              Nodes.Caption AS NodeName,
                              MIN(DateTime) AS DateAdded

                              FROM
                              Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


                              WHERE
                              Nodes.NodeID >= X
                              AND
                              Nodes.NodeID <= Y

                               

                              Group By
                              Nodes.Caption, Nodes.NodeID

                               

                              Order By
                              Nodes.NodeID DESC

                    • Re: Report for historcal node count
                      jljeff

                      Hello,

                       

                      This report looks like it could help me also...but I need a way to filter it to only windows nodes...can someone help me w/ the above SQL to do this?  And also to change the range from 2012 to be from January 2013 to Aug 2013?  I'm trying to plot our "server sprawl" starting in Jan 2013.

                       

                      Thanks!!


                      Jeff

                       

                      EDIT - I updated the date ranges. but I'm still getting 0s for al the values except for the now value and the last month in the chain value...I also figured out if theres aw ay to filter it by vendor and restrict it to windows, that would fix that as well...but I'm not sure why I'm getting the 0s.  Hoping someone can help me out...

                       

                      Thanks,

                      Jeff

                      • Re: Report for historcal node count
                        rgward

                        Having this feature would make this report a piece of cake.  Please vote, if you haven't already!