How to find unused tags trough SQL

Hi Jinmo,

PI SQL Commander Lite                4.3.19310.1

Microsoft .NET Framework         4.0.30319.42000

Operating System                          6.2.9200.0

I know about the Stale and Bad points option, but I would like to know which points have not had any new data in the last 4 years.

Regards,

Igor

I also tried this query, but I also get duration times below the desired (1500) and I can't adjust that.

Thanks Jinmo,

that should be OK. I corrected it a little and put AND status <> 0 because it also took values earlier than 1500d

Hi igreguri​ ,

I imagine you could search for PI Points/AF Attributes with current timestamp or snapshot time of older than 4 years. Which PI SQL product is available for you?

In addition, PI-SMT also provides addin under Data > Stale and Bad Points for this purpose.

igreguri​ ,

In PI OLEDB Provider, you could probably do some query similar to the one from Compendium:

-- Returns "bad and stale" calculated tags.
-- Using a SQL WHERE condition, you can define any criterion for being "stale and bad". 
-- In this case, the criterion is either snapshot older than 4 hours and newer than 1 
-- year or snapshot with a bad status.
SELECT tag FROM piarchive..pisnapshot
WHERE tag IN (SELECT tag FROM pipoint..classic WHERE pointsource = 'C')
AND ((DATE('*') - time) BETWEEN RELDATE('4h') AND RELDATE('365d') OR status <> 0)

igreguri​ ,

While I haven't tried below query, you may be wanting similar to below:

SELECT tag,time,value 
FROM piarchive..pisnapshot
WHERE tag IN (SELECT tag FROM pipoint..pipoint WHERE tag like 'radu3%.pv' )
  AND ((DATE('*') - time) >= RELDATE('1500d') OR status <> 0)

Yeah... I tried that, but our data archive was too big and it timed out.. thank you for responding.