You are here :
- Home
- Technical Information
- Asset Manager Pro: Data access via SQL
OVERVIEW
Accessability to asset data has always been a baseline for Asset Manager Pro. The database tables are published in the online and PDF documentation.
Some larger customers with their own DBA resources have high levels of SQL expertise and have created a lot of added value in their AM Pro installations by exploiting asset data within other business applications.
This Tech Note is intended to promote awareness of the availability of the data and encourage its greater use.
Vector's AM Pro support group is available to give advice on using SQL queries to access the database, and will write queries for you. Complex requirements may be delivered under Professional Services terms.
SELECT DISTINCT NODES.NODENAME FROM NODES WHERE ( NODES.OSNAME like '%Windows 7%' )
SELECT DISTINCT NODES.NODENAME FROM NODES WHERE ( not(NODES.OSNAME like '%x64%') )
SELECT DISTINCT NODES.NODENAME FROM NODES, HWUPDATE, HWBUS WHERE (NODES.NODENAME=HWUPDATE.NODENAME) AND (HWBUS.HWDATE=HWUPDATE.HWDATE) AND (HWBUS.NODENAME=HWUPDATE.NODENAME) AND ( HWBUS.HWNAME like '%Virtual%' OR HWBUS.HWNAME like '%VMware%' )
SELECT DISTINCT NODES.NODENAME, NODES.CPU, NODES.CPUSPEED, NODES.TOTMEMORY, NODES.NETADDR, NODES.DEPT, NODES.OWNERNAME, NODES.OWNERTELE FROM NODES, HWUPDATE WHERE CREATEDATE < '$MM/DD/YY(-30)$' AND NODES.NODENAME = HWUPDATE.NODENAME
All four of these queries could be generated and copied from the SQL Wizard that is available to support Dynamic Group creation in Asset Manager Pro. Use the Natural Language editor to create your query, then View SQL.
Here are two examples of more complex queries created by the AM Pro support team for a customer.
SELECT dbo.Nodes. [NODENAME] AS MAINNODENAME, [OSNAME], [dbo]. [LOC_REGION]. [Name] AS 'Region', dbo.LOC_STATE.Name AS 'State', HWMAIN.PCSERIALNUM, BIOSSYSDATE FROM [dbo]. [NODES] JOIN dbo.LOC_REGION ON dbo.LOC_REGION.OBJECTID = dbo. [NODES].FKLOC_REGION JOIN dbo.LOC_STATE ON dbo.NODES.FKLOC_STATE = dbo.LOC_STATE.OBJECTID JOIN dbo.HWMAIN ON dbo.Nodes.NODENAME = dbo.HWMAIN.NODENAME WHERE (osname like 'Windows XP%' or osname like 'Windows Server 2003%') AND HWMAIN.HWDATE = ( SELECT TOP 1 HWMAIN.HWDATE FROM HWMAIN WHERE HWMAIN.NODENAME=NODES.NODENAME ORDER BY HWMAIN.HWDATE DESC )
SELECT DISTINCT dbo.NODES. [NODENAME], [PACKAGE], [dbo]. [LOC_REGION]. [Name] AS 'Region', dbo.LOC_STATE.Name AS 'State', PCSERIALNUM, BIOSSYSDATE FROM [dbo]. [NODES] JOIN dbo.LOC_REGION ON dbo.LOC_REGION.OBJECTID = dbo. [NODES].FKLOC_REGION JOIN dbo.LOC_STATE ON dbo.NODES.FKLOC_STATE = dbo.LOC_STATE.OBJECTID JOIN dbo.REGAPPS ON [dbo]. [NODES]. [NODENAME] = dbo.REGAPPS.NODENAME JOIN dbo.HWMAIN ON dbo.Nodes.NODENAME = dbo.HWMAIN.NODENAME JOIN (Select Nodename, MAX(HWMAIN.BIOSSYSDATE) as BIOSDate, MAX (HWMAIN.HWDATE) as HWDATE from HWMAIN GROUP BY NODENAME AS SUB ON dbo.NODES.NODENAME = sub.NODENAME WHERE PACKAGE like '%Office%' AND PACKAGE like '%2003%' AND PACKAGE not like '%web%' AND PACKAGE not like '%interop%' AND PKGSTATUS <> 'Removed' AND BIOSSYSDATE = sub.BIOSDate AND HWMAIN.HWDATE = sub.HWDATE
For rapid assistance with your SQL queries: email support@vector-networks.com