5 Replies Latest reply on May 4, 2009 2:51 PM by jnathlich

    Stored Procedue to populate custom property

      Does anyone have any experience using a stored procedure on their SQL server to populate a custom property on a recurring schedule?

      For example, I want to create a custom property with a floating value that contains the delta of the volume space used over the past hour.  I want to populate this value every 60 minutes using a stored procedure which calcluates this delta.

      I have the SQL query for this and I do it outside of Orion in a static report.  I would like to move it into a custom property so I can alert based on this value.

      Thanks

        • Re: Stored Procedue to populate custom property
          sotherls

          pgraham,

          I have been doing this in my SQL for some time and it works great. I configured a SQL Agent job to run on a regualr basis, actually have several running at different times for various CP's.

          It can be done, just let me know what you need to know to make it work.

            • Re: Stored Procedue to populate custom property

              Thanks for the reply.  I was wondering if anyone had tried and hit any issues.  Sounds like I can give it a try then.

              • Re: Stored Procedue to populate custom property

                Sotheris,

                Any chance you can post that SQL agent query?  I'm going through the 'get UnDP data onto a map' scenario. I'm using a UnDP to query the amperage from a PDU, so would like to populate a custom property in the nodes table for my PDU's.  I think it would run every 5 minutes, but there's only 30 or so PDU's so I don't think it'll stress the system much.

                My sql is, uh.. adequate, but at the moment the table structure of the Orion DB's looks a little daunting.  Hmm.. maybe it's just a coffee issue.

                  • Re: Stored Procedue to populate custom property
                    sotherls

                    More than happy to help....

                    If it is for updating from a custom property this is one I use to push the current temperature from our APC environmental units to a custom property.

                    UPDATE dbo.nodes
                    set temp = tempf,
                    TempTime = LastPolled
                    from (
                    select distinct caption, TempF, dateadd(hour, -5, datetime) as 'LastPolled'
                    from (
                    select c.caption, round(cast(a.status as varchar),0) as Tempf, a.datetime
                    from dbo.CustomPollerStatus A
                    join dbo.CustomPollerAssignment B
                    on b.CustomPollerAssignmentID = a.CustomPollerAssignmentID
                    join dbo.nodes C
                    on b.nodeid = c.nodeid
                    where b.AssignmentName like '%APCEM_temp%'
                    ) subquery
                    ) as T, dbo.Nodes as T1
                    where t.caption = t1.caption

                    See if this makes sense.....