Task is not getting created when we are using Dataset inside Analytics

[User_ID_Singh1]

Hi,

We have a requirement where we have to create disable account task for the inscope applications for that we are creating datasets where we are storing endpointname and endpointkey and relating this inside analytics for creating tasks. Please let me know if am missing anything

Query:
SELECT distinct u.username,u.displayname as 'User Full Name',u.email, a.name 'Application Account Name',a.accountkey as acctKey, e.displayname 'Application Name', a.CREATED_ON 'Account Creation Date', CASE WHEN a.status = 1 THEN 'ACTIVE' ELSE a.status END AS 'Account Status', a.lastlogondate, 'disableAccount' as 'Default_Action_For_Analytics' FROM accounts a inner join endpoints e on a.ENDPOINTKEY=e.ENDPOINTKEY inner join dataset_values ds on ds.attribute2= e.endpointkey inner join user_accounts ua on a.accountkey=ua.accountkey inner join users u on u.userkey = ua.userkey where date(a.lastlogondate) = CURDATE() - INTERVAL 90 DAY and ds.attribute2=e.endpointkey

[pmahalle]

Hi @User_ID_Singh1 ,

Query returning result when ran in Data Analyzer? Can you try that once and check the result. Also make sure to checked the Save Data option for field Where do you want to save your data?

[vivekmistry]

Yes query working in data analyser

[pmahalle]

@vivekmistry 

You are triggering the analytics though Job or executing manually?

[vivekmistry]

we tried both ways Job and manual, no help

[NM]

Hi @vivekmistry , try what I suggested below once..

[rushikeshvartak]

• Did you selected disable account under allowed action in analytics configuration?
• did you selected take default action checkbox under job configuration?

[vivekmistry]

Yes all the configuration is in place.

Task getting created for this query:

SELECT distinct u.username,u.displayname as 'User Full Name',u.email, a.name 'Application Account Name',a.accountkey as acctKey, e.displayname 'Application Name', a.CREATED_ON as 'Account Creation Date', CASE WHEN a.status = 1 THEN 'ACTIVE' ELSE a.status END AS 'Account Status', a.lastlogondate, 'disableAccount' as Default_Action_For_Analytics FROM accounts a inner join endpoints e on a.ENDPOINTKEY=e.ENDPOINTKEY  inner join user_accounts ua on a.accountkey=ua.accountkey inner join users u on u.userkey = ua.userkey where date(a.lastlogondate) =DATE_SUB(CURDATE(), INTERVAL 90 DAY) and e.customproperty31= 'True'

 But task is not getting created for query :

SELECT distinct u.username,u.displayname as 'User Full Name',u.email, a.name 'Application Account Name',a.accountkey as acctKey, e.displayname 'Application Name', a.CREATED_ON 'Account Creation Date', CASE WHEN a.status = 1 THEN 'ACTIVE' ELSE a.status END AS 'Account Status', a.lastlogondate, 'disableAccount' as Default_Action_For_Analytics FROM accounts a inner join endpoints e on a.ENDPOINTKEY=e.ENDPOINTKEY inner join dataset_values ds on ds.attribute2= e.endpointkey inner join user_accounts ua on a.accountkey=ua.accountkey inner join users u on u.userkey = ua.userkey where date(a.lastlogondate) =DATE_SUB(CURDATE(), INTERVAL 90 DAY) and ds.attribute2=e.endpointkey

[rushikeshvartak]

SELECT DISTINCT 
    u.username,
    u.displayname AS 'User Full Name',
    u.email, 
    a.name AS 'Application Account Name',
    a.accountkey AS acctKey, 
    e.displayname AS 'Application Name', 
    a.CREATED_ON AS 'Account Creation Dat', 
    CASE WHEN a.status = 1 THEN 'ACTIVE' ELSE a.status END AS 'Account State', 
    a.lastlogondate as 'Last Login', 
    'disableAccount' AS Default_Action_For_Analytics 
FROM 
    accounts a 
    INNER JOIN endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY  
    INNER JOIN user_accounts ua ON a.accountkey = ua.accountkey 
    INNER JOIN users u ON u.userkey = ua.userkey 
WHERE 
    DATE(a.lastlogondate) = CURDATE() - INTERVAL 90 DAY 
    AND e.customproperty31 = 'True';

[vivekmistry]

With using CP31 we are able to create the task, but we have to use datasets table for creating task.

[rushikeshvartak]

Does attribute 2 contains proper endpointkey ?

[vivekmistry]

Yes:

[NM]

Hi @vivekmistry I will suggest create a new analytics.

[rushikeshvartak · Accepted solution]

You have not specified dataset name

SELECT DISTINCT 
    u.username,
    u.displayname AS 'User Full Name',
    u.email, 
    a.name AS 'Application Account Name',
    a.accountkey AS acctKey, 
    e.displayname AS 'Application Name', 
    a.CREATED_ON AS 'Account Creation Dat', 
    CASE WHEN a.status = 1 THEN 'ACTIVE' ELSE a.status END AS 'Account State', 
    a.lastlogondate as 'Last Login', 
    'disableAccount' AS Default_Action_For_Analytics 
FROM 
    accounts a 
    INNER JOIN endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY  
    INNER JOIN user_accounts ua ON a.accountkey = ua.accountkey 
    INNER JOIN users u ON u.userkey = ua.userkey 
	inner join dataset_values ds on ds.attribute2= e.endpointkey
WHERE 
    DATE(a.lastlogondate) = CURDATE() - INTERVAL 90 DAY 
	AND ds.attribute2=e.endpointkey
	And ds.datasetname='RUSHI'

[vivekmistry]

Thanks it worked

[rushikeshvartak]

✅Please click the 'Accept As Solution' button on the reply  that best answers your original question and hit 'Kudos' button 👍.

[NM]

Hi @User_ID_Singh1 , if you are creating task using analytics job

Add below in your query

sysdate() as currentDate

[rahul_p]

Hello @User_ID_Singh1 ,

Can you check the logs when running the analytics and share those here please?

Thanks!

Regards,
Rahul
Please accept this as solution & give kudos if it resolves your issue.