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.

Need help with SQL / custom poller data

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!

  • 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.

  • 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. 

  • 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

  • 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.

  • 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

  • I think the reason the serial number is in a custom property is simply because that's where the custom poller puts it. To me, it's far more useful in the Nodes table, which is where I'm trying to go with that. How do you poll the serial number and place it in the Nodes table?

  • My serial number custom poller does not store the serial number in a custom property.  The fact of the matter is I don't know which table it's in and I have not had a reason to care.

    For a custom poller assigned to a node, you can report on it:

     

    you can alert on it:

     

     

    and you can show it in web pages:

     

     

    ...all of this without a need to know which particular database table is involved.

  • If you want, for whatever reason, to have it show up in a label than it has to come from a custom property.

  • 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 

  • 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?