![tomscott2340 tomscott2340](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-05-2018 05:00 PM
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!
Thanks for any help!
Labels:
- Labels:
-
Report Center
4 REPLIES 4
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2018 07:43 PM
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.
and
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:
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.
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.
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 Esben_D](https://community.lansweeper.com/legacyfs/online/forums_avatars/14060.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2018 09:49 AM
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 tomscott2340](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-05-2018 05:17 PM
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?
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 Esben_D](https://community.lansweeper.com/legacyfs/online/forums_avatars/14060.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-05-2018 05:07 PM
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
You can also find it in our Report center here: https://www.lansweeper.com/forum/yaf_postst10103_Windows--Parallel-port-PS-2.aspx#post38553
![](/skins/images/C814C14E754F4012AB6B3098C075832D/responsive_peak/images/icon_anonymous_message.png)