SMS v1.2 for Asset Management Reporting

waxspadeΔιαχείριση

18 Νοε 2013 (πριν από 3 χρόνια και 6 μήνες)

64 εμφανίσεις





SMS v1.2 for Asset Management
Reporting

A Continued Series in Developing Intranet ASP
for Systems Management Server Reporting.

Larry A. Duncan, MCP

Principle Partner, PDC Group






Asset Management

Join us as we revisit the custom MIF and SQL view
s.

The saga continues

In my last whitepaper titled
Improved SQL Queries for SMS v1.2

I outlined the
functionality of using the SMS SQL View Generator to provide more accurate, weighted
results from more complex SQL queries. In this paper, I will take this

measure one step
further by putting these complex queries into action.


Our demonstrations will consist of how to use the information gathered from a custom
management information form (MIF) to provide asset management reporting. In this
paper, the resul
ts will be a standard SQL query result. However, as noted previously, this
is leading to our closing paper


Intranet ASP Development for SMS Reporting.


Background
:

It is assumed that you have already developed a means of creating and deploying your
cust
om MIF form and have populated your SQL database from the data gathered. In
addition, you must have already created your views using the SMS SQL View Generator.
For consistency, we would prefer that you have created your views using the updated
version ava
ilable from Microsoft, though it is not required. Alternatively, you can download
the updated View Generator from
www.pdcgroup.com
.


Scenario
:

Your corporation consists of 20,000 desktop users scattered though 10 sta
tes and one
foreign country. Because of the scale of the company’s operations it is often times difficult
to maintain an accurate database of employee information as it is related to asset
management. A special project team has been convened to resolve thi
s matter and
provide accurate, timely reports to upper management in regards to the following:

1. Proof of Delivery (Vendor Based)

2. User First Name, Middle Initial and Last Name

3. User Street Address, City, State and Zip

4. User Mail Code

5. User Respo
nsibility Center (RC) Code

6. User Department

7. User Supervisor

8. PC Responsibility Center (RC) Code

9. PC Make, Model and Serial Number

10. Monitor Make, Model/Size, and Serial Number

11. Local Printer Make, Model and Serial Number

12. Any applicable s
oftware asset information

13. Asset Tag information from all PC based equipment

14. Technical Coordinator contact information

Chapter

1



2


These reports will serve the following purposes:

(Note: Each will be followed by the line item listed above that provides the inf
ormation)




Allow Accounts Payable personnel to verify vendor invoices for machine installations. (1, 9, 10, 11)



Permit accounting personnel the ability to accurately bill departments for PC rental and network
support fees and maintain asset records for all

related equipment. (5, 6, 7, 8, 9, 10, 11, 12, 13)



Provide department managers details on machine installation base to determine cost and
complexity of system upgrades. (6, 7, 9, 10, 11, 13)



Allow Technical Coordinators to view information as it relates t
o machines for which they are
accountable. (14)



Provide Human Resources an alternate means of storing/verifying employee data. (2, 3, 4, 6, 7)



Accurately report the number of PC per state for property tax reporting purposes.


It was also decided to use a
DMI compliant product for obtaining the hardware specific information
that SMS is not able to

gather
. Therefore, item 9 will be obtained from an alternate source, though it
will still be reported to SMS. An example of this is the IBMBIOS.EXE available from
http://www.pdcgroup.com
. IBMBIOS.EXE reports a
variety of information about IBM based machines
including BIOS revisions, serial numbers, make and model inf
ormation. The result is a
*
DMTF

compliant MIF form being writte
n to the ..
\
MS
\
SMS
\
NOIDMIFS folder.


*The Desktop Management Task Force (DMTF) is the industry consortium chartered with development, support and maintenance
of management standards for PC systems and products, including DMI and CIM.


As you can
see,

your t
ask is a complex one that will effect a great deal of people and departments. You
can rest assured that as the word spreads what you are now able to provide that others will come forth
with their request as well. Therefore, it is paramount that you make yo
ur code as reusable as possible
take the necessary steps to minimize the amount of administrative overhead involved.



Course of Action:


Having assembled your custom MIF and deployed it to all users, you have begun seeing the SQL table
populate themselves
. For the purpose of illustration, we will use the following MIF form:


Start Component


Name = "Machine"


Start Group


Name = "PowerMIF Data"


ID = 1


Class = "PowerMIF|PowerMIF Data|1.0"


Start Attribute


Name = "First Name"


ID =

1


Type = String(25)


Storage = Specific


Value = "Larry"


End Attribute


Start Attribute


Name = "Middle Initial"


ID = 2


Type = String(1)


Storage = Specific


Value = "A"



3


End Attribute


Start Attribu
te


Name = "Last Name"


ID = 3


Type = String(25)


Storage = Specific


Value = "Duncan"


End Attribute


Start Attribute


Name = "Address Line 1"


ID = 4


Type = String(50)


Storage = Specific


Value =

"3938 Avera Ave"


End Attribute


Start Attribute


Name = "Address Line 2"


ID = 5


Type = String(50)


Storage = Specific


Value = ""


End Attribute


Start Attribute


Name = "City"


ID = 6


Type = String(
25)


Storage = Specific


Value = "Winston
-
Salem"


End Attribute


Start Attribute


Name = "State"


ID = 7


Type = String(2)


Storage = Specific


Value = "NC"


End Attribute


Start Attribute


Name = "Zip Co
de"


ID = 8


Type = String(10)


Storage = Specific


Value = "27106"


End Attribute


Start Attribute


Name = "Phone"


ID = 9


Type = String(14)


Storage = Specific


Value = "(336) 813
-
6125"


End Attribut
e


Start Attribute


Name = "Mail Code"


ID = 10



4


Type = String(10)


Storage = Specific


Value = "NC
-
33019"


End Attribute


Start Attribute


Name = "User RC Code"


ID = 11


Type = String(5)


Storage = Sp
ecific


Value = "54321"


End Attribute


Start Attribute


Name = "Computer RC Code"


ID = 12


Type = String(5)


Storage = Specific


Value = "12345"


End Attribute


Start Attribute


Name = "Department"


ID
= 13


Type = String(30)


Storage = Specific


Value = "Information Technology"


End Attribute


Start Attribute


Name = "Supervisor"


ID = 14


Type = String(30)


Storage = Specific


Value = "Bill Gates"


End A
ttribute


Start Attribute


Name = "PC Asset Tag"


ID = 15


Type = String(10)


Storage = Specific


Value = "123456"


End Attribute


Start Attribute


Name = "Monitor Asset Tag"


ID = 16


Type = String(10)



Storage = Specific


Value = "654321"


End Attribute


Start Attribute


Name = "Monitor Model"


ID = 17


Type = String(10)


Storage = Specific


Value = "V50"


End Attribute



5


Start Attribute


Name = "Monitor Si
ze"


ID = 18


Type = String(2)


Storage = Specific


Value = "15"


End Attribute


Start Attribute


Name = "Printer Asset Tag"


ID = 19


Type = String(10)


Storage = Specific


Value = "HP8520"


End Attrib
ute


Start Attribute


Name = "Printer Make"


ID = 20


Type = String(25)


Storage = Specific


Value = "Hewlett Packard"


End Attribute


Start Attribute


Name = "Printer Model"


ID = 21


Type = String(25)



Storage = Specific


Value = "LasetJet 5Plus"


End Attribute


Start Attribute


Name = "Printer Serial"


ID = 22


Type = String(15)


Storage = Specific


Value = "1234567890"


End Attribute


Start Attribute


Na
me = "Software Asset Tag"


ID = 23


Type = String(10)


Storage = Specific


Value = "N/A"


End Attribute


Start Attribute


Name = "TC First Name"


ID = 24


Type = String(25)


Storage = Specific


Value = "Da
na"


End Attribute


Start Attribute


Name = "TC Last Name"


ID = 25


Type = String(25)



6


Storage = Specific


Value = "Duncan"


End Attribute


Start Attribute


Name = "TC Phone"


ID = 26


Type = String(14)


Storage = Specific


Value = "(336) 813
-
6136"


End Attribute


End Group

End Component


Obviously, hand creating a custom MIF is not an option. Microsoft does offer a custom MIF generator
(MIFGEN.EXE) as well a PDC Group (PowerMIF). Both are a
dequate for the task, though we are partial
to PowerMIF, of course.


Once the first user completes the MIF form and
performs an SMS
inventor
y
, the MIF will be sent to the
SMS server for processing. Consequently, the following table and rows will be created in SMS:


Tab
le:

PowerMIF_COMM

PowerMIF_SPEC

Column:

PowerMIF_SPEC

First_Name0

Middle_Initial0

Last_Name0

Address_Line_10

Address_Line_20

City0

State0

Zip_Code0

Phone0

Mail_Code0

User_RC_Code0

Computer_RC_Code0

Department0

Manager0

PC_Asset_Tag0

Monitor_Asset_Tag0

Mon
itor_Model0

Monitor_Size0

Printer_Asset_Tag0

Printer_Make0

Printer_Model0

Printer_Serial0

Software_Asset_Tag0

TC_First_Name0

TC_Last_Name0

TC_Phone0

IBM_Bios_Information_SPEC


PC_Serial_Number0



7


PC_Model_Number0


BIOS_Image_Date0


BIOS_Image_ID0


System_Bo
ard_Serial_Number0


Model_Unique_Identifier0


System_Board_Replacement_Number0


System_Board_Part_Number0


IBM_Bios_Information_COMM


Category0


Release_Date0



(Note: Each column name has had the spaces replaced with underscores and the name appended wit
h a zero.)


IMPORTANT:
It is at this point that you must re
-
create your views. The SMS SQL View Generator is
not dynamic. Therefore, each time you add or drop tables from the SMS database you should
recreate the views. For more information on SMS Views be
sure to read “Improved SQL Queries for
SMS v1.2”.


Proof of Delivery

Report

Now that we have a better
understanding

of the steps required to produce our queries, let’s take a
look at our first report:


For this report we’ll build on the following views cr
eate by the view generator:

vIdentification

vIBM_Bios_Information

vPowerMIF

vNetwork


Here is our SQL query script:


SELECT

a.name0,

b.PC_Serial_Number0,

c.PC_Asset_Tag0,

d.IP_Address0

FROM

vIdentification a,

vIBM_Bios_Information b,

vPowerMIF c
,

vNetwork d


WHERE

a.dwMachineID = b.dwMachineID and

a.dwMachineID = c.dwMachineID and

a.dwMachineID = d.dwMachineID

ORDERBY

b.PC_Serial_Number0





8

The results would resemble the following:


Name0


PC_Serial_Number0


PC_Asset_Tag0


IP_Address0


PDCServ
er

23AA993



123456



1.10.1.1

PDCWkstn1

23AU921



123457



1.10.1.2

PDCWkstn2

23AU922



123458



1.10.1.3


3 Rows Affected


As you can tell from the SQL query, this is a
simple

query. However, the simplicity is derived from the
use of the SQL view
s.


On to our second report:


Accounting RC Code Report


For this
report,

we’ll use the following views:


vIdentification

vAssetMIF

vIBM_BIOS_Information


Here’s the query:



SELECT


a.Name0,

b.First_Name0,

b.Middle_Initial0,

b.Last_Name0,

b.User_R
C_Code0,

b.Computer_RC_Code0,

c.PC_Serial_Number0,

b.PC_Asset_Tag,

b.Monitor_Serial0,

b.Monitor_Asset_Tag0,

b.Printer_Serial0,

b.Printer_Asset_Tag0

FROM


vIdentification a,


vAssetMIF b,



vIBM_BIOS_Information c

WHERE



a.d
wMachineID = b.dwMachineID AND



a.dwMachineID = c.dwMachineID


ORDERBY


b.Computer_RC_Code0





9

As demonstrated above, the
essential

elements to effective Asset Management reporting is the use of
custom MIF forms for data capturing and the use o
f the SQL views. When combined, your reporting
capabilities are virtually limitless.


Since it would be tiresome and redundant to continue these examples for each of the report
requirements outlined earlier, we will conclude this paper with a final SQL qu
ery that will search the
SMS database for a specific record.


Human Resource Supplement Report


For this
report,

we will only use those fields specific to each user.


SELECT


a.First_Name0

a.Middle_Initial0


a.Last_Name0

a.Address_Line_10

a.Address_Lin
e_20

a.City0

a.State0

a.Zip_Code0

a.Phone0

a.Mail_Code0

a.Department0

a.Manager0

FROM

vAssetMIF


WHERE

a.First_Name0 = “Larry” AND

a.Last_Name0 = “Duncan AND

a.Middle_Initial0 = “A”


The results would be one row set with the information specific to that

of the employee named “Larry A.
Duncan”. In
case

of more than one

record matching the criteria
, all
records

found
would be included in
the result set.



Closing


I hope that I have opened your eyes to the wealth of information that can be obtained via SMS.
Howeve
r,
all th
is
is

academic if you do not have upper management sponsorship. As with any other
project, the lack of support from the hierarchy is what will prevent your successful design,
implementation, use and support. They must stand b
ehind you from the beginning.


Certainly a topic as heated as Asset Management often gets, one would suspect that the senior
management could only be interested in accurate reporting regarding their cost centers.
Nevertheless,

you
can never be certain.




10

If your company wou
ld like more information on Asset Management, Enterprise Engineering, Custom
MIF form tools, etc… please visit our website at:
www.pdcgroup.com
. As always, we look forward to
working with you to help meet your consult
ing needs.


Larry A. Duncan, MCP

Principle Partner, PDC Group

larry.duncan@pdcgroup.com

http://www.pdcgroup.com