Last Boot Greater Than 35 Days Report

KNW316 7 months ago

Hello,

I was wondering if anyone can help me customize a report for me. I'm not good at SQL or SWQL, so I used co-pilot for the answer. Co-pilot did give me a basic report to start off, but I would like to add custom properties to the report and when I tried, it errors out. I'm looking for a report that shows all nodes where the last reboot was greater than 35 days ago. The report columns should include node name, environment, system name, last boot date and time, machine type, node_owner (custom prop), application_name (custom prop), application_owner (custom), patch_group (custom), and comments (custom). Machine type should not equal "VMware ESX Server." Node_Owner should equal "Server Engineering."

This is what the co-pilot output looks like when you DON'T add any custom properties I mentioned above. This actually works; however, I'd like to AT LEAST add that machine type should not equal "VMware ESX Server" and Node_Owner should equal "Server Engineering.

SELECT
Caption AS [Node Name],
SysName AS [System Name],
LastBoot AS [Last Boot Date and Time],
MachineType AS [Machine Type]
FROM
Nodes
WHERE
DATEDIFF(DAY, LastBoot, GETUTCDATE()) > 35
AND MachineType NOT LIKE '%VMware ESX Server%'
ORDER BY
LastBoot DESC;

Can anyone help me with figuring out how to add the custom properties?

Co-pilot tells me this, but it doesn't work:

SELECT
Caption AS [Node Name],
CustomProperties.Node_Environment AS [Node Environment],
SysName AS [System Name],
CustomProperties.Application_Name AS [Application Name],
CustomProperties.Application_Owner AS [Application Owner],
LastBoot AS [Last Boot Date and Time],
MachineType AS [Machine Type],
CustomProperties.Patch_Group AS [Patch Group],
CustomProperties.Comments AS [Comments]
FROM
Nodes
WHERE
DATEDIFF(DAY, LastBoot, GETUTCDATE()) > 35
AND MachineType NOT LIKE '%VMware ESX Server%'
AND CustomProperties.Node_Owner = 'Server Engineering'
ORDER BY
LastBoot DESC;

Top Replies

stevenstadel 7 months ago +1

For SWQL alias the table (Orion.Nodes N) you are querying and then you can use SWQL Studio to assist in finding the linked properties. SELECT N.Caption AS [Node Name], N.SysName AS [System Name], N…

0 KNW316 7 months ago

I should add that the end goal is to email an alert to "application_owner" whenever a server hasn't rebooted in 35 days. We want our app owners to take responsibility for making sure reboots are happening after monthly updates, so our team doesn't have to manually email them every month.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

0 stevenstadel 7 months ago

For SWQL alias the table (Orion.Nodes N) you are querying and then you can use SWQL Studio to assist in finding the linked properties.

SELECT
    N.Caption AS [Node Name],
    N.SysName AS [System Name],
    N.LastBoot AS [Last Boot Date and Time],
    N.MachineType AS [Machine Type],
    N.CustomProperties.Node_Owner AS [Owner]
FROM
    Orion.Nodes N
WHERE
    DATEDIFF(DAY, LastBoot, GETUTCDATE()) > 35
    AND N.MachineType NOT LIKE '%VMware ESX Server%'
    AND N.CustomProperties.Node_Owner = 'Server Engineering'
ORDER BY
    N.LastBoot DESC;

This is a great article on how linked properties work. - Decreasing JOINs - the Power of Navigation Properties

0 tmcurry 5 months ago

When using Copilot for SWQL, I typically include detailed instructions to ensure the AI recognizes that it’s dealing with SWQL and not SQL. For example, I specify that I need a SolarWinds Orion Platform SWQL query (using the old name) to accomplish specific tasks. I mention the tables, such as Orion.Nodes N) and if there are SWIS joins, I include those as well, like using the built-in SWIC to access custom properties, e.g., N.CustomProperties.Locations.

The key is to clearly indicate that the query pertains to SWQL, which helps the AI engine differentiate it from SQL. This approach has worked well for me when using other AI models like ChatGPT. I have a template that I use, which I'll share when I get a chance.

AI is never 100% accurate, but if it gets you close, you can make the necessary adjustments and input it back into Copilot. Generally, it will get the hint. However, be aware that Copilot often struggles with SWQL date-time fields due to different syntax handling. Keep this in mind when crafting your queries.

With a little back and forth generally you can get there. I will als0 use SWQL studio to test and to help with the "built in joins"

I know this does not answer your specific question however it is a framework f0r better copilot success.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel