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
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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
Please log in to comment

Answer Chosen by the Author

0
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

Answered 05/26/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

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
Answered 05/26/2015 by: TheAustinDave
Third Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity