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   [ + ] Show 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.

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
Answer this question or Comment on this question for clarity