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
