Good day all Techs out there

i need a little help regarding an msi report uploading and installing it as a dependency

i need to pull a report to show me (installed and not installed) as i am not very technical with sql, the wizard report not helping me either :-(

this is the script iam using below:

SELECT DISTINCT DATE_FORMAT(MI.CREATED,'%Y/%m/%d') as CREATED_DATE,
                S.DISPLAY_NAME,
                S.DISPLAY_VERSION,

                CASE
                    WHEN MS.MACHINE_ID > 0 THEN 'Installed'
                    WHEN MIA.ATTEMPT_COUNT > 0 THEN CONCAT('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
                    ELSE 'Not Installed'
                END  AS STATUS,
                M.NAME,
                M.IP,
                M.LAST_SYNC,
                M.USER_NAME
FROM   SOFTWARE S
       JOIN MI
         ON ( S.ID = MI.SOFTWARE_ID )
       JOIN MI_LABEL_JT MIL
         ON ( MI.ID = MIL.MI_ID )
       JOIN LABEL L
         ON ( MIL.LABEL_ID = L.ID )
       JOIN MACHINE_LABEL_JT ML
         ON ( L.ID = ML.LABEL_ID )
       JOIN MACHINE M
         ON ( ML.MACHINE_ID = M.ID )
       JOIN SOFTWARE_OS_JT SO
         ON ( SO.SOFTWARE_ID = S.ID
              AND SO.OS_ID = M.OS_ID )
       LEFT JOIN MACHINE_SOFTWARE_JT MS
         ON ( M.ID = MS.MACHINE_ID
              AND MS.SOFTWARE_ID = S.ID )
       LEFT JOIN MI_ATTEMPT MIA
         ON ( MIA.MI_ID = MI.ID
              AND MIA.MACHINE_ID = M.ID )
WHERE DISPLAY_NAME = 'Desktop Agent 9 July 2013'
ORDER  BY S.DISPLAY_NAME,
          S.DISPLAY_VERSION,
          STATUS

Desktop Agent is the msi

your help will be highly appreciated

thanks in advance

Chuck

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

The query will work if the software record it creates in Kace is "Desktop Agent 9 July 2013". Since you are using an "=" it needs to match exactly. Whenever you install the msi on a test machine is this what Kace inventories?

Answered 07/25/2013 by: dugullett
Red Belt

  • thanks for getting back to me so quickly Dugullett

    with is what the msi is installed as and it inventory name does appear in the programs list Desktop Agent 9 July 2013
    • So this report is not working for you? What additional info are you needing in it?
  • yes the report is not working for me "all i need is for it to say Installed and not installed report"

    should one create a custome field in order to get a report for this msi? not sure
    • Is it giving you an error, or just not returning any results?

      Do me a favor and change this line:

      WHERE DISPLAY_NAME = 'Desktop Agent 9 July 2013'

      to this and see if it changes the results:

      WHERE DISPLAY_NAME LIKE '%Desktop Agent%'
  • hi Dugullett
    still nothing
    i forgot to tel you that there is a previous one that does state Desktop Agent

    it is not giving me any results as i am using a previous script and just changing the details to point to the msi basically just adding the name of the msi in the area 'Desktop Agent 9 July 2013'
  • this is just the update to the previous install but what makes this one different is was installed via dependency and ran via script
    i can pull a report to show me that the sript it however run on the machin but comes back with an ID stating that the script did run but i need to know if it did indeed installed.........:-)
    • The reason why that is not working then is because it's looking at the MI tables. Since it was ran through a script it wouldn't exist there. Try this below.

      SELECT DISTINCT M.NAME AS SYSTEM_NAME,
      CASE WHEN S.DISPLAY_NAME ='<SOFTWARE NAME>' THEN 'INSTALLED'
      ELSE 'NOT INSTALLED'
      END AS SOFTWARE
      FROM MACHINE M
      LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON M.ID = MSJT.MACHINE_ID
      LEFT JOIN SOFTWARE S ON MSJT.SOFTWARE_ID = S.ID
      ORDER BY S.DISPLAY_NAME
  • hi Dugullett

    how are you doing
    it does pull a report but under softare it says not installed

    here is what it looks like:
    # System Name Software
    1 8FZY44J NOT INSTALLED
    2 CZC128F30W NOT INSTALLED
    3 CZC1091QRH NOT INSTALLED
    4 CZC128F45H NOT INSTALLED
    5 CZC12001VC NOT INSTALLED
    6 CZC04695C1 NOT INSTALLED
    7 CZC128DV7D NOT INSTALLED
    8 CZC1087VRG1 NOT INSTALLED
    9 CZC128F3TQ NOT INSTALLED
    10 CZC128F2X2 NOT INSTALLED
    11 CZC128F405 NOT INSTALLED
    12 CZC128F2XD NOT INSTALLED
    13 CZC128F2HX NOT INSTALLED
    14 CZC0478ZVD NOT INSTALLED
    15 CZC047CTLK NOT INSTALLED
    16 CZC128F3GT0 NOT INSTALLED
    17 CZC128F2HV NOT INSTALLED
    18 CZC128F2KB NOT INSTALLED
    19 CZC128F48Z NOT INSTALLED
    20 CZC128F2X0 NOT INSTALLED
    21 CZC128F3ZZ NOT INSTALLED
    22 CZC128F3J1 NOT INSTALLED
    23 CZC128F2Z7 NOT INSTALLED
    24 CZC1091P1K NOT INSTALLED
    25 CZC1091R3G NOT INSTALLED
    26 CZC12001SL NOT INSTALLED
    27 CZC128DSZ2 NOT INSTALLED
    28 CZC128DVND NOT INSTALLED
    29 CZC128F2WP1 NOT INSTALLED
    30 CZC128F3QL NOT INSTALLED
    31 CZC20220CK NOT INSTALLED
    32 TRF24000CS NOT INSTALLED
    33 CZC1091NL4 NOT INSTALLED
    34 CZC1091QMD-PC NOT INSTALLED
    35 CZC11100N4 NOT INSTALLED
    36 CZC120027H NOT INSTALLED
    37 CZC128DV4X NOT INSTALLED
    38 CZC128F2G4 NOT INSTALLED
    39 CZC128F38Y NOT INSTALLED
    40 CZC1353R0Y NOT INSTALLED
    41 CZC2143FLR NOT INSTALLED
    42 CZC047CTKN NOT INSTALLED
    43 CZC1087VYS NOT INSTALLED
    44 czc04777px NOT INSTALLED
    45 CZC0470XGT NOT INSTALLED
    46 CZC0478ZVR NOT INSTALLED
    47 czc1087tn3 NOT INSTALLED
    48 CZC0476YKH NOT INSTALLED
    49 CZC04695C2 NOT INSTALLED
    this is what it looks like
    but what i want to see it the software title and it saying not installed and installed
    as this program does show under the installed programs
  • hello Dugullett

    how are you doing

    i did some research apon the above issue and came to an answer what i was looling for

    check it out the below inventory rule:

    Shellcommandtextreturn(cmd.exe /c type c:\windows\system32\install.txt)

    when i pull a report below:

    Title: Desktop Agent 9 July 2013
    Description:
    Category: Burton Deployment
    Server Hostname: kbox.jdg.co.za
    Generated: 08/01/2013 10:52:51

    IP Address System Name Install
    11.2.10.41 TEMP003
    11.54.5.72 poslab1004s
    11.54.5.76 poslab1005s
    11.54.5.118 poslab1004t
    11.208.2.13 SIPHOK
    11.208.2.230 JUDYBASSON
    11.208.8.4 JDGKACELAB
    11.208.8.17 CZC128F3FP hello \nhello \nhello \nhello \nhello \n
    11.208.8.19 CZC120021B hello \nhello \nhello \nhello \nhello \nhello \n
    11.208.8.50 CZC128F48X

    this is what i was looking for as it displays the text file as a title "install" and present what is in the txt file "hello" that it did install

    what puzzells me is that the inventory rule does not work in my other OU,'s and it is the same rule i am using any idea why?

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