Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Highlighted
Absent Member.. monnewbie3 Absent Member..
Absent Member..
765 views

SQL for Software Utilization report

Jump to solution

Hey Everyone,

 

I'm trying to put together a report for software utilization, but I find the DDMI Schema documentation to not be very helpful in terms of linking.

 

I was wondering if anyone had some SQL I could look at for a Software Util. report?

 

I grouped on the Device ID to eliminate the dups, but I'm not sure if that is the correct way of doing it.

I've also noticed for some reason there are MANY device utilization stats missing from this report, and yes the system is configured for util data.

 

Here's an example of a quick report I wrote up for Wordperfect Office 2002, however there are duplicates for some reason (some have more then one usage, one with 0 hours, and one with a value).

 

 

 

Select XXXXXX

FROM    (   (   (   (   aggregate.device device
                       JOIN
                          aggregate.softwareutilization softwareutilization
                       ON (device.Device_ID = softwareutilization.Device_ID))
                   JOIN
                      aggregate.version version
                   ON (softwareutilization.Version_ID = version.Version_ID))
               JOIN
                  aggregate.`release` `release`
               ON (version.Release_ID = `release`.Release_ID))
           JOIN
              aggregate.application application
           ON (application.Application_ID = `release`.Application_ID))
       JOIN
          aggregate.company company
       ON (application.Company_ID = company.Company_ID)
 WHERE     (company.Company_Name = 'Corel')
       AND (application.Application_Name = 'WordPerfect Office')

 

Thanks,

 

-M

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Jeff Sikorski Absent Member.
Absent Member.

Re: SQL for Software Utilization report

Jump to solution

 

The user_id doesnt HAVE to be set to 1... but I think that means "All Users".  DDMI Utilization actually captures utilization PER USER.  99% of the time I imagine it's 1 user per machine.  It's really up to you to decide how you want to do it.

 

So lets say my computer has just 1 user, me. DDMI would capture 2 things:
#1)  All Users

#2)  Jeff

 

In this case, #1 and #2 are the same... so it doesnt matter which one you pick right?  All Users = Jeff.  So technically you could write the query and use "#2"... HOWEVER... if you are running a query on 20,000 devices, and want to use #2... keep in mind that there might be a device or two that has:
#1)  All Users

#2)  Jeff

#3)  Bob

#4)  Sue

#5)  Mary

 

If you ran the same query and only grabbed #2... you would be missing out on #3, 4, 5.  You can probably write some advanced queries to get all of it separate if you want.

 

So keeping it with "All Users" will get the entire machine, and 'doesnt care' about users.  If you DO have to care about multiple users on the same machine, it's going to be a nightmare to administer and I feel bad for you 🙂

 

 

 

I'm not sure what you mean by " Why are there blank entries for the same application? (Utilization module not enabled?)"   I'd have to seea  screen shot of what you are talking about... but it could be a few different things.

 

#1)  When you run a query, lets say a new person started @ your company today and just got his laptop, but DDMI already inventoried it.  So his data might show blank when someone who has been around for 6 months+ will have good data

 

#2)  Really check out the footprint.  Sometimes HP makes a mistake with their footprints and has different 'main' files for the same application (or you might in your user.zsai).  For example... I just opened a ticket a couple months ago because Visual Studio had about 20 versions.  10 of them had a DLL file as their main.  the other 10 had DEVENV.EXE.  So I had HP look into correcting the 10 DLLs and switch them to the EXE to get utilization data

 

#3)  Maybe the user is killing the discusge.exe processes or it isn't running on their computer for some reason

 

~Jeff

0 Likes
7 Replies
Jeff Sikorski Absent Member.
Absent Member.

Re: SQL for Software Utilization report

Jump to solution

Here is what I use... might help get you started...

 

~~~~~~~~~~~~~

 

select
        d.device_label as Device_Name
       ,d.Device_Tag as Operating_System
       ,a.Application_Name as Application_Name
       ,c.Company_Name as Company_Name
       ,r.Release_Name as Release_Name
       ,v.Version_Name as Version_Name
       ,s.SWSubComponent_InstalledDirectory as Installed_Directory
       ,hwA.hwAssetEmployeeID as User_EMail_ID
       ,hwA.hwAssetDescription as Machine_Model
       ,d.device_preferredIPAddress as IP_Address
       ,d.device_preferredMACAddress as MAC_Address
       ,d.Device_LastScanTimeStamp as Inventory_Date
       ,d.device_DiscoveryRule as Location_based_off_IP
       ,su.softwareutilization_LastQuarterUsageDays as Days_Used_Last_Quarter
       ,su.softwareutilization_LastQuarterUsageHours as Hours_Used_Last_Quarter
       ,su.softwareutilization_PercentUtilization as Percentage_Used_Based_On_Days   
 


   from device d  
  
    LEFT JOIN hwAssetData hwA on d.Device_ID = hwA.Device_ID    
   
    LEFT JOIN swsubcomponent s on d.device_id = s.device_id       
      LEFT JOIN version v on s.Version_ID = v.Version_ID
             LEFT JOIN `release` r on v.Release_ID = r.Release_ID
             LEFT JOIN application a on r.Application_ID = a.Application_ID
             LEFT JOIN company c on a.Company_ID = c.Company_ID   
      LEFT  JOIN softwareutilization su on (s.version_ID = su.version_ID and s.device_ID  = su.device_ID and su.user_id = 1)
       
   where
         
          d.device_status = 'Active'     
--      and d.device_label = '<COMPUTERNAME>'     
     
      and (a.Application_Name = 'Eclipse IDE' or a.Application_Name = 'visual studio')


order by a.application_name, d.device_label

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

0 Likes
Absent Member.. monnewbie3 Absent Member..
Absent Member..

Re: SQL for Software Utilization report

Jump to solution
Ahh I missed the double link from swsub to swutilization, I'll check it out when I'm back at work.
Thanks!
0 Likes
Jeff Sikorski Absent Member.
Absent Member.

Re: SQL for Software Utilization report

Jump to solution

 

I did softwareutilization.user_id = 1  to get "All Users".  I think that's right, but you might want to double check in your environment though and experiment with this.

 

I think SW Utilization works better when the "EXE" is the MAIN file in the Footprint.  I've had really bad results (0s and nulls) when footprints have "DLL" files as their MAIN.  So I think SW Utilization tracks running processes... which is typically EXEs and not DLLs.. something else to keep in mind.

 

I'm pretty new to SW utilization as well so I'm still learning.  Any other tricks you pick up or learn, plz share em on this thread 🙂

 

~Jeff

Absent Member.. monnewbie3 Absent Member..
Absent Member..

Re: SQL for Software Utilization report

Jump to solution
Thanks for that post Jeff!
That actually totally makes sense.. Since the module running is disusage.exe, I highly highly doubt there is anyways to track dll's run time..if they're the main.
So this would shed light on a bunch of those blank values.
The only thing I don't understand is when I return null values on Applications that are configured correctly... Sometimes I get "0 hours", and sometimes I get blanks.. Don't understand that.
Kudos for the above answer, really help shed some light on things.

-M
0 Likes
Absent Member.. monnewbie3 Absent Member..
Absent Member..

Re: SQL for Software Utilization report

Jump to solution

Hey Jeff,

 

So everything seems to be working correctly, there's only 2 things I don't understand;

 

1. Why must the user_id field be set to '1'?

2. Why are there blank entries for the same application? (Utilization module not enabled?)

 

Thanks in advance,

 

-M

0 Likes
Jeff Sikorski Absent Member.
Absent Member.

Re: SQL for Software Utilization report

Jump to solution

 

The user_id doesnt HAVE to be set to 1... but I think that means "All Users".  DDMI Utilization actually captures utilization PER USER.  99% of the time I imagine it's 1 user per machine.  It's really up to you to decide how you want to do it.

 

So lets say my computer has just 1 user, me. DDMI would capture 2 things:
#1)  All Users

#2)  Jeff

 

In this case, #1 and #2 are the same... so it doesnt matter which one you pick right?  All Users = Jeff.  So technically you could write the query and use "#2"... HOWEVER... if you are running a query on 20,000 devices, and want to use #2... keep in mind that there might be a device or two that has:
#1)  All Users

#2)  Jeff

#3)  Bob

#4)  Sue

#5)  Mary

 

If you ran the same query and only grabbed #2... you would be missing out on #3, 4, 5.  You can probably write some advanced queries to get all of it separate if you want.

 

So keeping it with "All Users" will get the entire machine, and 'doesnt care' about users.  If you DO have to care about multiple users on the same machine, it's going to be a nightmare to administer and I feel bad for you 🙂

 

 

 

I'm not sure what you mean by " Why are there blank entries for the same application? (Utilization module not enabled?)"   I'd have to seea  screen shot of what you are talking about... but it could be a few different things.

 

#1)  When you run a query, lets say a new person started @ your company today and just got his laptop, but DDMI already inventoried it.  So his data might show blank when someone who has been around for 6 months+ will have good data

 

#2)  Really check out the footprint.  Sometimes HP makes a mistake with their footprints and has different 'main' files for the same application (or you might in your user.zsai).  For example... I just opened a ticket a couple months ago because Visual Studio had about 20 versions.  10 of them had a DLL file as their main.  the other 10 had DEVENV.EXE.  So I had HP look into correcting the 10 DLLs and switch them to the EXE to get utilization data

 

#3)  Maybe the user is killing the discusge.exe processes or it isn't running on their computer for some reason

 

~Jeff

0 Likes
Absent Member.. monnewbie3 Absent Member..
Absent Member..

Re: SQL for Software Utilization report

Jump to solution

Hey Jeff,

 

Thanks for the user ID explanation, makes perfect sense now!

 

As for the second part, I'm just referring to the query returning blank results, even though the application is installed.

And this number is quite large on a few I'm looking for.

I suppose the foot print could be wrong, I doubt they disabled the module since our images are pretty locked down.

 

Thanks for your help, you solved my issue 🙂

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.