If anyone can optimize, great! I’m still learning, so any input is appreciated. For information/help - rcariddi@gmail.com

Thank you,
Rich

Hi @cariddir . Your query looks great! 

I only have two minor things to suggest.

First, in the definition of ApprovedSW, you can use a handy built-in function called join(separator, list) to comma-separate the items. Specifically, you can replace that line with this:

let ApprovedSW = join(", ", "entry.SW_N, entry.SW_N1, entry.SW_N2])

Second, in the definition of “OS Compliance” column, you can avoid the series of if-else statements by using the “in” membership test. Specifically, you could write that column like this:

  "OS Compliance": if Current in Centry.SW_N, entry.SW_N1, entry.SW_N2]
                   then "good"
                   else "fail",

Finally, I can imagine that it might be hard to keep the LDoS data you have up-to-date and to ensure its quality. You could consider writing one or more queries that run some quality checks on them. For example, one that comes to mind is to find models of devices in your network where the model is not found in your LDoS dataset. You could do that with this query:

supportedModels =
  foreach entry in deviceSupportDetail
  select distinct entry.Model;

foreach device in network.devices
let platform = device.platform
where isPresent(platform.model) && platform.model not in supportedModels
select distinct { Vendor: platform.vendor, Model: platform.model }

Then you can review this list and see if it is as you’d expect. That is, these are unsupported devices. 

You could go a step further and add these models to your LDoS dataset or mark them as “known-unsupported” models, so that the query only outputs rows for models that are unexpected to you. In other words you could do something like:

knownUnsupportedModels = U"WS-C3650-48TS-L", "ASR1002-HX"];

supportedModels =
  foreach entry in deviceSupportDetail
  select distinct entry.Model;

foreach device in network.devices
let platform = device.platform
where isPresent(platform.model) 
      && platform.model not in supportedModels 
      && platform.model not in knownUnsupportedModels
select distinct { 
  Vendor: platform.vendor, 
  Model: platform.model 
}

Since that would only output rows if your dataset is incomplete or bad, you could enable this as an NQE Verification and have the platform notify you when it outputs any rows.

Andreas,

Thank you for the input! I’ve Implemented both suggestions, and this is GREAT. Yes, it was really painful to keep track of that original file and find devices that were not listed in the LDoS data. I would have to do 2 separate queries, export and ‘xlookup’ the heck out of it, in Excel to find the discrepancies, now I can track MUCH easier.

Sincere thanks!
Rich