/build/static/layout/Breadcrumb_cap_w.png
05/04/2018 590 views
 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
0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

2
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.
Answered 05/04/2018 by: chucksteel
Red Belt

  • 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

All Answers

1
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? 

LwklDP.jpeg
Answered 05/09/2018 by: madro
Senior White Belt

  • Ah, you just want currently installed software, along with the date it was installed? For all machines or for just one machine?
    • 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
      • 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.
0
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

Answered 05/11/2018 by: madro
Senior White Belt