Where does PowerShell collected data end up?

Question

Hi Thwackers,

I have a requirement to collect some data with PowerShell, transform it and ship to a customer as a CSV.

The Windows PowerShell monitoring component seems to suit my needs just fine.

However, even though the reporting scheduler does support emailing CSV attachments, I need to transform the data before sending it out every day.

My question is, given that CPU data can be found in the database in intuitively named tables like CPULoad_Hourly_20181106, in whicn tables can the PowerShell statistics data be found?

Thanks

Paul

pnm563 · Answer

I found a report in the legacy Report Writer utility and was able to extract the SQL. This helped me build the following which will do the trick: -

DECLARE @last7Days datetime = getdate() -7

SELECT DateTime,

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

APM_StatisticsUsage.AvgStatisticData,

APM_AlertsAndReportsData.ComponentName,

APM_AlertsAndReportsData.ApplicationName

FROM

Nodes

JOIN APM_AlertsAndReportsData ON Nodes.NodeID = APM_AlertsAndReportsData.NodeId

JOIN APM_StatisticsUsage ON APM_AlertsAndReportsData.ComponentId = APM_StatisticsUsage.ComponentID

WHERE DateTime > @last7Days

AND Nodes.NodeID=1593

AND ApplicationName = 'SQL Powershell Monitor'

ORDER BY DateTime DESC

pnm563 · Answer

Thanks frak

At first glance, it looks like this query would just return the last known application availability. I am looking for the historical statistic data going back as far as possible. Basically, rows of data with a datetime stamp and a value for the statistics at that time.

Do you think this is possible?

Cheers

Paul

frak · Answer

Check this query out - it will show you how to get the next and numbers for a componenet

SELECT
c.Name, dcs.Label
,ISNULL(pe.ErrorMessage, deM.StringData) msg
,ccs.ComponentID as ComponentID
,n.caption Node
,ccs.Availability
,pe.*
FROM dbo.APM_CurrentComponentStatus ccs WITH(NOLOCK)
left JOIN dbo.APM_PortEvidence pe WITH(NOLOCK) on ccs.ComponentStatusID = pe.ComponentStatusID
JOIN dbo.APM_ComponentExt c on (ccs.ComponentID = c.ID and c.ComponentType in (4, 5, 15, 7, 6, 14, 13, 21, 50, 11, 16, 18, 12, 29, 30, 31, 51, 10, 17, 2, 19, 20, 22, 23, 24, 25, 26, 27, 28, 32, 33, 34, 35, 36, 37, 38, 39, 42, 40, 41, 45, 46, 48, 44, 49, 58))
JOIN dbo.APM_Application a on ccs.ApplicationID = a.ID
JOIN dbo.Nodes n WITH(NOLOCK) on a.NodeID = n.NodeID
left join dbo.APM_DynamicEvidenceColumnSchema dcs on dcs.ComponentID = ccs.ComponentID and dcs.Type = 1
left join dbo.APM_DynamicEvidence de WITH(NOLOCK) on de.ComponentStatusID = ccs.ComponentStatusID and de.ColumnSchemaID = dcs.ID
left join dbo.APM_DynamicEvidence deM WITH(NOLOCK) on deM.ComponentStatusID = ccs.ComponentStatusID and deM.ColumnName = dcs.Name and deM.ColumnType = 0
WHERE c.Disabled = 0
--and ccs.Availability <> 1