Prev: Data Labels on cfchart
Next: showing a pdf
From: draves on 22 Aug 2006 17:00
Had to do a dummy select to preset the variable types to stop the bad
extraction of the timestamps in the 1870s. The microseconds have to be added
separately if that much precision is desired.

<cfquery name="TNAMES" datasource="x">
select
c.sys_tname as file
from qsys2.syscolumns c,qsys2.systables t
where
c.sys_tname = t.sys_tname and
c.sys_dname = t.sys_dname and
t.table_type = 'P' and
c.sys_tname like '%QHST%'
AND C.SYS_DNAME = 'QSYS'
</cfquery>

<cfloop query="tnames">
<cfquery name="#tnames.file#" datasource="x">
select char(current timestamp) AS TIMELOGGED ,

SUBSTR(
' '||
' '
,1,10) AS ENTRY ,' ' as type,
hex(substr(' ',1,8)) as HEXVAL
FROM qsys.#tnames.file# a where 1=2
union all
SELECT CHAR(
(timestamp('1928-08-23-12.03.06.314752') ) +
cast( ((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),1,1)))-1)*(16**10/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),2,1)))-1)*(16**9/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),3,1)))-1)*(16**8/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),4,1)))-1)*(16**7/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),5,1)))-1)*(16**6/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),6,1)))-1)*(16**5/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),7,1)))-1)*(16**4/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),8,1)))-1)*(16**3/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),9,1)))-1)*(16**2/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),10,1)))-1)*(16**1/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),11,1)))-1)/3906.25+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),12,1)))-1)*(16**4/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),13,1)))-1)*(16**3/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),14,1)))-1)*(16**2/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),15,1)))-1)*(16**1/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),16,1)))-1)*( 1/4096000000) as bigint )
SECONDS
+ cast( (mod(((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),1,1)))-1)*(16**10/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),2,1)))-1)*(16**9/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),3,1)))-1)*(16**8/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),4,1)))-1)*(16**7/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),5,1)))-1)*(16**6/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),6,1)))-1)*(16**5/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),7,1)))-1)*(16**4/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),8,1)))-1)*(16**3/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),9,1)))-1)*(16**2/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),10,1)))-1)*(16**1/3906.25)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),11,1)))-1)/3906.25+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),12,1)))-1)*(16**4/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),13,1)))-1)*(16**3/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),14,1)))-1)*(16**2/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),15,1)))-1)*(16**1/4096000000)+
((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),16,1)))-1)*( 1/4096000000),1)*1000000) as
bigint ) microSECONDS
)
AS TIMELOGGED ,

SUBSTR(SYSLOGFLD,POSSTR(SYSLOGFLD,'user')+5,posstr(SUBSTR(SYSLOGFLD,POSSTR(SYSLO
GFLD,'user')+5,100),'.')-1)||' '
AS ENTRY ,
'INCORRECT' AS TYPE,hex(substr(syslogfld,1,8)) as HEXVAL

FROM qsys.#tnames.file#
WHERE UPPER(SYSLOGFLD) LIKE '%PASSWORD%NOT CORRECT%'
UNION ALL
SELECT CHAR(
(timestamp('1928-08-23-12.03.06.314752') ) +
cast( ((POSSTR('0123456789ABCDEF',
substr(hex(substr(syslogfld,1,8)),1,1)))-1)*(16**10/3
 | 
Pages: 1
Prev: Data Labels on cfchart
Next: showing a pdf