SQL queries for DNS processing

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