Computer inventory within a specific IP range.
Good morning all.
I am wondering if there is a way to run a report that would list all computers which are within a specific IP range. We are planning on making a large enough computer order and i would like to prioritize those who are running off warranty or close to off warranty systems first.
thanks
1 Comment
[ + ] Show comment
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Top Answer
This is easy with a custom SQL report using the MySQL INET_ATON function:
SELECT * FROM ORG1.MACHINE
WHERE INET_ATON(IP) BETWEEN INET_ATON("172.16.16.1") and INET_ATON("172.16.20.255")
This will find computers with IP addresses between the given numbers.
Also, here's a report that finds Dell computers with a warranty expiring in the next 60 days:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,
DW.SERVICE_LEVEL_DESCRIPTION, MAX(DW.END_DATE) AS EXPIRATION_DATE
FROM DELL_WARRANTY DW JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
GROUP BY MACHINE_NAME
HAVING EXPIRATION_DATE BETWEEN NOW() and DATE_ADD(NOW(), INTERVAL 60 DAY)
ORDER BY EXPIRATION_DATE DESC
Combining the two gets you something like this:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,
DW.SERVICE_LEVEL_DESCRIPTION, MAX(DW.END_DATE) AS EXPIRATION_DATE
FROM DELL_WARRANTY DW JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND INET_ATON(IP) BETWEEN INET_ATON("172.16.16.1") and INET_ATON("172.16.20.255")
GROUP BY MACHINE_NAME
HAVING EXPIRATION_DATE BETWEEN NOW() and DATE_ADD(NOW(), INTERVAL 60 DAY)
ORDER BY EXPIRATION_DATE DESC
Posted by:
TheAustinDave
9 years ago
If you were looking at a quick inventory you could also obtain a list via the RexEx option with something like "^10\.10\.10\.([1-9]|[1-9][0-9]|1([0-9][0-9])|200)$"
https://msdn.microsoft.com/en-us/library/az24scfc.aspx
http://www.analyticsmarket.com/freetools/ipregex
https://msdn.microsoft.com/en-us/library/az24scfc.aspx
http://www.analyticsmarket.com/freetools/ipregex
Try this site to build it.
http://www.analyticsmarket.com/freetools/ipregex - nshah 9 years ago