I am new to the whole scripting but looks like that will be the only way to run my report. I got some help in writing it from Dell. I need to add one more column but don't know the field name. If you go under Assets we have a Department/Branch field. I want to pull that into my report but don't know the name. I have tried ASSET.Department/Branch and other things. Below is the script. I figured I could just add that one piece to the Order by line. I am now guessing I need to select and call that field to be able to have it in the list. Any help or links to how to find out all the field names so I can start learning more about SQL coding would be great.

Thank You 
Ed


This query will list all systems that contain specific software item and
version with their System Name, Software Title and Version. Replace the
XX
values with software titles and the YY values with version numbers. 


/** Query Start **/ 

/** Select System Name, Software Title and Version **/ 

SELECT MACHINE.NAME AS SYSTEM_NAME,
SOFTWARE.DISPLAY_NAME AS SOFTWARE_DISPLAY_NAME,
DISPLAY_VERSION as Version FROM MACHINE 

/** Custom Join Clause **/ 

LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE
<>
'hidden')
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID =
MACHINE.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 

/** Custom Where Clause **/ 

WHERE SOFTWARE.DISPLAY_NAME LIKE '%XX%' 
AND SOFTWARE.DISPLAY_VERSION='YY' 

/** Group By Machine ID and Software Title **/ 

GROUP BY MACHINE.ID, SOFTWARE.DISPLAY_NAME 

/** Order By Display Version, Label Name, Machine Name and Software
Title
Ascending **/ 

ORDER BY SOFTWARE.DISPLAY_VERSION, LABEL.NAME asc,MACHINE.NAME
asc,SOFTWARE.DISPLAY_NAME asc, 

/** End Query **/

1 Comment   [ + ] Show Comment

Comments

  • Hi. I do not remember all the names of the fields and tables and generally what I do when I need to create a custom report is connecting to the internal database using TOAD for MySQL or MySQL Browser (both free) and I start to fiddle around.
    To connect to the internal database you need to allow database connection in the Configuration ->Security menu.
    Usually the user is R1 and the default password box747
    Have fun :-) Marco - StockTrader
Please log in to comment

Community Chosen Answer

3
If you log in to your Kace appliance but on the address bar type http://<kaceip>/adminui
Navigate to the asset type for your Department/Branch asset, the address bar should reflect an ID for your asset type. Your field for your report should be 'Asset_Data_(ID number)'. 

Like StockTrader suggested you should get hold of a tool for looking at the MySQL database. I use MySQL workbench, but everyone has a favorite.
Answered 12/15/2014 by: Druis
Blue Belt

Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Member has been banned from the site
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja
Share SNMP OID and MIB Information
It’s easy to add custom SNMP OID GETs to your Dell KACE K1000 inventory, but where do you find the exact OIDs you need for a specific device? This is the place to share the OIDs and MIBs you have – and to request the ones you need.

Share