DotVBS | Windows Systems Management

The following SQL query returns a count of status messages by year, month, day, hour. The dateadd function subtracts 3 days from todays date, giving us counts for the last 3 days. ----Begin SQL---- --Count of Status Messages Broken Down by Year, Month, Day, Hour Select Datepart(yy,time), Datepart(mm,time), Datepart(dd,time), Datepart(hh,time), Count(*) FROM StatusMessages Where Time > dateadd(dd, -3, getdate()) Group By Datepart(yy,time), Datepart(mm,time), Datepart(dd,time), Datepart(hh,time) ----End SQL----

Three simple little queries to show you both the oldest and newest date for your clients hardware and software inventory. ----Begin SQL---- SELECT Min(LastHWScan), Max(LastHWScan) From v_GS_WORKSTATION_STATUS Select Min(LastScanDate), Max(LastScanDate) From v_GS_LastSoftwareScan Select Min(LastCollectedFileScanDate), Max(LastCollectedFileScanDate) From v_GS_LastSoftwareScan ----End SQL----

Some simple SQL to show quick status on heartbeat discovery for all your SMS clients. ----Begin SQL---- select AgentName, DatePart(dd, AgentTime) 'Day', DatePart(hh, AgentTime) 'Hour' , Count(*) 'Count' from v_AgentDiscoveries Where AgentName = 'Heartbeat Discovery' and AgentTime > '2007-06-18' Group by AgentName, DatePart(dd, AgentTime), DatePart(hh, AgentTime) Order By AgentName, DatePart(dd, AgentTime), DatePart(hh, AgentTime) ----End SQL----

A quick and dirty query to show status of your hardware scan for the last few days, add a darepart yy for year and mm for month if you are looking back more than just the current month. ----Begin SQL---- SELECT DatePart(dd,HWSCAN.LastHWScan), DatePart(hh,HWSCAN.LastHWScan), Count(*) From v_GS_WORKSTATION_STATUS HWSCAN Where LastHWScan > '2007-06-01' Group By DatePart(dd,HWSCAN.LastHWScan), DatePart(hh,HWSCAN.LastHWScan) Order by DatePart(dd,HWSCAN.LastHWScan), DatePart(hh,HWSCAN.LastHWScan) ----End SQL----

Looking for SMS 2003 script samples? Check out Microsoft's SMS 2003 Script Repository . Client Scripts show you how to Create a Discovery Data Record, List the Size of the Advanced Client Cache, Retrieve Updated Machine Policy, Retrieve Updated User Policy, Run Available Scripts on SMS Clients, or Start the SMS 2003 Hardware Inventory Cycle. SMS 2003 Site Server scripts show you how to Connect to an SMS Provider, Create a Console Folder, Create an SMS Object, Create an SMS Package and Program, Create an SMS Software Metering Rule, Delete a Console Folder, Delete an SMS Object, List Health Summary for Local SMS Sites, Modify an SMS Object, Move SMS Reports to a Console Folder, Retrieve Object Instance Permissions, Retrieve User Instance Permissions, Update a Package, or View an SMS Collection. These scripts really help automate your day to day SMS 2003 tasks. Many of these are excellent as they are, while others need some customization and tweaking for your environment.