List all computers with specific software...by Publisher
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?
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
Answers (9)
Please log in to answer
Posted by:
dchristian
13 years ago
If all your looking for is the machines and the software i think this will work.
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 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_NAMEJust 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%') )))
Posted by:
jmcelvoy
13 years ago
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.
Posted by:
jmcelvoy
13 years ago
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?
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?
Posted by:
dchristian
13 years ago
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.
Posted by:
jmcelvoy
13 years ago
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.
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.
Posted by:
wsteo
13 years ago
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
Posted by:
nird
4 years ago
Hello i need please
Report all software for all computers
And filter when recently used
in the last year
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.