cancel
Showing results for 
Search instead for 
Did you mean: 

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

Tom_Egger
Level 4
Certified

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

2 ACCEPTED SOLUTIONS

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

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

View solution in original post

4 REPLIES 4

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified
Hi,

Do you have multiple masters?

Do you only want to see policy and start time? How long back? Daily or weekly report?

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
Moderator
Moderator
Partner    VIP    Accredited Certified

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

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