SWQL query results not showing under powershell script

Hi All,

To identify components that have not been NOT UP for 180 days and delete them from the node level, first I have generated the following SWQL query, which produces the expected result when executed via SWQL Studio:


select
 c.ComponentID
,c.DetailsUrl
,cst.Timestamp
from orion.nodes n
join orion.apm.Application a on a.nodeid=n.nodeid
left join orion.apm.Component c on c.ApplicationID = a.ApplicationID
left join Orion.APM.CurrentComponentStatus cst on cst.ComponentID=c.ComponentID
WHERE daydiff(cst.Timestamp ,getdate())> 180
ORDER by cst.Timestamp ASC

I then incorporated the same code into PowerShell to save the results into a file, so as a next step I can use that data to delete components based on url or ComponentID:

Import-Module SwisPowerShell
 
$OrionServer = 'X.X.X.X'
$Username = 'XXXX'
$Password = 'XXXX'
 
$swis = Connect-Swis -Hostname $OrionServer -Username $Username -Password $Password
 
 
$query = "select
 c.ComponentID
,c.DetailsUrl
,cst.Timestamp
from orion.nodes n
join orion.apm.Application a on a.nodeid=n.nodeid
left join orion.apm.Component c on c.ApplicationID = a.ApplicationID
left join Orion.APM.CurrentComponentStatus cst on cst.ComponentID=c.ComponentID
WHERE daydiff(cst.Timestamp ,getdate())> 180
ORDER by cst.Timestamp ASC"
 
 
$results = Get-SwisData $swis $query
 
$results | Export-CSV -Path D:\Temp\Output.txt -NoTypeInformation

But the result returns an empty output. Any idea what the issue might be with the PowerShell code?Thinking

And another thing, is it possible to use the following command to delete the component from the node level?

Remove-SwisObject $swis -Uri 'swis://localhost/Orion/View.aspx?NetObject=AM:57092'

Thanks!

  • Normally this is permission based.  Are you running SWQL Studio using the same credentials as you are using in the PowerShell Connect-Swis function?

    And yes, if the URI string is formatted correctly (yours above is not), you can use that to delete any element (providing your account has the necessary permissions AND the entity supports CanDelete).

    I rebuilt your query using   Navigation Properties :

    SELECT [Nodes].Caption AS [Node Name]
         , [Nodes].Applications.DisplayName AS [ApplicationName]
         , [Nodes].Applications.Components.ComponentName AS [Component_Name]
         , [Nodes].Applications.Components.DetailsUrl AS [Component_DetailsUrl]
         , [Nodes].Applications.Components.ComponentID AS [ComponentID]
         , [Nodes].Applications.Components.CurrentStatus.Availability AS [Availability]
         , [Nodes].Applications.Components.CurrentStatus.ObservationTimestamp AS [Timestamp]
         , [Nodes].Applications.Components.Uri
    FROM Orion.Nodes AS [Nodes]
    WHERE DAYDIFF([Nodes].Applications.Components.CurrentStatus.ObservationTimestamp, GETUTCDATE()) > 180
    ORDER BY [Nodes].Applications.Components.CurrentStatus.ObservationTimestamp ASC
    

    What you need is the Uri of the element you want to delete.  This is NOT THE SAME as the details URL, though they look quite close.

  • Hi you are correct. I connected the SWQL studio Information Service with the credentials that I used to connect in Connect-Swis function, and It gives an empty result for the query. Thanks for the shared formatted query and the guidelines Handshake tone3

  • That shouldn't be the case.  If you are connecting in the same way, you should get the same results.

    You didn't mention your platform version in the question.  Can you provide that please?

  • SW Version is 2023.4.2 

    Confusion was caused due to logging in to SWQL Studio with the Orion (v3) Certificate option and testing PowerShell with a different account.

    I checked with our admin, and he verified that the account has a SAM limitation.

    I tried this with an account with full access, and it worked perfectly. :)

    Import-Module SwisPowerShell
     
    
    $OrionServer = 'X.X.X.X'
    $Username = 'XXXX'
    $Password = 'XXXX'
     
    $swis = Connect-Swis -Hostname $OrionServer -Username $Username -Password $Password
     
     
    $query = "SELECT 
          [Nodes].Applications.Components.Uri
    FROM Orion.Nodes AS [Nodes]
    WHERE DAYDIFF([Nodes].Applications.Components.CurrentStatus.ObservationTimestamp, GETUTCDATE()) > 180
    
    ORDER BY [Nodes].Applications.Components.CurrentStatus.ObservationTimestamp ASC"
     
     
    $results = Get-SwisData $swis $query
    $results | Export-CSV -Path D:\Temp\Output.txt -NoTypeInformation
     
    
    write-host $results 
    $todelete = $results
    if($todelete) {
    foreach ($results in $todelete){
    Remove-SwisObject $swis -Uri $results }
     
    }

    Thanks!

  • Yeah - this is one of the reasons I never run SWQL Studio on the SolarWinds Server.  It can easily give different results.

    We speak about this in SWQL Studio - SolarWinds Platform API - The Orion Platform - THWACK.