Custom SQL Query, Filter List, Based On 2 Different Custom Poller Field Values

why not define both of your queries as CTE's and then do a JOIN on IP Address? Do you get a crazy result?

dhanson‌

To put this as simple, and blunt, as possible...

I'm a big, stupid, dumb-dumb-head...

I have about as much SQL knowledge in my head, as my little girl has in her diaper...

I do my best to learn what I can, unfortunately, my best is simply not good enough.

Can you provide a bit more detail, and/or perhaps an example?

I am fairly decent at taking pieces of something and altering it to work for our needs, so if you could just provide even a super simplistic example, and can take it from there and try.

In the meantime, I will read up on "defining queries as CTE's", and see what I can learn.

Thank you,

-Will

So a CTE is a "Common Table Expression", which I typically use when I can isolate specific information from a large table and use it in the context of a different query. In the example below, "WITH a AS (query)" is defining a CTE from the results of your specific queries.

So let's look at it from your angle: you have 2 queries that have shared objects...both have "Caption" (the device hostname) and "IP_Address" (Device IP's). With these, we can do a JOIN.

So now the problem becomes "how is SQL going to mash this stuff together?"

Because of the likelihood that this is going to produce a Cartesian product (basically a row for every possible combination of results) this could get messy and may require some tweaking on your part, but I'll at least help you get started. =)

WITH a AS

(

SELECT * 

FROM 

( 

    SELECT n.NodeID, n.Caption, n.IP_Address, cs.RowID, cs.Status 

       FROM CustomPollerStatus cs JOIN CustomPollerAssignment ca ON (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID) 

                                                      JOIN CustomPollerS cp ON (ca.CustomPollerID=cp.CustomPollerID) 

                                                      JOIN Nodes n on (ca.NodeID=n.NodeID) 

   WHERE cp.UniqueName='Occam_Firmware_Version'  -- use your custom poller name 

      AND n.Vendor LIKE '%occam%' 

      /*AND n.Caption='node_name'*/ )  --here you can specify the node 

AS SourceTable 

PIVOT 

( 

MAX(Status) 

FOR RowID IN ([9.0],[13.0]) -- change the numbers if your polled RowIDs have different values 

) AS PivotTable 

ORDER BY '9.0', '13.0', 'Caption' ASC

)

,

b AS

(

SELECT DISTINCT t1.[NodeID],t1.[IP_Address],t1.[Caption] 

FROM [SWNPMDB].[dbo].[Nodes] as t1 

JOIN [SWNPMDB].[dbo].[Volumes] as t2 

ON t1.[NodeID]=t2.[NodeID] 

WHERE t1.Testing<>'TRUE' 

ORDER BY t1.[Caption] ASC

)

SELECT a.*

FROM a, b

WHERE a.IP_Address = b.IP_Address

-- So this final query should pull all the results from a where there is a shared attribute (IP_Address) between the two.

I'm sure you can tweak and play with this to where it needs to be.

HTH!

Almost forgot: if this doesn't work out of the package, make the CTE queries (your two queries) add "TOP 1000" or some other extremely high value to your SELECT statements, and it should work.

dhanson‌

Thank you very much for the information provided.

I have only spent a small amount of time working through your suggestion, so far, however, I have not had much success yet.

At first, the query failed, as you stated it might.  So, I made the recommended changes, and I was able to get the query to process, although, with 0 rows as the result.

I will continue to work through this, and report back my results.

Thank you, again, for your assistance.

-Will

I'd play with the final query as much as you can. Since I don't have anything similar (nor can I set up something similar) in my environment, it's difficult for me to diagnose what could be going wrong with the query. I really wish I could, because this sounds like a fun request. =/

I'd start with:

1. simply selecting all the results from your queries using "SELECT * FROM a" and "SELECT * FROM b" to make sure that the two CTE's are returning the tables you want.

2. Work with the select statement to see if you can get at least a Cartesian product from the two CTE's joined. Something like what I posted above, but you can do something like..."SELECT * FROM a,b" to make sure you can combine all the results.

3. Play with WHERE attributes to narrow down the results you really want, and work on JOIN's to flex it into the results you need.

REMEMBER: you should no longer be working out of your original tables, as now you're dealing with "a" and "b".

Also, since your request was two queries, the "WHERE" statements you add to the end result should be used to filter on the software/revision you want.

dhanson‌

I have not forgotten about this, however, I have had a few family emergencies to deal with over the past week, and have not touched this since my previous reply.

I am working on some upgrades right now, but afterwards, I will jump back into this and, of course, report back with my findings.

Thanks again for you help.

-Will

Well, dhanson‌, I think I finally accomplished my goal here...

I took your advice, the query example you provided, a bunch of time, and a ton of research... and I was able to come up with the following.

--[SWNPMDB].[dbo].[CustomPollers]
--     [UniqueName]
--          entitySoftwareRev
--          entitySoftwareRevExt
WITH a AS
(
SELECT nd.[NodeID],nd.[Caption],nd.[IP_Address],cps.[RowID],cps.[Status]
FROM [SWNPMDB].[dbo].[CustomPollerStatus] cps
  JOIN [SWNPMDB].[dbo].[CustomPollerAssignment] cpa
  ON (cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID)
  JOIN [SWNPMDB].[dbo].[CustomPollers] cp
  ON (cpa.CustomPollerID=cp.CustomPollerID)
  JOIN [SWNPMDB].[dbo].[NodesData] nd
  ON (cpa.NodeID=nd.NodeID)
  JOIN [SWNPMDB].[dbo].[NodesCustomProperties] ncp
  ON nd.[NodeID]=ncp.[NodeID]
WHERE cp.[UniqueName]='entitySoftwareRev'
)
,
b AS
(
SELECT nd.[NodeID],nd.[Caption],nd.[IP_Address],cps.[RowID],cps.[Status]
FROM [SWNPMDB].[dbo].[CustomPollerStatus] cps
  JOIN [SWNPMDB].[dbo].[CustomPollerAssignment] cpa
  ON (cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID)
  JOIN [SWNPMDB].[dbo].[CustomPollers] cp
  ON (cpa.CustomPollerID=cp.CustomPollerID)
  JOIN [SWNPMDB].[dbo].[NodesData] nd
  ON (cpa.NodeID=nd.NodeID)
  JOIN [SWNPMDB].[dbo].[NodesCustomProperties] ncp
  ON nd.[NodeID]=ncp.[NodeID]
WHERE cp.[UniqueName]='entitySoftwareRevExt'
)
,
c AS
(
SELECT DISTINCT nd.[NodeID],nd.[IP_Address],nd.[Caption]
  FROM [SWNPMDB].[dbo].[NodesData] AS nd
  JOIN [SWNPMDB].[dbo].[Volumes] AS v
  ON nd.[NodeID]=v.[NodeID]
  JOIN [SWNPMDB].[dbo].[NodesCustomProperties] AS ncp
  ON nd.[NodeID]=ncp.[NodeID]
  JOIN [SWNPMDB].[dbo].[CustomPollerAssignment] AS cpa
  ON (cpa.NodeID=nd.NodeID)
  JOIN [SWNPMDB].[dbo].[CustomPollerStatus] AS cps
  ON (cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID)
)
--SELECT a.[Caption],a.[IP_Address],a.[Status] as Firmware,b.[Status] as Rev
SELECT a.[Caption],a.[IP_Address], (CASE WHEN (a.[Status]='7.3') AND (b.[Status]='20.0') THEN 'SKIP' ELSE ((a.[Status] +'_'+ b.[Status])) END) as Firmware
FROM a,b,c
WHERE a.[NodeID]=b.[NodeID]
  AND a.[NodeID]=c.[NodeID]
-- AND ((a.[Status]='7.3') AND (b.[Status]<>'20.0'))
ORDER BY Firmware ASC

I ended up just going with 2 completely separate UnDPs, each pulling a piece of the data I needed. (Instead of pulling the whole table, and then filtering out the data I did not need, as I had previously done)

I am now able to adjust this query to meet my needs.

I am not sure, as to the efficiency of this query, however, I feel it is perfectly fine for me, as it provides the data I need.

Thank you very much for your assistance with this task.

-Will