Video Screencast Help
Search Video Help Close Back
to help
New in the Rewards Catalog: Vouchers for "Symantec Technical Specialist" and "Symantec Certified Specialist" exams.

Custom Reports

Updated: 21 May 2010 | 2 comments
CarrieFog's picture
0 0 Votes
Login to vote

I'm struggling to build a custom report using SQL.

I would like the following info in a report, however i don't know enough about inner/outer links to build the report - can anyone help.

Lease Schedule Number
start Date
End date
Computer Type
Serial Number
Manufactuer
Model
user Name
Cost Center

Comments

MBHarmon's picture
27
May
2009
1 Vote +1
Login to vote

Getting a report like that

Getting a report like that should be fairly easy.  I'm not tracking lease information right now, but if you look at this Article i wrote a while back it might give you somewhere to start. 
https://www-secure.symantec.com/connect/articles/asset-report-writing-tips

I've been trying to educate some of my teammates on writing reports as well and it can be trickey to start with, but once you understand the basics of the database it's fairly easy. 

Someone else might have a better way to do it, but here's another breakdown of what I've been doing.  This does take a little bit of SQL knowledge, but not much.

Each item has a "Guid".  Items in your case would be, computer, User, Cost Center, and maybe Lease (if those are tracked as contracts instead of Data on the computer)

Each item has "Data Classes".  Each data class is one Table or view on the Altiris database.  Those tables all have the "Guid"  for the item listed on them.  If you want information on one particular Type such as an inventory report might give you, you'd just JOIN on the appropriate "Guid" columns. 

Items can also have "Associations" such as "Cost Center" to "Computer".  These relationships also have their own guids and a table linking them.   So when you're associating the "Cost Center" to "Computer" in your report you need 3 things to get to the Cost center's information.  The Computer's Guid, the Cost Center's Guid, and the Association's Guid (also called an Association Type).  This is all stored in the "ResourceAssociation" table. 

Like I said before it does take some SQL knowledge, but because each database structure can be soo different (especially once you start using Asset Management Solution) it can be hard to get you that SQL directly.  Hopefully this helps you out at least a little bit.

- Matt

Sean_Ebeling's picture
27
May
2009
1 Vote +1
Login to vote

Joins -

I almost always use Full outer Joins and then Limit using my Where statement. It's just easier to read.

I don't have an NS in front of me now but your SQL will look something like,

Select table1.Lease_Schedule_Number, table2.[start date] - ETC.
--- Start Date needs to be bracketed because there is a space in it's collumn name.
From Table1 T1
Full Outer Join Table2 T2 on T2._ResourceGuid =T1._ResourceGuid
Where --- whatever.

A great resource for learning the basics of SQL is W3Schools.Com
http://www.w3schools.com/sql/default.asp

~Sean