Forum Discussion

Tom_Egger's avatar
Tom_Egger
Level 4
8 years ago
Solved

policyname and the start time. This however only once per day and policies

hi all

can you help me? 

I just need the policyname and the start time. This however only once per day and policies;

SELECT TOP 1000 START AT 1 domain_JobArchive.policyName as "domain_JobArchive.policyName",adjust_timestamp(domain_JobArchive.startTime,7200000 ) as "domain_JobArchive.startTime",adjust_timestamp(domain_JobArchive.endTime,7200000 ) as "domain_JobArchive.endTime" from domain_JobArchive , domain_MasterServer where domain_MasterServer.id = domain_JobArchive.masterServerId and ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_JobArchive.endTime BETWEEN '137166366798930000' AND '137167230798930000') ) AND ( (domain_JobArchive.policyType IN (40 )) ) AND ( (domain_JobArchive.type IN (0 )) ) AND ( ( (domain_JobArchive.masterServerId IN (1897 )) ) ) ) ORDER BY "domain_JobArchive.startTime" ASC 

 

one stepp more but now ein have a Problem with Date. I will start this every day:

SELECT domain_JobArchive.policyName as "PolicyName",SUBSTRING(UTCBigIntToNomTime(domain_JobArchive.startTime),1,10) as "StartTime"
from domain_JobArchive , domain_MasterServer
WHERE domain_MasterServer.id = domain_JobArchive.masterServerId and ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_JobArchive.endTime BETWEEN '137166366798930000' AND '137167230798930000') ) AND ( (domain_JobArchive.policyType IN (40 )) ) AND ( (domain_JobArchive.type IN (0 )) ) AND ( ( (domain_JobArchive.masterServerId IN (1897 )) ) ) )
GROUP BY PolicyName, StartTime
ORDER BY PolicyName ASC

now one step more
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    8 years ago

    select
    UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
    domain_JobArchive.PolicyName as "Policy Name"
    FROM domain_JobArchive,
    WHERE DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 24

    AND domain_JobArchive.policyType IN (40 )
    GROUP BY "Policy Name"  ORDER BY "Policy Name"  DESC

  • Tom_Egger's avatar
    Tom_Egger
    8 years ago

    This is what i needed. Thx Riaan

    SELECT domain_JobArchive.policyName as "PolicyName",SUBSTRING(UTCBigIntToNomTime(domain_JobArchive.startTime),1,10) as "StartTime"
    from domain_JobArchive , domain_MasterServer
    WHERE DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 24
    AND (domain_JobArchive.policyType IN (40 ) )
    GROUP BY PolicyName, StartTime
    ORDER BY StartTime ASC

4 Replies

Sort By
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    8 years ago
    Hi,

    Do you have multiple masters?

    Do you only want to see policy and start time? How long back? Daily or weekly report?
    • Tom_Egger's avatar
      Tom_Egger
      Level 4
      8 years ago

      Hi,

      i've only one Master.

      I want see the only VMware Policies and the start Date. I want this report daily.

      i've try this but not works;

      SELECT domain_JobArchive.policyName as "PolicyName",SUBSTRING(UTCBigIntToNomTime(domain_JobArchive.startTime),1,10) as "StartTime"
      from domain_JobArchive , domain_MasterServer
      WHERE domain_MasterServer.id = domain_JobArchive.masterServerId
      AND (domain_JobArchive.isValid = '1' )
      AND (domain_JobArchive.endTime >= DATEADD(hh, -24, GETDATE()))
      AND (domain_JobArchive.policyType IN (40 ) )
      AND (domain_JobArchive.type IN (0 ) )
      AND (domain_JobArchive.masterServerId IN (1897 ) )
      GROUP BY PolicyName, StartTime
      ORDER BY PolicyName ASC

      • RiaanBadenhorst's avatar
        RiaanBadenhorst
        Level 6
        8 years ago

        select
        UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
        domain_JobArchive.PolicyName as "Policy Name"
        FROM domain_JobArchive,
        WHERE DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 24

        AND domain_JobArchive.policyType IN (40 )
        GROUP BY "Policy Name"  ORDER BY "Policy Name"  DESC