Management is asking for a report with the following items. I am able to get each item at a point in time, but I need an automated process to allow me to trend over time and pull historical data.
TotalServers - (I get this out of Orion by doing a count(distinct caption) on Nodes
New Servers - (I get this out of Orion by using the following query:
count(n.Caption) [NodeName]
FROM dbo.Nodes n
INNER JOIN dbo.Events e
INNER JOIN dbo.EventTypes et
ON e.EventType = et.EventType
ON n.NodeID = e.NetworkNode
et.Name = 'node added' AND
e.EventTime >= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0) AND
e.EventTime < DATEADD(MONTH,datediff(MONTH,0,getdate()),0)
DecomServers (This is done by using the following formula:
(LastMonthTotal + LastMonthNew – ThisMonthTotal) = Decoms
So, I'm looking for suggestions / help on how I should get this done.
1. I'm thinking a new table with a stored proc to calculate the data and put it in the 1st of each month. To avoid possibly breaking SLA, I have a new blank database created that I can put the table in. But I don't know where to begin for the stored proc. (I am not a SQL guy. Just a server admin trying to make management happy)
2. If anyone has a better solution on how to do this, I would be greatly appreciative.
Ultimately, what I want is, the 1st of each month
- Tell me how many TotalServers. Add it to a row for the current month
- Calculate the new servers for the previous month. Add it to last month's row
- Calculate the decoms for the previous month. Add it to last month's row
