I have a computer/user report  that i want to combine with my software reports as additional columns to it.  How can I combine the three together?

 

Computer report:

 SELECT MACHINE.NAME AS SYSTEM_NAME,SYSTEM_DESCRIPTION,BIOS_SERIAL_NUMBER,CS_MODEL,USER_LOGGED,LAST_SYNC,OS_NAME FROM MACHINE    ORDER BY SYSTEM_NAME
 
 
Software report1:
select DISPLAY_NAME as 'Software Title',
DISPLAY_VERSION as 'Version',
MACHINE.USER_FULLNAME as 'USER',
MACHINE.NAME as 'Computer'
from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE) 
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID 
and not IS_PATCH
and DISPLAY_NAME LIKE '%microsoft% %project%'
and DISPLAY_NAME NOT LIKE '%MUI%'
and DISPLAY_NAME NOT LIKE '%Server%'
order by DISPLAY_NAME
 
Software report2:
select DISPLAY_NAME as 'Software Title',
DISPLAY_VERSION as 'Version',
MACHINE.USER_FULLNAME as 'USER',
MACHINE.NAME as 'Computer'
from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE) 
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID 
and not IS_PATCH
and DISPLAY_NAME LIKE '%microsoft office% %2010%'
and DISPLAY_NAME NOT LIKE '%primary%'
and DISPLAY_NAME NOT LIKE '%database%'
and DISPLAY_NAME NOT LIKE '%access%'
and DISPLAY_NAME NOT LIKE '%word%'
and DISPLAY_NAME NOT LIKE '%excel%'
and DISPLAY_NAME NOT LIKE '%outlook%'
and DISPLAY_NAME NOT LIKE '%infopath%'
and DISPLAY_NAME NOT LIKE '%groove%'
and DISPLAY_NAME NOT LIKE '%powerpoint%'
and DISPLAY_NAME NOT LIKE '%components%'
and DISPLAY_NAME NOT LIKE '%language%'
and DISPLAY_NAME NOT LIKE '%meeting%'
and DISPLAY_NAME NOT LIKE '%mui%'
and DISPLAY_NAME NOT LIKE '%onenote%'
and DISPLAY_NAME NOT LIKE '%project%'
and DISPLAY_NAME NOT LIKE '%proof%'
and DISPLAY_NAME NOT LIKE '%sharepoint%'
and DISPLAY_NAME NOT LIKE '%single%'
and DISPLAY_NAME NOT LIKE '%visio%'
order by DISPLAY_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

It looks like you already have the MACHINE table joined in the query. You just need to add the fields you need to the SELECT portion of the query. 

select DISPLAY_NAME as 'Software Title',
DISPLAY_VERSION as 'Version',
MACHINE.USER_FULLNAME as 'USER',
MACHINE.NAME as 'Computer', MACHINE.SYSTEM_DESCRIPTION,
MACHINE.BIOS_SERIAL_NUMBER,MACHINE.CS_MODEL,MACHINE.USER_LOGGED,
MACHINE.LAST_SYNC,MACHINE.OS_NAME

 

If I could also suggest using RLIKE or taking the % off of the beginning of the Display Name. It's been my experience leaving it like that takes a long time to return results. In my case with 90,000+ records the query would be looking for anything with %access%, %word%, or %excel%. You can clean this up some by changing it to 'Microsoft Access%', 'Microsoft Word%, or 'Microsoft Excel%'. For me doing it this way works better.

Answered 05/29/2013 by: dugullett
Red Belt

Please log in to comment
0

bump =/

Answered 05/28/2013 by: Papo28
Orange Belt

Please log in to comment
Answer this question or Comment on this question for clarity