/bundles/itninjaweb/img/Breadcrumb_cap_w.png
I need to modify one of the existing reports to list all computers that have a specific piece of software installed. I have a few reports that show all software listed on all computers, but I just need a report for 1 piece of software. I created a Machine Smart Label and copied the SQL statement out, which is:
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.PUBLISHER like '%Calyx%')) ))

but when I run this report I get an error:
Exception while running report.
net.sf.jasperreports.engine.JRException: Duplicate
declaration of field : KUID

Not sure what this means. Any ideas?
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
If all your looking for is the machines and the software i think this will work.
SELECT M.NAME,
S.DISPLAY_NAME
FROM MACHINE M,
SOFTWARE S,
MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID
AND S.ID = MS.SOFTWARE_ID
AND S.PUBLISHER LIKE '%MICROSOFT%'
ORDER BY M.NAME,
S.DISPLAY_NAME

Just change the Microsoft to match your program.

I wonder if the error is because Jasper sees the KUID field twice...
Try only returning the machine table and see if that works.
SELECT MACHINE.*,
Unix_timestamp(NOW()) - Unix_timestamp(LAST_SYNC) AS LAST_SYNC_TIME,
Unix_timestamp(MACHINE.LAST_SYNC) AS LAST_SYNC_SECONDS
FROM ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION
ON KUID_ORGANIZATION.KUID = MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION
ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
WHERE ((( 1 IN (SELECT 1
FROM ORG1.SOFTWARE,
ORG1.MACHINE_SOFTWARE_JT
WHERE MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
AND SOFTWARE.PUBLISHER LIKE '%Calyx%') )))
Answered 02/14/2011 by: dchristian
Red Belt

Please log in to comment
0
I'm looking for 1 specific piece of software and all the machines that it's installed on. I'll try this in a little bit and let you know if it worked. Thanks.
Answered 02/15/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
How would I modify
SELECT M.NAME,
S.DISPLAY_NAME
FROM MACHINE M,
SOFTWARE S,
MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID
AND S.ID = MS.SOFTWARE_ID
AND S.PUBLISHER LIKE '%MICROSOFT%'
ORDER BY M.NAME,
S.DISPLAY_NAME

to include the IP address?
Answered 02/24/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
add M.IP to your select statment
Answered 02/24/2011 by: dchristian
Red Belt

Please log in to comment
0
Where would I add that? It would be a new WHERE line right?
Answered 02/24/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
SELECT M.NAME,
S.DISPLAY_NAME,
M.IP
FROM MACHINE M,
SOFTWARE S,
MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID
AND S.ID = MS.SOFTWARE_ID
AND S.PUBLISHER LIKE '%MICROSOFT%'
ORDER BY M.NAME,
S.DISPLAY_NAME


You should probably spend some time checking out this website.
[link]http://www.w3schools.com/sql/default.asp[/link]

They have great tutorials on the basics of SQL.
Answered 02/24/2011 by: dchristian
Red Belt

Please log in to comment
0
I'd love to learn some basic SQL when I have time, that's just not right now. I'm 3 weeks behind on our Windows 7 migration and I'm supposed to have the entire company converted to Windows 7 by the end of March. I need this to figure out which pc's at each branch have a certain piece of software. I used:
SELECT M.NAME,
S.DISPLAY_NAME,
M.IP
FROM MACHINE M,
SOFTWARE S,
MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID
AND S.ID = MS.SOFTWARE_ID
AND S.Display_Name LIKE '%Insight Teller%'
ORDER BY M.NAME,
S.DISPLAY_NAME

and it returned all computers that have S.Display_Name Like '%Insight Teller%' with a column for their IP. I only want to return the pc's that are at specific branches. One of our IP subnets starts with 10.20. In conjuction with M.IP, where/how do I specify to only return items with that software display name AND 10.20. in the IP address field? Thanks for your help on this.
Answered 02/24/2011 by: jmcelvoy
Second Degree Blue Belt

Please log in to comment
0
SELECT M.NAME,
S.DISPLAY_NAME,
M.IP
FROM MACHINE M,
SOFTWARE S,
MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID
AND S.ID = MS.SOFTWARE_ID
AND S.PUBLISHER LIKE '%MICROSOFT%'
ORDER BY M.NAME,
S.DISPLAY_NAME


Will this do?

SELECT M.NAME,
S.DISPLAY_NAME,
M.IP
FROM MACHINE M, SOFTWARE S, MACHINE_SOFTWARE_JT MS
WHERE M.ID = MS.MACHINE_ID AND S.ID = MS.SOFTWARE_ID AND S.PUBLISHER LIKE '%MICROSOFT%' AND M.IP LIKE '10.20.%'
ORDER BY M.NAME, S.DISPLAY_NAME
Answered 03/06/2011 by: wsteo
Senior Yellow Belt

Please log in to comment