Script Results report

Question

I need a sql query that finds the script of a specific name(we can say "testscript" for now) and then gets the computer name and the computer discription that "testscipt" ran on. I only want the comptuters that "testscript" ran on. below is what i have so far that i got from Kace support. it would be even better if i could add a custom inventory field to it as well.

Select K.NAME,
 CASE 
     WHEN KL.STATUS IN (1,2)
         THEN 'SUCCESS'
     WHEN KL.STATUS IN (3.4)
         THEN 'FAILURE'
     ELSE
         'UNKNOWN'
 END AS STATUS,
 START_TIME AS SCRIPT_RUN, M.NAME
 FROM KBOT_LOG KL
 INNER JOIN KBOT K ON
 K.ID = KL.KBOT_ID AND K.TYPE != 'SYSTEM'
 INNER JOIN MACHINE M ON
 M.ID = KL.MACHINE_ID
 order by 1,2,3,4

thanks in advanced!

Answer

Something like this?
 SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME 
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K 
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M 
          ON M.ID = KL.MACHINE_ID
 WHERE K.NAME = 'TestScript'
 ORDER  BY 1, 
           2, 
           3, 
           4
 
 What fields where you looking to add?

Answer

What happens when u run this?
 
 SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME,
        MCI.STR_FIELD_VALUE AS CUSTOM_FIELD
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M
          ON M.ID = KL.MACHINE_ID
        INNER JOIN MACHINE_CUSTOM_INVENTORY MCI
          ON M.ID = MCI.ID
 WHERE K.NAME = 'TESTSCRIPT'
 ORDER  BY 1,
           2, 
           3, 
           4

Answer

hmm.... (Scratches head)
 
 Does this give results?
 SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME,
        MCI.STR_FIELD_VALUE AS CUSTOM_FIELD
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M
          ON M.ID = KL.MACHINE_ID
        LEFT JOIN MACHINE_CUSTOM_INVENTORY MCI
          ON M.ID = MCI.ID
 /* WHERE  K.NAME = 'TESTSCRIPT' */
 ORDER  BY 1,
           2, 
           3, 
           4

Answer

Thank you so much for the quick reply. i do need to add a custom inventory field to it. i have the query for it but dont know how to combine it. the query is below.

SELECT (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=70888) AS MACHINE_CUSTOM_INVENTORY_0_70888 FROM MACHINE

Answer

SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME,
        MCI.STR_FIELD_VALUE AS CUSTOM_FIELD
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M
          ON M.ID = KL.MACHINE_ID
        INNER JOIN MACHINE_CUSTOM_INVENTORY MCI
          ON M.ID = MCI.ID
 WHERE K.NAME = 'TESTSCRIPT'
   AND MCI.SOFTWARE_ID = 70888
 ORDER  BY 1,
           2, 
           3, 
           4

Answer

that give no results.

Answer

i need that as a column if possible.

Answer

nothing

Answer

It is now working. Thanks so much.

Answer

If that last query works then it begs the question:  why do you want the custom field if there is no data in it?  Are you looking to do something in future with it?  
 
 If you are able perhaps you could post your sample output so future readers could see if this is something they would want?

Answer

Ok, what had happened was your second post fixed my report. the one machine that i had ran the query for did not have the custom field because it was 64bit and the custom field was only for 32 bit. I added another custom field that worked for 64 bit and now it shows the field.

Answer

Guys - I need this same SQL script to run (minus the custom field). So, I've used this - but when I hit "preview", I'm getting a "caught invalid xml string" error. Any ideas? (I'm creating this from classic reports SQL)

Select 'SMART Notebook Shortcuts',
 CASE 
     WHEN KL.STATUS IN (1,2)
         THEN 'SUCCESS'
     WHEN KL.STATUS IN (3.4)
         THEN 'FAILURE'
     ELSE
         'UNKNOWN'
 END AS STATUS,
 START_TIME AS SCRIPT_RUN, M.NAME
 FROM KBOT_LOG KL
 INNER JOIN KBOT K ON
 K.ID = KL.KBOT_ID AND K.TYPE != 'SYSTEM'
 INNER JOIN MACHINE M ON
 M.ID = KL.MACHINE_ID
 order by 1,2,3,4

Answer

Actually, I just ran that script and generated a PDF - it literally shot out 2000 pages of reports. It's like every check-in or something - seconds apart. We have only ran this script on a about 200 machines as "run now". 
 
 Any ideas?

Answer

You need to filter on a script.
 
 Right now it looks like your returning the results from all the scripts.

Answer

What exactly do I need to put in to run reports on specific scripts? I'm a noob when it comes to SQL Queries, but if I just need to edit one or two lines with script name/info, it shouldn't be hard. My script name is "SMART Notebook Shortcuts"

Answer

try this

SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME 
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K 
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M 
          ON M.ID = KL.MACHINE_ID
 WHERE K.NAME = 'SMART Notebook Shortcuts'
 ORDER  BY 1, 
           2, 
           3, 
           4

Answer

Tried that instead, and got this:
 
 Error Running Report
 
 Exception while running report. Unknown column name : SMART Notebook Shortcuts

Answer

sfigg,
 
 Can you delete the script and try again.
 
 The SQL from mramsdell should work as long as you script name is "SMART Notebook Shortcuts"

Answer

I deleted the report, then made a new classic SQL report. I copied it into the SQL field again, and this is what I got when I saved it and clicked PDF (am I doing that right? this is my first report I've run this way):

Error Running Report
 
 Exception while running report. net.sf.jasperreports.engine.JRException: Duplicate declaration of field : NAME

Answer

have you tried running this under just reports and not classic reports? it does not have PDF but you can do HTML.

Answer

Looks like you need to create an alias because it uses "name" twice. Try this.

SELECT K.NAME,
        CASE 
          WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
          WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
          ELSE 'UNKNOWN' 
        END        AS STATUS, 
        START_TIME AS SCRIPT_RUN, 
        M.NAME MACHINE_NAME
 FROM   KBOT_LOG KL 
        INNER JOIN KBOT K 
          ON K.ID = KL.KBOT_ID 
             AND K.TYPE != 'SYSTEM' 
        INNER JOIN MACHINE M 
          ON M.ID = KL.MACHINE_ID
 WHERE K.NAME = 'SMART Notebook Shortcuts'
 ORDER  BY 1, 
           2, 
           3, 
           4

Answer

Seems to work as a regular report instead of classic! Thanks!
 
 Now just to find some other cool custom SQL reports to run :)

Answer

the report above will work in classic if you need PDF.

Answer

It's not working for me under classic reporting  but works under regular reports.

Answer

is it still giving you the same error?

Answer

Yes, it is. Regular reports works fine - but a PDF would be nice so that I could email it to people.

Answer

does it work when you preview? If it does, are the fields correct?

Answer

Doesn't work when I hit preview either. I'm just copying the same script from above - which works in regular reports but just not custom. I'm not big on SQL, so I apologize for not being able to really troubleshoot further than what we're kind of already listed here. 
 
 Preview shows "caught invalid xml string"

Answer

can you post the XML Report Layout? maybe i can compare it to mine and find the issue.

Answer

Sure. Here you go (not the smart notebook script - a different one which we're using today)

Answer

try this

<?xml version="1.0" encoding="UTF-8"?>
 
 <!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
 <jasperReport name="landscape" 
 	columnCount="1" 
 	printOrder="Vertical" 
 	orientation="Portrait" 
 	pageWidth="842" 
 	pageHeight="595" 
 	columnWidth="782" 
 	columnSpacing="0" 
 	leftMargin="30" 
 	rightMargin="30" 
 	topMargin="20" 
 	bottomMargin="20" 
 	whenNoDataType="AllSectionsNoDetail" 
 	isTitleNewPage="false" 
 	isSummaryNewPage="false">
 	<property name="ireport.scriptlethandling" value="0"/>
 	<property name="ireport.encoding" value="UTF-8"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
 	<style name="GroupLabel" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 	<style name="GroupHeader" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">	
 		<box leftPadding="3"/>
 	</style>
 	<style name="GroupFooter" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#e5e5e5" backcolor="#e5e5e5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
 		<box leftPadding="3"/>
 	</style>	
 	<style name="ColumnHeaderFooter" mode="Transparent" isDefault="false" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
 		<box leftPadding="3"/>
 	</style>
 	<style name="ColumnHeaderPanel" isDefault="false" mode="Opaque" forecolor="#FFFFFF" backcolor="#006699" fill="Solid" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 	<style name="GroupHeaderPanel" isDefault="false" mode="Opaque" forecolor="#FFFFFF" backcolor="#999999" fill="Solid" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 	<style name="ReportHeader" mode="Transparent" forecolor="#000000" isDefault="false" fontName="Arial" fontSize="16" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
 		<box/>
 	</style>
 	<style name="Detail" mode="Transparent" forecolor="#000000" isDefault="false" fontName="Arial" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
 		<box/>
 	</style>	
 	<style name="detail_small" isDefault="false" fontName="Arial" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 	<style name="detail_large" isDefault="false" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 	<style name="columnLabel" isDefault="false" forecolor="#000000" fontName="Arial" fontSize="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" isPdfEmbedded="false"/>
 	
 	<queryString><![CDATA[SELECT K.NAME,
       CASE 
         WHEN KL.STATUS IN ( 1, 2 ) THEN 'SUCCESS' 
         WHEN KL.STATUS IN ( 3.4 ) THEN 'FAILURE' 
         ELSE 'UNKNOWN' 
       END        AS STATUS, 
       START_TIME AS SCRIPT_RUN, 
       M.NAME 
 FROM   KBOT_LOG KL 
       INNER JOIN KBOT K 
         ON K.ID = KL.KBOT_ID 
            AND K.TYPE != 'SYSTEM' 
       INNER JOIN MACHINE M 
         ON M.ID = KL.MACHINE_ID
 WHERE K.NAME = 'CoWriter 6.0 '
 ORDER  BY 1, 
          2, 
          3, 
          4]]></queryString>

Script Results report

Answers (31)

Posted by:

View more:

Related Questions

Related Links

Related Posts

ITNinja

Script Results report

Answers (31)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share