sms frontend 2

[sepp]

Hello,

do you know a frontend to the sms server.
I want to make an ms-access frontend to the sql database in order
to see the inventory informations. Do I have to check the sms database
model myself (a lot of work), or does there exist an easier way ?

Thanks a lot

Andi

 

[snyderj]

There are a variety of access points to the SMS 2.0 data:
- SMS Admin console
- SQL Server ODBC via Access (or other DB)
- WMI ODBC / WBEM via Access, Excel or VB
- SMS Web Reports
- WBEMTEST (not a good method)
- CIM Studio (best for schema, not data)

These are some of the core CIM tables:
* SMS_BaseClass\SMS_Resource
- SMS_R_System
* SMS_BaseClass\SMS_Group\SMS_G_System_Current
- SMS_G_System_OPERATING_SYSTEM
- SMS_G_System_WORKSTATION_STATUS
- SMS_G_System_LastSoftwareScan
- SMS_G_System_LOGICAL_DISK
- SMS_G_System_PARITION
- SMS_G_System_X86_PC_MEMORY
* SMS_BaseClass\SMS_Group\SMS_G_System
- SMS_G_SoftwareProduct
- SMS_G_SoftwareFile

These are some of the core Web Reports view tables:
- dbo_v_R_System
- dbo_v_GS_OPERATING_SYSTEM
- dbo_v_GS_WORKSTATION_STATUS
- dbo_v_GS_LastSoftwareScan
- dbo_v_GS_LOGICAL_DISK
- dbo_v_GS_PARTITION
- dbo_v_GS_X86_PC_MEMORY
- dbo_v_GS_SoftwareFile

Table links for current information is generally on ResourceID (may be on SystemName for disk information; ProductID for software). For historical information the link would also be on Revision ID.

Hope this helps.

 

[deanc]

Definitely get the SMS Web Reporting system installed. It not only works great for 99% of what most people need, but it creates a series of SQL views that are well thought out. Just use Access or similar to look at the contents of the views (which appear to be tables) starting with v_ and you'll probably find what you need for ad-hoc queries.

 

[jpmackl]

We are just deploying SMS 2.0 - We would like to deploy the
Microsoft Web Reports. I have been told by our consultant that currently we Microsoft has a SQL 2000 security bug and it's a risk to roll out Web Reports - has anyone seen this problem? Thanks!

 

[wmaraman]

You may try using the smsextract.xlt query tool. This tool is found in the Backoffice 4.5 Resource kit. It allows you to connect to the SQL database and export the queries from the site server into excel. You can then port these excel files into an access database and link together whatever tables you want. There is also an access database tool as well. This might not be the best implementation if you can't afford to spend some time here and there to manipulate the data a little bit. Also because you are actually doing exports it's not completely dynamic. However by doing this you could make a query for the User's Fullname and Username and port that into access and then make another query for the users hardware and then link the two based on username. This would allow you to match up a User's hardware with their Fullname without to much effort. If your interested the Resource Kit runs about $150-200 dollars if I remember correctly. Hope this helps.

 

[mbarlam]

I am evaluating SMS 2.0 for my company. I can't get Crystal Reporting and Software Inventory to work with the Eval copy. Does this stuff work? I have about 1000 nodes. Win95/98/NT4.0 environment. Should I screw Crystal Info and use the Web Reporting?

 

[mbarlam]

I mean Software Metering.