Sample queries

From CEDPS

Contents 1 Intro 2 Queries 2.1 Get events related by a guid 2.2 Get date as a timestamp 2.3 Get logs related to a given user (DN) 3 Old and in the way

Intro

Sample queries for CEDPS logging database.

See DB_schema page for the schema.

See Pegasus Sample Queries for Pegasus-specific queries.

Queries

Get events related by a guid

create view same_guid as 
select a1.e_id as id1, a2.e_id as id2 
from ident as a1 join ident as a2 on a1.value = a2.value 
where a1.name = 'guid' and a2.name = 'guid' and a1.e_id < a2.e_id;

Notes:

• using a view means the indexes are available for queries that use the view
• changing 'guid' to 'someother'[.id] would work
• the a1.e_id < a2.e_id makes sure you only get (1,2), not also (2,1), (1,1) and (2,2)

Get date as a timestamp

select DATE_ADD('1970-01-01', interval event.time second) from event ...

Get logs related to a given user (DN)

select ... from event join dn on event.id = dn.e_id where dn.value like '%Dan Gunter%';

Old and in the way

These queries are out of date and will be replaced by better ones

Time and status by DN, from WS-GRAM pre-BP (parsed) logs

Return the duration between globus.ws-gram.job.start and globus.ws-gram.job.end that have the same job id. Since the start and end log events only list the user name, use the authorization event to determine the mapping from user to DN. Since this does not have the job id in it as well, this mapping is approximate (i.e. if there are multiple mappings in the same log, it may be wrong -- using the host name might help with this).

-- Select a database
use nltest;
--
-- Query to show the Job ID, DN, duration and status of all
-- jobs that had a start and end event.
--
select 
     job.value as 'jobid', 
     user.value as 'user',
     dn1.dn as 'dn',
     startend.dur as 'duration',
     startend.status as 'status'
from event
     --
     -- join '.start' events with their user and job.id attrs
     --
     join event_type on event_type.id = event.et_id
     join ident_val as job  on job.e_id = event.id
     join ident_name on job.name_id = ident_name.id
     join attr_val user on user.e_id = event.id
     join attr_name  on user.name_id = attr_name.id
     --
     -- join with the user/DN mapping in the auth events
     --
     join ( 
          select distinctrow user, dn 
          from (
              select event.id, attr_val.value as 'user' 
              from event 
                    join event_type on event.et_id = event_type.id 
                    join attr_val on attr_val.e_id = event.id 
                    join attr_name on attr_name.id = attr_val.name_id 
              where event_type.name = 'globus.ws-gram.auth.user' 
                    and attr_name.name = 'user'
          ) as t1 
              join ( select event.id, dn.value as 'dn' 
                     from event 
                         join event_type on event.et_id = event_type.id 
                         join dn on dn.e_id = event.id
                     where event_type.name = 'globus.ws-gram.auth.user' 
              ) as t2
              on t1.id = t2.id 
     ) as dn1 
     on dn1.user = user.value
     --
     -- join with elapsed time and status of each job
     --
     join (
          select
                start1.jobid, 
                (end1.time - start1.time) as 'dur', 
                end1.status  
          from ( 
              select distinctrow event.time, ident_val.value as 'jobid' 
              from event 
                  join event_type on event.et_id = event_type.id 
                  join ident_val on ident_val.e_id = event.id 
                  join ident_name on ident_name.id = ident_val.name_id 
              where event_type.name =  'globus.ws-gram.job.start' and
                    ident_name.name = 'job.id' 
          ) as start1 
              join (
                  select 
                         distinctrow event.time,
                         ident_val.value as 'jobid', 
                         status.value as 'status'
                  from event 
                      join event_type on event.et_id = event_type.id 
                      join ident_val on ident_val.e_id = event.id 
                      join ident_name on ident_name.id = ident_val.name_id
                      join attr_val status on status.e_id = event.id
                      join attr_name on attr_name.id = status.name_id 
                  where event_type.name =  'globus.ws-gram.job.end'  and
                        ident_name.name = 'job.id'  and
                        attr_name.name = 'status' 
               ) as end1 
               on start1.jobid = end1.jobid
    ) as startend 
    on startend.jobid = job.value
where 
    event_type.name = 'globus.ws-gram.job.start' and
    ident_name.name = 'job.id' and
    attr_name.name = 'user'
;