Need Assistance Writing a KACE Report
I'm trying to create a fairly unique report and I have no idea where to start.
I'd like to determine how many systems in KACE are assigned to the same user, by way of the System Description field.
In my organization, our System Descriptions look something like this: John Smith - Sacramento Office
We need a report that shows ONLY systems that have two or more System Descriptions that are the same.
For example, if we rolled a PC to John Smith a few years ago and also rolled a laptop to him 6 months ago, we would want John Smith's systems to show on this report, as he has two assigned to him.
This seems difficult because, for one thing, we could have system number 1 Description as "John Smith - Sacramento Office" and his second system could be "John Smith - Bay Area Office", so it couldn't be an exact comparison of the entire System Description.
Any assistance with this is very much appreciated.
Thanks in advance,
Cameron
I'd like to determine how many systems in KACE are assigned to the same user, by way of the System Description field.
In my organization, our System Descriptions look something like this: John Smith - Sacramento Office
We need a report that shows ONLY systems that have two or more System Descriptions that are the same.
For example, if we rolled a PC to John Smith a few years ago and also rolled a laptop to him 6 months ago, we would want John Smith's systems to show on this report, as he has two assigned to him.
This seems difficult because, for one thing, we could have system number 1 Description as "John Smith - Sacramento Office" and his second system could be "John Smith - Bay Area Office", so it couldn't be an exact comparison of the entire System Description.
Any assistance with this is very much appreciated.
Thanks in advance,
Cameron
3 Comments
[ + ] Show comments
-
Is there always a dash between the user's name and location? - chucksteel 7 years ago
-
Yes, there is. That part is consistent. - cameronlanni 7 years ago
-
Do you ever include a middle name? Are there any employees with hyphenated last names? - JasonEgg 7 years ago
-
No, we never include a middle name. Yes, there are a handful of users with hyphenated last names but they would never have spaces on the sides of those hyphens, whereas the hyphens in the System Descriptions always have one space before and one space after the hyphen. - cameronlanni 7 years ago
Answers (2)
Answer Summary:
SELECT SUBSTRING_INDEX(SYSTEM_DESCRIPTION,' - ', 1) AS myUser, COUNT(*) AS deviceCount, GROUP_CONCAT(NAME) AS `Computer Names` FROM MACHINE GROUP BY myUser HAVING deviceCount > 1
SELECT SUBSTRING_INDEX(SYSTEM_DESCRIPTION,' - ', 1) AS myUser, COUNT(*) AS deviceCount, GROUP_CONCAT(NAME) AS `Computer Names` FROM MACHINE GROUP BY myUser HAVING deviceCount > 1
Please log in to answer
Posted by:
JasonEgg
7 years ago
Top Answer
How about this:
SELECT SUBSTRING_INDEX(SYSTEM_DESCRIPTION,' - ', 1) AS myUser,
COUNT(*) AS deviceCount,
GROUP_CONCAT(NAME) AS `Computer Names`
FROM MACHINE
GROUP BY myUser
HAVING deviceCount > 1
Comments:
-
This is awesome and exactly what I need. You have my sincere gratitude! Thank you very much for helping me out with this. It's much easier than sorting through 2,000 system descriptions! manually. - cameronlanni 7 years ago
Posted by:
Hobbsy
7 years ago
What you appear to be wanting to do is much more akin to an asset management process rather than an inventory manipulation, in other words if in the Asset for the inventory PC you created a user field you would be able to assign the device to John Smith. Once this value is in place you can report on the assets that are assigned to John. Always with the location in your description the better way of handling that is to use the Asset-Location field added to the asset record, so that you can still assign multiple devices to John Smith, but each device could also be associated with a different location.
If this data is updated by the techs when they assign or move kit this all rolls up top better reporting in particular from a starters and leavers perspective
Comments:
-
Thank you for this suggestion. We do use an Environmental Variable to track users, as well, and it is much easier, but it doesn't always get updated by the techs when they re-roll the system to a new user, so that's why I needed this System Description report. Good lookin' out. - cameronlanni 7 years ago
