GoGreen PC TuneUp™
Learn More

Insta-Install™
this is how we code sign our software and ssl encrypt our websites from our affiliate partner SSL.com

myBlog

myBlog Home

Back to Blog MTNCOMP


SQL queries for DNS processing

Published: Sunday, December 16, 2018 written by Drew Jackson
View Count: 217
Keywords: sql, dns, 2017



while working on DNS processing research this type of processing helped me work on the data sets.
 

/* dnsRecords1 works and the ps1 associated does not need ODBC entry, integrated security is fine */

create database dbDNS
use dbDNS

create table DNSRecords1 (
    row_id integer identity,
    --ztimestampvalue integer null,
    domain_name varchar(255) null,
    --domainnamevalue nchar(255) null,
    recordtype varchar(255) null,
    --recordtypevalue nchar(100) null,
    ipaddress varchar(255) null,
    --ipaddressvalue nchar(100)
);

sp_columns DNSRecords1

alter table DNSRecords1
add homepagetext nvarchar(max) null

alter table DNSRecords1
add processed_datetime datetime null

alter table DNSRecords1
add been_processed tinyint null

CREATE INDEX i1 ON DNSRecords1 (row_id);  
CREATE INDEX i2 ON DNSRecords1 (been_processed);  

/* https://dataedo.com/kb/query/sql-server/list-table-indexes */

select schema_name(t.schema_id) + '.' + t.[name] as table_view,
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type],
    i.index_id,
    case when i.is_primary_key = 1 then 'Primary key'
        when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [type],
    i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type
from sys.objects t
    inner join sys.indexes i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by schema_name(t.schema_id) + '.' + t.[name], i.index_id


set rowcount 0
set rowcount 50

select max(row_id) from DNSRecords1
update DNSRecords1 set been_processed = 1 where processed_datetime is not null
select substring('name:n8i704x.kacpw.com', 5, 9)
select distinct recordtype from DNSRecords1
select * from DNSRecords1 where row_id = 904543794
select * from DNSRecords1 where row_id = 1455919465
select * from DNSRecords1 where processed_datetime is not null
select * from DNSRecords1 where been_processed = 1

/* "type":"a" */
/* "type":"cname" */

select * from DNSRecords1 where processed_datetime = null and substring(recordtype,9,5) = 'cname'
select substring(recordtype,9,5) from DNSRecords1
select * from DNSRecords1 where row_id > 500000000 and row_id < 500001000
select * from DNSRecords1 where row_id > 5000000 and row_id < 5000100

EXEC sp_rename 'dbDNS.DNSRecords1.domainname', 'domain_name', 'COLUMN';
GO

select 2 from DNSRecords1 where processed_datetime is not null
update DNSRecords1 set processed_datetime = getdate() where row_id > 5000000 and row_id < 5000100
update DNSRecords1 set processed_datetime = null where row_id > 5000000 and row_id < 5000100
select getdate()

delete from DNSRecords1
drop table DNSRecords


DECLARE @RecordDetails VARCHAR(MAX)

select @RecordDetails =
        BulkColumn
        -- FROM OPENROWSET(BULK'D:\dns\2018-10-26-1540556314-fdns_a.json', SINGLE_BLOB) JSON
        FROM OPENROWSET(BULK'D:\dns\sample.json', SINGLE_BLOB) JSON

  -- select @RecordDetails as RD_Details;
  select @RecordDetails

     SELECT * FROM OPENJSON(@RecordDetails, '$')
        WITH (    
                ztimestamp varchar(100)    '$.timestamp',
                domainname varchar(100) '$.name',
                recordtype varchar(100) '$.type',
                ipaddress  varchar(100) '$.value'
            )




        INSERT INTO DNSRecords
        SELECT *
        FROM OPENJSON(@RecordDetails)
        
        
        WITH (    
                ztimestamp varchar(100)    '$.timestamp',
                domainname varchar(100) '$.name',
                recordtype varchar(100) '$.type',
                ipaddress  varchar(100) '$.value'
            )
END

select * from DNSRecords



Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'D:\dns\sample.json', SINGLE_CLOB) as j

Select @JSON

If (ISJSON(@JSON)=1)
Print 'Valid JSON'




BULK INSERT dbo.DNSRecords
FROM 'D:\dns\sample.json'
WITH
(
  CODEPAGE = '1252',
  FIELDTERMINATOR = ',',
  CHECK_CONSTRAINTS
)

select count(*) from DNSRecords1

select * from DNSRecords1
set rowcount 50
select max(row_id) from DNSrecords1

select distinct(ipaddress) from DNSRecords1



if you found this article helpful, consider contributing $10, 20 or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2019 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.