/build/static/layout/Breadcrumb_cap_w.png

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
  • You can but you would have to create a regex statement in the filters portion of the report on the IP Address.

    Try this site to build it.

    http://www.analyticsmarket.com/freetools/ipregex - nshah 8 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
0

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 8 years ago
Third Degree Brown Belt
0
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ