Report on PCs using PS/2 Keyboard and Mice?

[tomscott2340]

I need to find out which assets of mine are using PS2 keyboards / mice.. We are doing a mass PC refresh and replacing all desktops with laptops... If users use PS2 mice/keyboard they will need those replaced as the laptops obviously don't have the ports..

Thanks for any help!

[RCorbeil]

Maybe you could use process of elimination? You can use tblUSBDevices to see whether or not a machine has a USB keyboard or USB mouse. You can use tblPortConnector to determine whether a machine as a PS/2 keyboard port or a PS/2 mouse port. If a machine has PS/2 keyboard/mouse ports and doesn't have USB keyboard or mouse plugged in, there's a good chance it's got PS/2 keyboard and mouse.

I used my inventory to identify USB device descriptions that correspond to mice and keyboards. You'll probably need to tweak the device name lists for your inventory.

SELECT Distinct Name
FROM tblUSBDevices
WHERE Name LIKE '%keyboard%'

and

SELECT Distinct Name
FROM tblUSBDevices
WHERE Name LIKE '%mouse%'

should give you lists to work from. I, for example, eliminated things like "Microsoft%Mouse and Keyboard Center)" as they appear in the hardware lists alongside HID mouse and keyboard references.

Once you've got your list of USB keyboards and mice, something like this could be used as a starting point for what you're after:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID Keyboard Device'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB Keyboard'
                        OR tblUSBDevices.Name = 'Designer Keyboard'
                       )
                )
    THEN 'USB Keyboard'
    ELSE ''
  END AS USBKeyboard,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID-compliant mouse'
                        OR tblUSBDevices.Name = 'Lenovo Optical Mouse (HID)'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB mouse'
                        OR tblUSBDevices.Name = 'Microsoft USB Wheel Mouse Optical'
                        OR tblUSBDevices.Name LIKE 'Microsoft USB Basic Optical Mouse%'
                       )
                )
    THEN 'USB Mouse'
    ELSE ''
  END AS USBMouse,

  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 13
                )
    THEN 'PS/2 Keyboard Port'
    ELSE ''
  END AS PS2KeyboardPort,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 14
                )
    THEN 'PS/2 Mouse Port'
    ELSE ''
  END AS PS2MousePort
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
  tblAssetCustom.State = 1
  AND tblAssets.AssetType=-1

Once you do some sanity-checking to make sure the results are accurate, add filters. E.g. eliminate all computers that have both USB keyboard and mouse and eliminate any computers that don't have either PS/2 keyboard port or PS/2 mouse port.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID Keyboard Device'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB Keyboard'
                        OR tblUSBDevices.Name = 'Designer Keyboard'
                       )
                )
    THEN 'USB Keyboard'
    ELSE ''
  END AS USBKeyboard,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID-compliant mouse'
                        OR tblUSBDevices.Name = 'Lenovo Optical Mouse (HID)'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB mouse'
                        OR tblUSBDevices.Name = 'Microsoft USB Wheel Mouse Optical'
                        OR tblUSBDevices.Name LIKE 'Microsoft USB Basic Optical Mouse%'
                       )
                )
    THEN 'USB Mouse'
    ELSE ''
  END AS USBMouse,

  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 13
                )
    THEN 'PS/2 Keyboard Port'
    ELSE ''
  END AS PS2KeyboardPort,
  CASE
    WHEN EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 14
                )
    THEN 'PS/2 Mouse Port'
    ELSE ''
  END AS PS2MousePort
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
  tblAssetCustom.State = 1
  AND tblAssets.AssetType=-1
  AND NOT (EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID Keyboard Device'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB Keyboard'
                        OR tblUSBDevices.Name = 'Designer Keyboard'
                       )
                )
    AND EXISTS (SELECT Top 1
                 FROM tblUSBDevices
                 WHERE
                   tblUSBDevices.AssetID = tblAssets.AssetID
                   AND (   tblUSBDevices.Name = 'HID-compliant mouse'
                        OR tblUSBDevices.Name = 'Lenovo Optical Mouse (HID)'
                        OR tblUSBDevices.Name = 'Microsoft Hardware USB mouse'
                        OR tblUSBDevices.Name = 'Microsoft USB Wheel Mouse Optical'
                        OR tblUSBDevices.Name LIKE 'Microsoft USB Basic Optical Mouse%'
                       )
                )
          )
  AND (EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 13
                )
    OR EXISTS (SELECT Top 1
                 FROM tblPortConnector
                 WHERE
                   tblPortConnector.AssetID = tblAssets.AssetID
                   AND tblPortConnector.ConnectorType LIKE '%61%'
                   AND tblPortConnector.PortType = 14
                )
      )

It won't be a list of computers that definitely have PS/2 mouse and/or keyboard, but it should be a short list of machines that might have them. On my result set, for example, I can further eliminate notebook computers because I know we don't roll out PS/2 input devices for them.

[Esben_D]

It will just show the assets which have a PS2 port available. However, you should be able to use this in combination with tblKeyboard and tblPointingDevice. tblPointingDevice has a specific field for indicating the port used, but tblKeyboard does not so it might be harder to find out of a keyboard is still using that port.

[tomscott2340]

Thanks a lot for the reply!

Does that report show if there are things actually plugged into those ports / being used or does it just show all devices that actually have the PS2 ports?

[Esben_D]

We have a built in report for checking if an asset has a PS/2 port named "Port: Parallel Port PS/2"

You can also find it in our Report center here: https://www.lansweeper.com/forum/yaf_postst10103_Windows--Parallel-port-PS-2.aspx#post38553