Last Boot Greater Than 35 Days Report

KNW316 1 month 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 1 month 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…

Parents

0 stevenstadel 1 month 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

Reply

0 stevenstadel 1 month 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

Children

No Data