0

I have been tasked with analyzing license utilization via data stored in a database controlled by Flexnet manager (flexlm). What I need to graph is the number of concurrent licenses in use for a specific period of time (high water mark). I am having some trouble doing this as I have very little experience of SQL or BI tools.

I have taken 2 tables, license_events and license_features (these have been filtered for specific users and features). I have then done a join to create a License_feature table. Sample data looks as follows:

CLIENT_PROJECT FEATURE_NAME LIC_COUNT START_TIME    EVENT_TIME DURATION 
BGTV           eclipse      1         1,422,272,438 1422278666 6,228 
BGTV           eclipse      1         1,422,443,815 1422443845 30 
BGTV           eclipse      1         1,422,615,676 1422615681 5 
BGTV           eclipse      1         1,422,631,395 1422631399 4 
BGTV           eclipse      4         1,422,631,431 1422631434 3 
BGTV           eclipse      1         1,422,631,465 1422631474 9 
BGTV           eclipse      1         1,422,631,472 1422631474 2 
BGTV           eclipse      2         1,422,632,128 1422632147 19 
BGTV           eclipse      1         1,422,632,166 1422632179 13 
BGTV           eclipse      6         1,422,632,197 1422632211 14

What I need now is to graph something like this:

For each time (second) sum(LIC_COUNT) where start_time <= time && end_time >= time

Ideally this should give me the number of concurrent licenses checked out at a specific second. Even better would be if I could get this information for a different time period such as hours or days.

How could I go about doing this?

Rudi Kershaw
  • 12,332
  • 7
  • 52
  • 77
Neil Brown
  • 1
  • 1
  • Did you ever find a solution to your problem? If any of the answers to your question solved it, don't forget to accept it by clicking the grey tick next to the answer. If you solved it another way you can post your own answer and accept that, so that future visitors can be helped by your solution. – Rudi Kershaw Mar 02 '15 at 21:38

1 Answers1

0

Use the GROUP BY keywords to group the SUM() together on a specific column. For example, grouping the SUM() of LIC_COUNT by each START_TIME;

SELECT START_TIME, SUM(LIC_COUNT) AS TOTAL_LIC_COUNT
FROM YOUR_TABLE
GROUP BY START_TIME

Now, to SUM() all LIC_COUNT at each increment between START_TIME and END_TIME you'll need to explicitly specify those unique values somewhere else. For example, if you created a table called UniqueTimes that contained all possible values between your earliest START_DATE and last END_DATE. Then you could do something like the following;

SELECT UniqueTime, SUM(LIC_COUNT) AS TotalLicCount
FROM YOUR_TABLE
LEFT JOIN UniqueTimes ON (UniqueTime >= START_TIME AND UniqueTime <= END_TIME)
GROUP BY UniqueTime

This should group your rows as each unique time, and show the total of all summed LIC_COUNT at each specific time.

I hope this helps.

Rudi Kershaw
  • 12,332
  • 7
  • 52
  • 77