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
Second Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Share SNMP OID and MIB Information
It’s easy to add custom SNMP OID GETs to your Dell KACE K1000 inventory, but where do you find the exact OIDs you need for a specific device? This is the place to share the OIDs and MIBs you have – and to request the ones you need.

Share