SQL Query Examples: New Assets Since Last Scan

Document created by synapsr on Feb 4, 2015
Version 1Show Document
  • View in full screen mode

Are you in the same boat with many of our customers who find themselves supporting the organization's asset management needs?  In addition to the previously posted unmanaged assets SQL query, here's one that shows new assets since last scan.

 

WITH

  last_scan_for_site AS (

    SELECT site_id, MAX(scan_id) as scan_id

    FROM dim_site_scan

       JOIN dim_scan USING (scan_id)

    WHERE status_id = 'C'

    GROUP BY site_id

),

previous_scan_for_site AS (

    SELECT site_id, MAX(scan_id) as scan_id

    FROM dim_site_scan

       JOIN dim_scan USING (scan_id)

    WHERE (site_id, scan_id) NOT IN (

       SELECT * FROM last_scan_for_site

    )

    AND status_id = 'C'

    GROUP BY site_id

),

current_scan_state AS (

    SELECT site_id, asset_id, scan_id

    FROM dim_site

       JOIN dim_site_asset USING (site_id)

       JOIN last_scan_for_site USING (site_id)

       JOIN dim_asset_scan USING (asset_id, scan_id)

),

previous_scan_state AS (

    SELECT site_id, asset_id, scan_id

    FROM dim_site

       JOIN dim_site_asset USING (site_id)

       JOIN previous_scan_for_site USING (site_id)

       JOIN dim_asset_scan USING (asset_id, scan_id)

),

last_scan_for_asset AS (

    SELECT site_id, asset_id, scan_id

    FROM last_scan_for_site

       JOIN dim_site_asset USING (site_id)

),

new_assets AS (

  SELECT all_sate.site_id, all_sate.asset_id, baselineComparison(all_sate.scan_id, lsfa.scan_id)

  FROM (

     SELECT * FROM current_scan_state

     UNION ALL

     SELECT * FROM previous_scan_state

  ) all_sate

  JOIN last_scan_for_asset lsfa USING (asset_id)

  GROUP BY all_sate.site_id, all_sate.asset_id

  HAVING  baselineComparison(all_sate.scan_id, lsfa.scan_id) = 'New'

)

SELECT ip_address, mac_address, host_name, site.name, os.description

  FROM new_assets

     JOIN dim_asset USING (asset_id)

     JOIN dim_operating_system os USING (operating_system_id)

     JOIN dim_site site USING (site_id)

  ORDER BY ip_address

3 people found this helpful

Attachments

    Outcomes