Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need to report Component Details for a Powershell monitor

I have a simple powershell monitor that pulls the active sessions from our Citrix system. What I am needing to do is pull a weekly report that gives the client the min/max/avg stat on a per day basis and in numerical form. The graph that I have figured out how to do really does nothing for you when you create a PDF file and send to client as they are unable to see the true numbers. I am pretty sure this will need to be done from SWQL but I have no knowledge of how to perform these types of queries.

try {
Add-PsSnapIn Citrix.Broker.Admin.V2;
$res=Get-BrokerSession;
}
catch {
Write-Host "Message: $($Error[0])";
exit 1;
}
if ($res -eq $null) {
Write-Host "Message.SessionState: Information not available. Possibly broker is not configured properly or no active sessions.";
Write-Host "Statistic.SessionState: -1";
exit 0;
}
$stat1=0;
$mess1="";
if ($res -is [system.array])
{
for ($i=0; $i -le $res.Count; $i++)
{
if ($res[$i].SessionState -match "Active")
{
$stat1++;
$mess1 += "<br/>";
$mess1 += $res[$i].UserName+" "+$res[$i].ClientName;
$mess1 += ";"
}
}
}
else
{
if ($res.SessionState -match "Active")
{
$stat1++;
$mess1 = $res.UserName+" "+$res.ClientName;
}
}

Write-Host "Statistic.Active: $stat1";

exit 0;

Find more posts tagged with

Accepted answers

kschmalz

I haven't taken a long look at the accuracy of this, but it should provide a good starting point to work from.

In my case this query retrieves the daily min/max/avg for my component called 'Average Match TAT'. This would obviously need to be adjusted if you have duplicate component names, or are returning multiple statistics on a single component.

SELECT DateTrunc('day',CE.TimeStamp) As CollectDay, 
Round(Min(CE.MinStatisticData),2) as MinStat, 
Round(Max(CE.MaxStatisticData),2) as MaxStat, 
Round(Avg(CE.AvgStatisticData),2) as AvgStat
FROM Orion.APM.ChartEvidence2 CE
JOIN Orion.APM.Component as C
ON CE.componentID = C.ComponentID 
WHERE C.Name = 'Average Match TAT'
GROUP BY DateTrunc('day',CE.TimeStamp)
Order by DateTrunc('day',CE.TimeStamp)

CollectDay	MinStat	MaxStat	AvgStat
2020-03-28T00:00:00.0000000	3	7	5.06
2020-03-29T00:00:00.0000000	3	10	6.75
2020-03-30T00:00:00.0000000	1	10	5.8
2020-03-31T00:00:00.0000000	4	8	5.67
2020-04-01T00:00:00.0000000	2	8	5.18
2020-04-02T00:00:00.0000000	2	10	5.68

kschmalz

In that case the Nodes table needs to be joined into the query. You could either return all servers, or define which particular one you want to return, as I identified in the code below.

SELECT DateTrunc('day',CE.TimeStamp) As CollectDay, 
N.DisplayName as NodeName,
Round(Min(CE.MinStatisticData),2) as MinStat, 
Round(Max(CE.MaxStatisticData),2) as MaxStat, 
Round(Avg(CE.AvgStatisticData),2) as AvgStat
FROM Orion.APM.ChartEvidence2 CE
JOIN Orion.APM.Component as C
ON CE.componentID = C.ComponentID 
JOIN Orion.APM.Application as A
ON C.ApplicationID = A.ApplicationID
JOIN Orion.Nodes as N
ON A.NodeID = N.NodeID
WHERE C.Name = 'COMPONENT_NAME'
AND N.DisplayName = 'SERVER_NAME'
GROUP BY DateTrunc('day',CE.TimeStamp), N.DisplayName
Order by DateTrunc('day',CE.TimeStamp), N.DisplayName

All comments

kschmalz

I haven't taken a long look at the accuracy of this, but it should provide a good starting point to work from.

SELECT DateTrunc('day',CE.TimeStamp) As CollectDay, 
Round(Min(CE.MinStatisticData),2) as MinStat, 
Round(Max(CE.MaxStatisticData),2) as MaxStat, 
Round(Avg(CE.AvgStatisticData),2) as AvgStat
FROM Orion.APM.ChartEvidence2 CE
JOIN Orion.APM.Component as C
ON CE.componentID = C.ComponentID 
WHERE C.Name = 'Average Match TAT'
GROUP BY DateTrunc('day',CE.TimeStamp)
Order by DateTrunc('day',CE.TimeStamp)

CollectDay	MinStat	MaxStat	AvgStat
2020-03-28T00:00:00.0000000	3	7	5.06
2020-03-29T00:00:00.0000000	3	10	6.75
2020-03-30T00:00:00.0000000	1	10	5.8
2020-03-31T00:00:00.0000000	4	8	5.67
2020-04-01T00:00:00.0000000	2	8	5.18
2020-04-02T00:00:00.0000000	2	10	5.68

cliffwebb

So is the place I adjust to mine where you have the C.Name = at?

kschmalz

Correct. You would replace that with the name of your component monitoring active sessions.

cliffwebb

So ignore last comment figured that out. What happens or how would I go about it if I have stats that need to be displayed from a specific server but they have the same component name? In other words multiple servers attached to same Monitor but need output separate from each one.

kschmalz

In that case the Nodes table needs to be joined into the query. You could either return all servers, or define which particular one you want to return, as I identified in the code below.

SELECT DateTrunc('day',CE.TimeStamp) As CollectDay, 
N.DisplayName as NodeName,
Round(Min(CE.MinStatisticData),2) as MinStat, 
Round(Max(CE.MaxStatisticData),2) as MaxStat, 
Round(Avg(CE.AvgStatisticData),2) as AvgStat
FROM Orion.APM.ChartEvidence2 CE
JOIN Orion.APM.Component as C
ON CE.componentID = C.ComponentID 
JOIN Orion.APM.Application as A
ON C.ApplicationID = A.ApplicationID
JOIN Orion.Nodes as N
ON A.NodeID = N.NodeID
WHERE C.Name = 'COMPONENT_NAME'
AND N.DisplayName = 'SERVER_NAME'
GROUP BY DateTrunc('day',CE.TimeStamp), N.DisplayName
Order by DateTrunc('day',CE.TimeStamp), N.DisplayName