Following on to Laura's good work, if you do store the
account code in the contact information, as you suggested, I'd
make the following addition:

  select substr(CHAR(contact),1,4) account_code, -
         substr(CHAR(contact),6) contact_name, -
         occupancy.node_name, -
         sum(logical_mb) as storage_mb -
    from nodes, occupancy -
   where nodes.node_name = occupancy.node_name -
group by contact, occupancy.node_name

And, since Eric's interested in comma-delimited output, how about
this. Save the above query into a file called storage.dsm. Now,
run the following command:

  dsmadmc -id=ADMIN -pass=XXXX -commadelimited macro storage.dsm

redirect the output to a file if you'd like.

  -- Tom

Thomas A. La Porte
DreamWorks Feature Animation
tlaporte AT anim.dreamworks DOT com


On Thu, 15 Apr 1999, Laura Buckley wrote:

>Ok Eric,
>
>Here's what you're looking for I think (this one challenged me...)
>
>This command gives the following output
>
>adsm> select contact, occupancy.node_name, sum(logical_mb) as STORAGE_MB from
>      nodes, occupancy where nodes.node_name = occupancy.node_name group by
>      contact, occupancy.node_name
>
>CONTACT              NODE_NAME                  STORAGE_MB
>------------------           ------------------
>                   ---------------------------------
>
>John Sorensen          BEBEAR                      463.82
>John Sorensen          BEEBEE                      1105.20
>Kelly Lipp                 LIPSTR                         13783.56
>Kelly Lipp, Laptop     WEAZER                      2470.04
>Laura Buckley          BIRD                              1013.30
>Laura Buckley          LOLA                             3393.12
>Laura's System        SNAIL                              698.00
>Nicole's PC              COLE                              1562.06
>strat                         STRAT                            1549.06
>
>Laura Buckley
>Storage Solutions Specialists, Inc.
>(719) 531-7171
>buckley AT storsol DOT com
>
>-----Original Message-----
>From:   Loon, E.J. van - SPLXM [SMTP:Eric-van.Loon AT KLM DOT NL]
>Sent:   Thursday, April 15, 1999 6:04 AM
>To:     ADSM-L AT VM.MARIST DOT EDU
>Subject:        ADSM charge-out
>
>Hi ADSM-ers!
>My manager asked me to start charging our customers for ADSM usage. I
>checked the Administrator Guide and came across the Accounting feature which
>allows you to create accounting records. This is however at a session level
>and only viable if you want the user to be charged for every byte which was
>send to the server.
>I would like to charge the user for the amount of Mb.'s stored on the server
>and I'm going to collect this once a week (in the beginning).
>I was thinking of updating the nodes contact field with an 4 digit account
>code which I can use for processing. So, if a node is called "ERIC" and the
>contact information is now: "Eric van Loon" I will change it to: "0761-Eric
>van Loon" where 0761 is the account code for ERIC's department.
>I am looking for an SQL query which I can use for collecting this data. It
>would be great if it's possible to have the following output:
>0761,ERIC,1075
>where the first value is the account code (extracted from the contact
>information), the second is the node name and the third is the amount of
>stored megabytes.
>Is this possible? Any help of you SQL wizards out there is VERY much
>appreciated!
>Kindest regards,
>Eric van Loon
>KLM Royal Dutch Airlines
>