/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Blog Posts by jverbosk

Ask a question

John Verbosky - Good-bye Fellow Ninjas!

Hi everyone!

Well it's been an exciting ride, but it is with an extremely heavy heart that I have to announce my departure from KACE due to a workforce reduction by the new owners.  Although my focus for the past several years had been on internal technical training materials, my team was aggressively developing materials that were intended for our customers (you) when the company moved out of Dell and my job was eliminated.  I'm hopeful that my first implementation web-based training course will reach you eventually, although I won't be the one to finish it at this point.

Since all of my fellow ninjas have been so supportive of me and my work over the years, I'm sincerely hoping that you might be able to help me in my current situation - that is, assisting me in my search for a new technical position, where I can continue to help others by doing what I love.  So, if any of my blogs, KACE webinars or Dell Software User Forum training sessions have helped you (and if you wouldn't mind), I would sincerely appreciate it if you would reach out to me on LinkedIn and help me with a recommendation:


Thank you again for all of your support over the years, and I'll be sure to let you know where and when I land.

Have a good one!  ^_^ /

John C. Verbosky
jverbosk@gmail.com
View comments (7)

K1000 Labels - Developing Labels for Hierarchical Organization

Typical disclaimer: I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

This example can be considered an extension of an earlier blog that I wrote about labels, with this one being more focused on the herarchical organization of the labels themselves:

Questions and/or suggestions are welcome, and as usual - hope that helps!  ^_^

John
_____________________________

Developing Labels for Hierarchical Organization

The benefit of using Smart labels is that they will automatically be applied (and removed) from devices as the devices meet (or no longer meet) the specified matching criteria.

All K1000 labels support hierarchies, but planning is both necessary and recommended.  The nice part is that once the hierarchy is defined (through Smart label criteria and label groups), only the lowest-level of the hierarchy needs to be specific – the rest of the hierarchy simply leverages the names of the labels lower in the hierarchical structure.

To clarify, here is an example using locations from very specific (classrooms) to very broad (region).  When configured correctly, devices and their associated labels can be easily viewed at any level of the hierarchy.  And keeping track of hundreds and thousands of labels is entirely possible and relatively easy to do.

Example Scenario

A customer wants to create labels for tracking devices at all locations, from regional down to individual classrooms.  Labels listed on the Labels page should be easy to read and logically grouped.  It should also be possible to easily view all devices associated with labels at any level of the hierarchy.

Note:
The example provided here only includes a minimal sample for each level of the hierarchy for illustrative purposes.  Any actual implementation will typically contain many more objects at the different levels.

Configuration Steps

1)      Develop a naming scheme that assists with identification and organization.  This step is absolutely critical for the label hierarchy to function properly and will help considerably with label and label group creation.

Suggested implementation

  • First, develop a list of the location hierarchies, from most general to most specific. 

For example:

    • Region
    • Country
    • State
    • City
    • Site
    • Building
    • Classroom
  • Second, create a list of the exact locations that correspond to these groups.

For example:

    • Region
      • EMEA
      • North America
    • Country
      • Belgium
      • Germany
    • State
      • Bavaria
      • Saxony
    • City
      • Ingolstadt
      • Munich
    • Site
      • Munich University of Applied Sciences
      • Technische Universitaet Muenchen
    • Building
      • A
      • T
    • Classroom
      • Room 101
      • Room 102

2)     Develop label groups which correspond to the different location hierarchies.

Note:
K1000 label groups can only be nested to a single level.

For example, this approach (which we will use below in our example) works fine:

Top-Level Label GroupBottom-Level Label GroupLabel
LocationLocation-RegionLocation-Region-EMEA
Location
Location-CountryLocation-Country-Germany
Location
Location-StateLocation-State-Bavaria
Location
Location-CityLocation-City-Munich
However, this type of approach will not work:

Label GroupLabel GroupLabel GroupLabel GroupLabel GroupLabel
LocationRegionCountryStateCityMunich
Attempting to nest label groups in more than one level will prevent the label groups from being displayed and functioning properly in the View By menu on the Devices page.

In the example below, the different location levels are associated with a top-level Location label group.  Although it does require some administrative effort to plan and design, the results will be easy to work with.
Label Groups
  • Location
    • Location-Region
    • Location-Country
    • Location-State
    • Location-City
    • Location-Site
    • Location-Building
    • Location-Classroom
Once the label groups have been determined:

  • Use the New Label Group option in the Choose Action menu of the Labels page to create them.
  • Next, use the Apply Label Groups option in the Choose Action menu of the Labels page to associate the bottom-level label groups with the top-level Location label group.

In the Field:
Prefixing the bottom-level label groups with the top-level label group's name will help considerably with the organization of the Labels page, as we will see later.

3)      Develop Smart labels that target these locations by using corresponding criteria.

Note:
When developing Smart labels, start at the lowest level of the hierarchy (the classroom level, in this example) and then move up in the hierarchy by using the lower-level labels for the next level label's criteria (Label Names = ____).  Prefixing label names is also recommended.
  • Location-Classroom-T-Room 101
            Example Smart label criteria based upon IP addresses:
                IP Address > 192.168.77.0  *AND*
                IP Address < 192.168.77.51

  • Location-Classroom-T-Room 201
            Example Smart label criteria based up IP addresses:
                IP Address > 192.168.77.50  *AND*
                IP Address < 192.168.77.101

Note:
Including a building identifier (“T” in this example) along with the room number will help differentiate identically-named classrooms.
In the Field:
Aside from the previous planning and hierarchy development steps, determining what to use for the Smart label criteria for the lowest level of the hierarchy is the most critical aspect of this entire configuration.

Some suggested approaches include:

  • If computers are logically separated by VLANs (or similar), grouping by IP address will work fine (as in this example).  
  • If the computers’ names designate location (for example, ClassA-Comp12), use the unique part of the location aspect of the name as the criteria (for example: System Name begins with ClassA).
  • If Active Directory attributes specify the location of computers, use LDAP labels (instead of Smart labels) to target the specific LDAP attribute.
In the event that there is nothing about the computer (IP address, naming convention, LDAP attribute) that can be leveraged, computers will either need to be manually assigned to the lowest hierarchy label (which in this specific situation, would need to be a manual label).  

To ease the administrative burden in the future (and to avoid resorting to manual labels), a breadcrumb can be placed on computers (in the form of a file or registry entry) via K1000 scripts that could be leveraged by a K1000 custom inventory rule (which could then be used as the Smart label criteria).  The target computers in the K1000 scripts would still need to be specified manually, but label application could still be automated as outlined here.
  • Location-Building-T
            Example Smart label criteria based upon Classroom labels:
                Label Names = Location-Classroom-T-Room 101  *OR*
                Label Names = Location-Classroom-T-Room 201

  • Location-Site-Munich University of Applied Sciences
            Example Smart label criteria based upon Building labels:
                Label Names = Location-Building-A  *OR*
                Label Names = Location-Building-T

  • Location-City-Munich
            Example Smart label criteria based upon Site labels:
                Label Names = Location-Site-Munich University of Applied Sciences  *OR*
                Label Names = Location-Site-Technische Universitaet Muenchen

  • Location-State-Bavaria
            Example Smart label criteria based upon City labels:
                Label Names = Location-City-Ingolstadt  *OR*
                Label Names = Location-City-Munich

  • Location-Country-Germany
            Example Smart label criteria based upon State labels:
                Label Names = Location-State-Bavaria  *OR*
                Label Names = Location-State-Saxony

  • Location-Region-EMEA
            Example Smart label criteria based upon Country labels:
                Label Names = Location-Country-Belgium  *OR*
                Label Names = Location-Country-Germany


4)      Associate the Smart labels created in step 3 with the label groups created in step 2.

      First, associate the labels with the bottom-level label groups:

  • Location-Classroom
    • Location-Classroom-T-Room 101
    • Location-Classroom-T-Room 201
  • Location-Building
    • Location-Building-A
    • Location-Building-T
  • Location-Site
    • Location-Site-Munich University of Applied Sciences
    • Location-Site-Technische Universitaet Muenchen
  • Location-City
    • Location-City-Ingolstadt
    • Location-City-Munich
  • Location-State
    • Location-State-Bavaria
    • Location-State-Saxony
  • Location-Country
    • Location-Country-Belgium
    • Location-Country-Germany
  • Location-Region
    • Location-Region-EMEA
    • Location-Region-North America

      Second, associate the bottom-level label groups with the top-level label group:

  • Location
    • Location-Building
    • Location-City
    • Location-Classroom
    • Location-Country
    • Location-Region
    • Location-Site
    • Location-State

5)      As devices perform inventory, they will be associated with the appropriate bottom-level Smart label (Location-Classroom-____) and will then "roll up" into the higher-level Smart labels.

For example, a device with an IP address of 192.168.77.66 will automatically have the following Smart labels applied:

  • Location-Classroom-T-Room 201
  • Location-Building-T
  • Location-Site-Munich University of Applied Sciences
  • Location-City-Munich
  • Location-State-Bavaria
  • Location-Country-Germany
  • Location-Region-EMEA

With this type of naming convention and label grouping structure in place, the organization of the Labels page is intuitive.  Associated label groups are indicated in brackets following the label (or label group) name.  For example, the Location-City-Munich label belongs to the bottom-level Location-City label group, while the Location-City label group belongs to the top-level Location label group.

1iwLgi.png
Viewing Associated Labels and Devices

Going the Label Group Detail page for a label group and expanding its Labeled Items section allows you to quickly view all member labels.

CCovxG.png

Similarly, opening the Label Detail page for a label will list all of the devices associated with that label.

09ct7t.png
For viewing devices that belong to labels on the Devices page, use the View By menu and focus on the desired label.

gOjjq2.png
...
HAaCC5.png
Devices can be viewed for all levels of the hierarchy, due to the Smart label criteria used for higher-level labels.

dUlM9d.png

Conclusion

Although setting up a hierarchal labeling scheme does require planning and time to initially configure, once these steps have been completed device organization is completely automated.

In summary, it is worth investing the time up front as these labels can then be used to drive the automation of tasks (software/script/patch deployment and so on) throughout the rest of the K1000, and reporting on discrete groups of devices (using the K1000’s Reporting module) becomes extremely easy as you only need to filter on the desired label name.


View comments (2)

K1000 Agent Uninstall Script

Typical disclaimer:  I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate. 

Questions and/or suggestions are welcome, and as usual - hope that helps!  ^_^

John

_____________________________

The following K1000 script can be used to completely remove the K1000 Agent from Windows devices.  It is architecture agnostic and will work equally well for x86 and x64 versions of Windows.

Successful testing was completed against the following platforms:

  • Windows 7 Professional SP1 x86
  • Windows 7 Enterprise SP1 x64
  • Windows 8 Enterprise x86

K1000 Script Configuration


Name
Agent Uninstall And Cleanup
Enabled
Yes
Type
Online KScript (although Offline should work as well)
Operating Systems
Microsoft Windows
Windows Run As
Local System

Task 1 Configuration


Verify
Verify the service "AMPAgent" is running.
On Success
Run the batch file "AgentUninstallAndCleanup" with params "".


AgentUninstallAndCleanup Batch File (Retain KUID)

The following batch file works in several steps, but basically it does the following:

1) Creates a cleanup batch file to remove any remaining Agent directories after the Agent is uninstalled, as well as the cleanup batch file itself.  This is placed in the C:\Windows\Temp directory, where it is executed by a scheduled task and then is removed once it has been run.

2) Creates a scheduled task to run the batch file created in step 1.  This scheduled task executes 2 minutes after creation.  If the scheduled task already exists, it is removed and recreated.

3) Runs the standard "AMPTools uninstall" command to uninstall the Agent.


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a batch file in C:\Windows\Temp to remove any remaining Agent directories after the Agent is uninstalled::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
echo rmdir /s/q C:\ProgramData\Dell\KACE >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files (x86)\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo del C:\Windows\Temp\AgentCleanup.bat /f/s/q >> C:\Windows\Temp\AgentCleanup.bat
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a scheduled task to run the AgentCleanup batch file (delete & recreate if it already exists)                   ::
::The scheduled task will run 2 minutes after being created, which should be enough time for the Agent to be uninstalled::
::This can be adjusted in the "set /A minute+=2" line - just change the 2 to the desired length of time (in minutes)    ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
cls
schtasks /query > doh
findstr /B /I "AgentCleanup" doh >nul
if %errorlevel%==0  goto :delete
goto :create
  
:delete
schtasks /delete /tn "AgentCleanup" /f >nul
  
:create
setlocal
  
set hour=%time:~0,2%
set minute=%time:~3,2%
set /A minute+=2
  
if %minute% GTR 59 (
 set /A minute-=60
 set /A hour+=1
)
  
if %hour%==24 set hour=00
if "%hour:~0,1%"==" " set hour=0%hour:~1,1%
if "%hour:~1,1%"=="" set hour=0%hour%
if "%minute:~1,1%"=="" set minute=0%minute%
  
set tasktime=%hour%:%minute%
  
schtasks /ru "SYSTEM" /create /sc ONCE /tn AgentCleanup /tr C:\Windows\Temp\AgentCleanup.bat /st %tasktime% >nul
del doh >nul
:::::::::::::::::::::::
::Uninstall the Agent::
:::::::::::::::::::::::
if /i "%programfiles%"=="%programfiles(x86)%" ("C:\Program Files (x86)\Dell\KACE\AMPTools.exe" uninstall) else ("C:\Program Files\Dell\KACE\AMPTools.exe" uninstall)
 
:::::::::::::::::
::2015/06/03-jv::
:::::::::::::::::

AgentUninstallAndCleanup Batch File (Remove KUID - Agent 6.2 and below)

The following batch file works the same as the previous, but includes a command to remove all references to the KUID in the registry.  This approach is necessary if the Agent is version 6.2 or lower.


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a batch file in C:\Windows\Temp to remove any remaining Agent directories after the Agent is uninstalled::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
echo rmdir /s/q C:\ProgramData\Dell\KACE >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files (x86)\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo del C:\Windows\Temp\AgentCleanup.bat /f/s/q >> C:\Windows\Temp\AgentCleanup.bat
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a scheduled task to run the AgentCleanup batch file (delete & recreate if it already exists)                   ::
::The scheduled task will run 2 minutes after being created, which should be enough time for the Agent to be uninstalled::
::This can be adjusted in the "set /A minute+=2" line - just change the 2 to the desired length of time (in minutes)    ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
cls
schtasks /query > doh
findstr /B /I "AgentCleanup" doh >nul
if %errorlevel%==0  goto :delete
goto :create
  
:delete
schtasks /delete /tn "AgentCleanup" /f >nul
  
:create
setlocal
  
set hour=%time:~0,2%
set minute=%time:~3,2%
set /A minute+=2
  
if %minute% GTR 59 (
 set /A minute-=60
 set /A hour+=1
)
  
if %hour%==24 set hour=00
if "%hour:~0,1%"==" " set hour=0%hour:~1,1%
if "%hour:~1,1%"=="" set hour=0%hour%
if "%minute:~1,1%"=="" set minute=0%minute%
  
set tasktime=%hour%:%minute%
  
schtasks /ru "SYSTEM" /create /sc ONCE /tn AgentCleanup /tr C:\Windows\Temp\AgentCleanup.bat /st %tasktime% >nul
del doh >nul
 
:::::::::::::::::::::::
::Uninstall the Agent::
:::::::::::::::::::::::
if /i "%programfiles%"=="%programfiles(x86)%" ("C:\Program Files (x86)\Dell\KACE\AMPTools.exe" uninstall) else ("C:\Program Files\Dell\KACE\AMPTools.exe" uninstall)
 
::::::::::::::::::::
::Registry Cleanup::
::::::::::::::::::::
if /i "%programfiles%"=="%programfiles(x86)%" (reg delete HKLM\SOFTWARE\Wow6432Node\Dell\Kace /f) else (reg delete HKLM\SOFTWARE\Dell\Kace /f)
 
:::::::::::::::::
::2015/06/03-jv::
:::::::::::::::::

AgentUninstallAndCleanup Batch File (Remove KUID - Agent 6.3 and above)

The following batch file works the same as the previous, but uses the "all-kuid" option (available starting in version 6.3 of the Agent) to remove all references to the KUID in the registry.


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a batch file in C:\Windows\Temp to remove any remaining Agent directories after the Agent is uninstalled::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
echo rmdir /s/q C:\ProgramData\Dell\KACE >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo rmdir /s/q "C:\Program Files (x86)\Dell\KACE" >> C:\Windows\Temp\AgentCleanup.bat
echo del C:\Windows\Temp\AgentCleanup.bat /f/s/q >> C:\Windows\Temp\AgentCleanup.bat
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::Create a scheduled task to run the AgentCleanup batch file (delete & recreate if it already exists)                   ::
::The scheduled task will run 2 minutes after being created, which should be enough time for the Agent to be uninstalled::
::This can be adjusted in the "set /A minute+=2" line - just change the 2 to the desired length of time (in minutes)    ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
cls
schtasks /query > doh
findstr /B /I "AgentCleanup" doh >nul
if %errorlevel%==0  goto :delete
goto :create
  
:delete
schtasks /delete /tn "AgentCleanup" /f >nul
  
:create
setlocal
  
set hour=%time:~0,2%
set minute=%time:~3,2%
set /A minute+=2
  
if %minute% GTR 59 (
 set /A minute-=60
 set /A hour+=1
)
  
if %hour%==24 set hour=00
if "%hour:~0,1%"==" " set hour=0%hour:~1,1%
if "%hour:~1,1%"=="" set hour=0%hour%
if "%minute:~1,1%"=="" set minute=0%minute%
  
set tasktime=%hour%:%minute%
  
schtasks /ru "SYSTEM" /create /sc ONCE /tn AgentCleanup /tr C:\Windows\Temp\AgentCleanup.bat /st %tasktime% >nul
del doh >nul
 
:::::::::::::::::::::::
::Uninstall the Agent::
:::::::::::::::::::::::
if /i "%programfiles%"=="%programfiles(x86)%" ("C:\Program Files (x86)\Dell\KACE\AMPTools.exe" uninstall all-kuid) else ("C:\Program Files\Dell\KACE\AMPTools.exe" uninstall all-kuid)
 
:::::::::::::::::
::2015/06/03-jv::
:::::::::::::::::

Notes

1) The AgentCleanup scheduled task remains after the K1000 script runs.  However, this should not be a major issue since it only runs once and the batch file it runs (AgentCleanup.bat) is deleted after execution.

2) Carriage returns will break some of the longer statements (if /i ... in the Uninstall the Agent and Registry Cleanup sections) - these appear on multiple lines here due to text wrapping.  Copying and pasting into a text editor like Notepad should result in these statements being placed on a single line.

View comments (2)

K1000 Reports - Reports to List Metering Information for Standalone Applications and Applications within a Suite

K1000 Reports - Reports to List Metering Information for Standalone Applications and Applications within a Suite

Typical disclaimer:  I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

The SQL queries below will hopefully fill in some gaps in the (version 5.5) K1000's built-in metering reports, as well as the metering reports which are possible to create using the report wizard.  These queries are basically tweaks to the reports created by the report wizard.

_________________________________

The report wizard in version 5.5 of the K1000 allows for the creation of metering reports. However, there are limitations to each of the topics within the wizard:


Software Catalog - Metering By Applications

Reports created using this Report Topic list useful metering information for each metered application, but only include standalone applications. Applications within a suite (such as components of Microsoft Office - Excel, Word, etc.), do not appear in this report. Nor is usage broken down per machine - this report simply advises on the total usage (Hours Used, Launches) and the number of machines that were metered.


Software Catalog - Metering By Machines

Reports created using this Report Topic are more comprehensive in regards to including all metered application. However, applications within a suite are only indicated by a version number and are not explicitly identified by title.

_________________________________

SQL Queries

_________________________________

Metering Per Machine

Columns:
Machine Name
Product Name
Product Version
Install Count
Hours Used
Launches
Last Launched

SQL Query:

SELECT M.NAME AS MACHINE,

SVTS.NAME AS PRODUCT_NAME,

SAM_METER_DATA.VERSION,

(SELECT COUNT(DISTINCT MACHINE_ID)

 FROM SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE X

 WHERE X.ID = SVMDS_APPS.ID OR X.ID = SVMDS_SUITES.ID) AS INSTALL_COUNT,

ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS HOURS_USED,

SUM(SAM_METER_DATA.LAUNCHES) AS LAUNCHES,

MAX(SAM_METER_DATA.START) AS LAST_LAUNCHED

FROM SAM_METER_DATA

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_APPS

  ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVMDS_APPS.ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_APPS.MACHINE_ID

  AND SAM_METER_DATA.VERSION = SVMDS_APPS.VERSION

LEFT JOIN CATALOG.TITLED_SUITE_APPLICATION_JT TSAJT

  ON TSAJT.TITLED_APPLICATION_ID = SAM_METER_DATA.TITLED_APPLICATION_ID

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_SUITES

 ON SVMDS_SUITES.ID = TSAJT.TITLED_SUITE_ID

 AND SAM_METER_DATA.MACHINE_ID = SVMDS_SUITES.MACHINE_ID

LEFT JOIN SAM_VIEW_TITLED_SOFTWARE SVTS

 ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVTS.ID

JOIN MACHINE M ON M.ID = SVMDS_APPS.MACHINE_ID OR M.ID = SVMDS_SUITES.MACHINE_ID

GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID,SAM_METER_DATA.MACHINE_ID

ORDER BY M.NAME, PRODUCT_NAME

_________________________________

Metering Per Application

Columns:
Product Name
Product Version
Install Count
Total Hours Used
Total Launches

SQL Query:

SELECT SVTS.NAME AS PRODUCT_NAME,

SAM_METER_DATA.VERSION,

(SELECT COUNT(DISTINCT MACHINE_ID) from SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE X

 WHERE X.ID = SVMDS_APPS.ID OR X.ID = SVMDS_SUITES.ID) AS INSTALL_COUNT,

ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS TOTAL_HOURS_USED,

SUM(SAM_METER_DATA.LAUNCHES) AS TOTAL_LAUNCHES

FROM SAM_METER_DATA 

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_APPS

  ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVMDS_APPS.ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_APPS.MACHINE_ID

  AND SAM_METER_DATA.VERSION = SVMDS_APPS.VERSION

LEFT JOIN CATALOG.TITLED_SUITE_APPLICATION_JT TSAJT

  ON TSAJT.TITLED_APPLICATION_ID = SAM_METER_DATA.TITLED_APPLICATION_ID

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_SUITES

  ON SVMDS_SUITES.ID = TSAJT.TITLED_SUITE_ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_SUITES.MACHINE_ID

LEFT JOIN SAM_VIEW_TITLED_SOFTWARE SVTS

 ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVTS.ID

JOIN MACHINE M ON M.ID = SVMDS_APPS.MACHINE_ID OR M.ID = SVMDS_SUITES.MACHINE_ID

GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID

ORDER BY PRODUCT_NAME
View comments (4)

K1000 Report - Patching - Vendor severity with machine count and completion rates

Typical disclaimer: I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

I developed a "CIO level" patching report this morning and am sharing it, in case it might be useful to any other kboxers out there.  ^_^

Hope that helps!

John

___________________________________

*Title*
Vendor severity with machine count and completion rates

*Description*
Lists all active patches by vendor severity, with an applicable machine count, total number patched, total number unpatched and percent patched for each severity level. Keep in mind is that not all patches apply to all machines, so the machine count will vary for each severity level.

*Select Statement*

SELECT (IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING,
ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS COMPLIANCE,
COUNT(MS.MACHINE_ID) AS APPLICABLE,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM((MS.DEPLOY_ATTEMPT_COUNT >= 3 and MS.STATUS != 'PATCHED')
OR MS.STATUS = 'FAIL' or MS.DEPLOY_STATUS = 'FAIL') AS ERROR
FROM KBSYS.PATCHLINK_PATCH P
LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID = P.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON MS.PATCHUID = P.UID
JOIN PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID = P.UID
WHERE V.ATTR = 'MaximumSeverityRating'
AND V.ATTRVALUE not rlike '8211|recommended'
AND PS.STATUS = 0
GROUP BY VENDOR_RATING

 *Example Output*

View comments (5)
Showing 1 - 5 of 41 results

Top Contributors

Talk About Remote Control