SQL queries for DNS processing
by Mountain Computers Inc., Publication Date: Sunday, December 16, 2018
View Count: 1609, Keywords: sql, dns, 2017, Hashtags: #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 an Andrew Jackson 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.
© 2024 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.