Video Screencast Help
Security Community Blog

Search Assets Database by IP Address

Created: 22 Jul 2011 • Updated: 22 Jul 2011
awgtek variq's picture
0 0 Votes
Login to vote

Problem:

You would like to search the Assets and Policies database for an IP using an Advanced (SQL) Query.

Solution:

In your advanced query enter the following SQL. Then replace [ipnum] with your IP address (i.e. dotted quad IP).

SELECT SYMCMGMT.SYMC_SIM_POLICY.NAME,  SYMCMGMT.SYMC_SIM_ASSET.* FROM SYMCMGMT.SYMC_SIM_ASSET_POLICY_MAP RIGHT JOIN SYMCMGMT.SYMC_SIM_ASSET
on SYMCMGMT.SYMC_SIM_ASSET_POLICY_MAP.ASSET_ID = SYMCMGMT.SYMC_SIM_ASSET.ID
LEFT JOIN SYMCMGMT.SYMC_SIM_POLICY ON SYMCMGMT.SYMC_SIM_ASSET_POLICY_MAP.POLICY_ID = SYMCMGMT.SYMC_SIM_POLICY.ID
 WHERE SYMCMGMT.SYMC_SIM_ASSET.IP = (

SELECT CASE WHEN  (IPNumber - 2147483648) > 0 THEN IPNumber -(2*2147483648) ELSE IPNumber END AS IPNUMBER2  FROM  ( SELECT     16777216 *
CAST(LEFT(IPNumber1, LOCATE('.', IPNumber1, 1)-1) AS BIGINT)     +  65536 * CAST(SUBSTR(IPNumber1, LOCATE('.', IPNumber1, 1) + 1, LOCATE('.',
IPNumber1,LOCATE('.', IPNumber1, 1) +1) - LOCATE('.', IPNumber1, 1) - 1) AS BIGINT)     +    256 * CAST(SUBSTR(IPNumber1, LOCATE('.',
IPNumber1,LOCATE('.', IPNumber1, 1) +1) + 1, LOCATE('.', IPNumber1, LOCATE('.', IPNumber1,LOCATE('.', IPNumber1, 1) +1) +1) - LOCATE('.',
IPNumber1,LOCATE('.', IPNumber1, 1) +1) - 1) AS BIGINT)      +          CAST(RIGHT(IPNumber1, LENGTH(IPNumber1)  - LOCATE('.', IPNumber1,
LOCATE('.', IPNumber1,LOCATE('.', IPNumber1, 1) +1) +1)) AS BIGINT)      AS IPNumber FROM (SELECT '[IPNUM]' AS IPNumber1 FROM
SYSIBM.SYSDUMMY1) AS TEMP1)  AS TEMP2)