SWQL query results not showing under powershell script

Question

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? 
 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!

KMSigma.SWI · Accepted Answer

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.

SWQL query results not showing under powershell script

Top Replies