Gather Power Trend Data for Your Systems with Deployment Solution Icon Status
Like many other companies we need to 'go green'. We are exploring options for power management on our desktop systems. In the mean time we want to know where we are today in terms of how many systems are powered on during the day and at night.
I came up with this 'quick and dirty' way to start gathering info. I am using Notification Server to report against the Express (Deployment Solution) database. Then I am using Connector solution in NS to run the report and export the results to a CSV file.
First the SQL query... We run this report where NS and DS are on the same server, if they are on different servers the query would need to be modified.
select
GETDATE() as 'Time',(Select Count(Icon) FROM express.dbo.computer Where Icon <> 0) as 'Count of Systems Powered On',
(Select Count(Icon) FROM express.dbo.computer Where Icon = 0) as 'Count of Systems Powered Off'
FROM
express.dbo.computer COgroup
by CO.UAC
Create a report in NS that runs this query
- In the 6.5 NS console go to 'All Reports'
- Right click on User Defined (If you are familiar with reports put it wherever you would like) and select New-Report
- Give your report a name, description, and choose to Enter SQL directly. Paste the query from above into the SQL Statement area. Then click Finish. At this point you should be able to run your new report.
Now you can create a data source and export rule in the Connector Solution to output the data from this report at regular intervals. I run it hourly.
- In the NS 6.5 console click View-Configuration
- Expand Solution Settings-Connectors-Data Sources
- Right click on Data Sources and choose New-CSV File Data Source
- Type a Name and Description. Choose a delimiter. Check Allow Exports and select Write data to a specified file. Type the UNC where you want your file saved and check to allow it to be created. Click Apply
- Right click on Import/Export Rules and choose New-Report Export Rule.
- Type the Name and Description. Select the Data Source you created. Select the report you created. Enable a schedule to run this report and export it to the CSV in the Data Source.
After this has run for some time you should be able to get a power trend for your systems. If you are like us, every once in a while we need to bounce the Express service, this may cause a spike in your reporting until all of the systems AClients report back in.
GREEEEEEN
This will help companies with getting some good information on how green they are/could be.
Dewayne Cudney
Thats actually a very
Thats actually a very interesting way to look at those icons. Great example of using what's there in a completely new way.
- Matt
Thank you
We have been running a vbs script to gather this information. This is just so much better. Thank you!!
Changed the SQl query
After running this awhile the query above was returning odd results. Here is a query that uses sub-queries in the select statements to get the results. But then I needed a 'Group By', otherwise I ended up with a line returned for each item counted. I did a group by UAC in the computer table. I'm not sure what this column is, but is had the same entry on every entry, 0. Hopefully this works better.
-----------------------------------------------------------------------------------
select
GETDATE() as 'Time',
(
Select Count(Icon) FROM express.dbo.computer Where Icon <> 0) as 'Count of Systems Powered On', (Select Count(Icon) FROM express.dbo.computer Where Icon = 0) as 'Count of Systems Powered Off'
FROM
express.dbo.computer CO
group
by CO.UAC
-----------------------------------------------------------------------------------
Patrick
Sacramento Municipal Utility District
I have edited the query in
I have edited the query in the main article. Hope it takes.
Patrick
Sacramento Municipal Utility District
Now I played around a little
Now I played around a little more with connector solution. Now I have made the data source a table in a new database I created on my SQl server. Now I just run a report against that table to display historical data. I also make to report show in a line graph for quick reference.
Patrick
Sacramento Municipal Utility District
I'm going to test this out on
I'm going to test this out on our stage environment, but it looks much better than the VisualBasic hack/script we run to try to do this same thing. Thanks.
cool
It's working well as long as DS is running good. We have even made a report that makes a line chart and gives you the ability to put in a date range.
Patrick
Sacramento Municipal Utility District
would you share?
Sounds like exactly the report I need, would you mind sharing it?
DS Power status
OK... Like I mentioned I use connector solution to run a report and write the results to a SQL table I created in another DB. So now I have trend data. Now I created a report that I can specify a begining and end date and chose it to display in graph form. I don't see where I can attach a file. I will see if I can in the inbox message so you can see the report.
Patrick
Sacramento Municipal Utility District
Would you like to reply?
Login or Register to post your comment.