Check the number of workstations assigned to each site in SBRP - SQL Server

Version 2
    Share This:

    Below is the query to check the number of workstations assigned to each BMC CM site.

    A site is a collection of repeaters that are assigned for a particular IP subnet.

     

    select inv_machine_site.location_name,  count(distinct  inv_machine.mac)  '# of Machines' from inv_machine, inv_machine_site
    where inv_machine_site.machine_id = inv_machine.id
    group by inv_machine_site.location_name

    order by 2 desc

     

    If you need to check the number of workstations which are not assigned to any site, you can use the below query:

     

    select 'No Site Name' as 'Site Name',
    (select count(distinct  inv_machine.mac) from inv_machine)
    -
    (select count(distinct  inv_machine.mac) from inv_machine, inv_machine_site
    where inv_machine_site.machine_id = inv_machine.id
    )   '# of Machines'

     

    The below query would be useful for pulling up the list of workstations not assigned to any of the sites:

     

    select distinct inv_machine.name from inv_machine

    where inv_machine.name not in (select distinct  inv_machine.name from inv_machine, inv_machine_site

    where inv_machine_site.machine_id = inv_machine.id)