Sql query for inventory software installed in Machine
Hi, I need to make a sql query,( Machine name, Ip, Software currently installed, Version software, Status machine, Data Software Installed, Data Software uninstalled ).
I found a software table but this table looks for the installed and uninstalled software. I would like to identify the current machines (active status only)
I'm starting in sql
Anyone have any idea on which tables do I get this information?
Thank very much
I found a software table but this table looks for the installed and uninstalled software. I would like to identify the current machines (active status only)
I'm starting in sql
Anyone have any idea on which tables do I get this information?
Thank very much
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
chucksteel
6 years ago
To get the dates software was discovered (installed) or no longer detected (uninstalled) you need to check the ASSET_HISTORY table. Here is an example that shows software installed on specific machines in the past seven days:
SELECT ASSET_HISTORY.ASSET_ID,
ASSET_HISTORY.TIME,
ASSET_HISTORY.VALUE1,
ASSET_HISTORY.VALUE2,
ASSET_HISTORY.USER_TEXT,
MACHINE.NAME
FROM ASSET_HISTORY
JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
JOIN MACHINE ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
WHERE CHANGE_TYPE = "Detected"
AND FIELD_NAME = "SOFTWARE"
AND DATE(ASSET_HISTORY.TIME)> DATE_SUB(NOW(), INTERVAL 7 day)
AND MACHINE.NAME LIKE 'lib-ic-%'
Note that this query assumes you are matching computer assets to the computer inventory based on serial number, you may need to adjust that for your environment.
The SOFTWARE table contains all of the software in the inventory, to get a list of software for a particular machine you need to go join MACHINE to MACHINE_SOFTWARE_JT to SOFTWARE, like this:
SELECT DISPLAY_NAME, DISPLAY_VERSION FROM ORG1.SOFTWARE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
JOIN MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
WHERE MACHINE.NAME = "steelc"
ORDER BY DISPLAY_NAME
I hope that helps in putting together the pieces you need for your report. If you run into trouble, post your current SQL and we can provide more specific guidance.
Comments:
-
Thanks for the help Chuck, Tuesday fair I will do this, and I will report it to you as it was.
Thank you very much - madro 6 years ago
Posted by:
madro
6 years ago
Maybe I have formulated my wrong tag, I would like to take an inventory of this field (image below) The software installed currently. Or do I get this information just by putting range of days in the ASSET_HISTORY table?
Comments:
-
Ah, you just want currently installed software, along with the date it was installed? For all machines or for just one machine? - chucksteel 6 years ago
-
I just want the software currently installed on all machines, in Kace's standard inventory I can only see one machine. I would like to see all of them with IP information together. Do you know the name of the table that contains this data?
Thanks Chuck - madro 6 years ago-
The software is stored in the SOFTWARE table. If you want to view all of the software installed on all machines, the easiest thing is to go to Inventory, Software and sort by the devices column. A report that included the IP addresses of all of the machines that have a software title installed would be very long, but if you click on a software title on the inventory page it will show you those machines. - chucksteel 6 years ago
Posted by:
madro
6 years ago
Hi chuck, I've tried looking at Inventory / Software with only seeing the devices that are installed in each software.
I will try to make a query with the software table and machine together. I think I get what I want from this jumble.
I need to do an audit on each floor of the company and I need to know which software is installed on each machine.
Thanks for the answer
