06-14-2017 04:23 AM - edited 06-14-2017 06:56 AM
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
Solved! Go to Solution.
06-20-2017 06:28 AM
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
06-22-2017 04:49 AM
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
06-14-2017 07:27 AM
06-14-2017 10:38 PM
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
06-20-2017 06:28 AM
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
06-22-2017 04:49 AM
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