12 Replies Latest reply on Feb 17, 2012 12:32 PM by rkidder

    Need help with SQL / custom poller data

    rkidder

      I grabbed a custom poller someone wrote which collects the serial number from Cisco devices and stores them in the database. It doesn't, however, store them in the "SerialNumber" column in the "Nodes" table, I'm guessing it's in some other table. 

      Ideally, I'd like to schedule a script to run that does a select on that other table and then sets the "SerialNumber" in the Nodes table.

      I know very basic stuff about SQL and I know less about scripting in Windows so I'm looking for some in-depth help here. 

      Thanks in advance!

        • Re: Need help with SQL / custom poller data
          sotherls

          I would be glad to help as I am doing this already (that might have been my original post you found).

          Let me know what you need.

            • Re: Need help with SQL / custom poller data
              rkidder

              Thanks for the reply.

              I've poked around in the database and figured a few things out. Now I need to tie them together. If this were a MySQL database on a Unix box I could do it in a perl script run out of cron no problem. But being MS-SQL and Windows, I'm at a little bit of a loss.

              The CustomPollerID for the CiscoSerialNumber field the custom poller gathers is 5284505F-DA45-4CDE-A30C-BE5CA41780F9. If I do a select like this:

              select [NodeID],[CustomPollerAssignmentID] from [SolarWindsOrion].[dbo].[CustomPollerAssignment] where [CustomPollerID] = '5284505F-DA45-4CDE-A30C-BE5CA41780F9'

              I get results like so:

              NodeIDCustomPollerAssignmentID
              1599534B4-07C9-4AC8-9BDD-292D0C2F9F37
              270B4BE31-8A22-4D00-A3C7-D50CA10EB2A1
              4EA6FF157-F97A-4141-B853-4222061D7732
              5B8F73272-5130-48C4-9BD8-EE9846AF9571
              688B5BA0B-F7C1-4237-87BF-C5FB57006FE5
              72CCB908A-2AE7-4576-9A1B-19F9945859F3

              I can then take each CustomPollerAssignmentID and do another select like this: select [Status] from [SolarWindsOrion].[dbo].[CustomPollerStatus] where [CustomPollerAssignmentID] = '599534B4-07C9-4AC8-9BDD-292D0C2F9F37'

              That returns the CiscoSerialNumber (the field the custom poller created). Then, given the NodeID and the serial number, I could do an update (pseudocode) that looks like this:

              update [SolarWindsOrion].[dbo].[Nodes] set [SerialNumber] = 'FTX00000000' where NodeID == '1'

              Now if I could loop through that second query (putting it in a hash) and do my third query and update, that would be great. 

                • Re: Need help with SQL / custom poller data
                  sotherls

                  Ok, here is how I am doing this....

                  I have a SQL Server Agent job called 'Read Cisco Serial Numbers and Push to CP's' and it contains this query.

                  UPDATE dbo.nodes
                  set SerialNumber = t.status
                  from (
                  select distinct caption, status
                  from (
                  select caption, a.Status
                  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 'chassisid%')
                  ) subquery
                  ) as T, dbo.Nodes as T1
                  where t.caption = t1.caption
                  ------ 

                  Verify your assignmentname and if different than above change it. To see if it is going to work run this subset query (doesn't update just reads the data):

                   select distinct caption, status
                  from (
                  select caption, a.Status
                  from dbo.CustomPollerStatus A
                  join dbo.CustomPollerAssignment B
                  on b.CustomPollerAssignmentID = a.CustomPollerAssignmentID
                  join dbo.nodes C
                  on b.nodeid = c.nodeid
                  where (c.mfg like 'cisco%') and ( b.assignmentname like 'chassisid%')
                  ) subquery
                  -----

                  If you need help with setting up a SQL Server Agent job let me know

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  UPDATE

                   

                  dbo.

                  nodes

                   

                  set

                   

                   

                  Model = t.

                   

                  status

                   

                  from

                   

                   

                   

                   

                  (

                   

                  select

                   

                   

                  distinct caption,

                   

                  status

                   

                  from

                   

                   

                  (

                   

                   

                   

                  select

                   

                   

                  caption, a.

                   

                  Status

                   

                  from

                   

                   

                  dbo.

                   

                  CustomPollerStatus A

                   

                  join

                   

                   

                  dbo.

                   

                  CustomPollerAssignment B

                   

                  on

                   

                   

                  b.CustomPollerAssignmentID = a.

                   

                  CustomPollerAssignmentID

                   

                  join

                   

                   

                  dbo.

                   

                  nodes C

                   

                  on

                   

                   

                  b.nodeid = c.

                   

                  nodeid

                   

                  where

                   

                   

                  (c.mfg like 'cisco%') and ((b.assignmentname like 'chassismodel%')

                   

                   

                   

                  or

                   

                   

                  b.assignmentname like '%modelnumber%'

                   

                  )

                  )

                   

                   

                  subquery

                   

                  )

                   

                   

                  as T, dbo.Nodes as

                   

                  T1

                   

                  where

                   

                   

                  t.caption = t1.

                   

                  caption

                    • Re: Need help with SQL / custom poller data
                      rkidder

                      If I modify the where clause in your query so like so:

                       

                      ..
                      where ( b.assignmentname like 'CiscoSerialNumber%')
                      ..

                      it looks like it gets me what I'm looking for. Instead of doing the update based on the caption field, would it be better to do it on the Node ID as that's the key between the tables? Or am I misinterpreting that? And yes, help with setting up an SQL Agent job would be welcome.

                      Thanks,

                      Roy

                        • Re: Need help with SQL / custom poller data
                          sotherls

                          Roy,

                          Yes you can change it to match on the nodeid, it shouldn't pose a problem. In fact I chnaged the main part of the query to reflect this and compared it to the original and it returned the same records, data.

                          select nodeid, status
                          from (
                          select b.nodeid, a.Status
                          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 'chassisid%')
                          ) subquery 

                            • Re: Need help with SQL / custom poller data
                              rkidder

                              So the following update seems to do exactly what I'm looking for:

                              UPDATE dbo.nodes
                              set SerialNumber = t.status
                              from (
                              select distinct caption, status
                              from (
                              select caption, a.Status
                              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 'CiscoSerialNumber%')
                              ) subquery
                              ) as T, dbo.Nodes as T1
                              where t.caption = t1.caption

                              Thanks for the help on that, I appreciate it. Next is to automate it. You mentioned a SQL Server Agent job. How do I do that?

                                • Re: Need help with SQL / custom poller data
                                  sotherls

                                  Using SQL Server Management Studio log into your database.

                                  Expand your database and look at the bottom where you should see 'Sql Server Agent'.

                                  Expand that then expand Jobs

                                  Right click and click on 'New Job'

                                  Type in a meaningful name and description then click on Steps. Click on New and give it a name.

                                  Chaneg the Database to point to your database and then paste your query in the box and verify by clicking on the 'Parse' button.

                                  Click on Schedules and then New. Give it a name (yeah, I know, another name...) Configure how often you want it to run (I run mine daily at 00:05) then OK.

                                  Click OK

                                  To test to make sure it will run Right click on the job and click Start Job and see if it runs.

                      • Re: Need help with SQL / custom poller data
                        d09h

                        What is the benefit of the serial number being in a custom property?  I poll it and display it on Node Details pages as well as a report I wrote, but I'd be curious what's driving these efforts to make a custom property out of it?

                        I'm trying to see if this kind of thing would be useful in my environment.