Hide DNS Version

Some time ago, I’ve setup internal (AD based) DNS and external DNS for our domain. Now, when you do domain checkup with BIND tools, you will usualy get DNS version. For my external DNS it reported it was Windows server DNS. Now, for that problem (security issues), I found a solution on: http://www.admin-enclave.com/en/solutions/windows/146-hide-microsoft-dns-software-version.html
I is pretty simple, over CMD or over REGEDIT;
dnscmd /config /EnableVersionQuery 0
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\DNS\Parameters\EnableVersionQuery to 0

 

More on the subject:

https://msdn.microsoft.com/en-us/library/cc422472.aspx

 

Good Luck

SRSS – Get list of Reports and Its Data sources and Datasets

I needed to get the list of my reports and what data source and dataset they use.

use ReportServer;
WITH XMLNAMESPACES ( DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition’, ‘http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd )
SELECT distinct ReportName = name
,DataSetName = x.value(‘(@Name)[1]’, ‘VARCHAR(250)’)
,DataSourceName = x.value(‘(Query/DataSourceName)[1]’,’VARCHAR(250)’)
,CommandText = x.value(‘(Query/CommandText)[1]’,’VARCHAR(250)’)

FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes(‘/Report/DataSets/DataSet’) r ( x )
CROSS APPLY x.nodes(‘Fields/Field’) f(df)
ORDER BY name

image.png

 

 

Good Luck

SQL – Getting list of jobs and their steps

How to get list of jobs an their steps. For this I will use three tables dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb database.

You can create select from each of these tables to get relevant data.

select name,enabled,description from msdb.dbo.sysjobs
select job_id,step_id,step_name,command,database_name from msdb.dbo.sysjobsteps
select job_id,next_run_date,next_run_time from msdb.dbo.sysjobschedules

 

But we can combine these three tables, to get unified complex select.

SELECT
    job.name,
    job.enabled,
    job.description,
    steps.step_name,
    steps.command,
    steps.database_name,
    sch.next_run_date AS ‘Sched Date’,
    sch.next_run_time AS ‘Sched Time’
FROM msdb.dbo.sysjobs job JOIN
     msdb.dbo.sysjobsteps steps       
        ON job.job_id = steps.job_id INNER JOIN
    msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
–where name like ‘%BACKUP%’ –you can also filter with where clause
order by name

 

 

 

Good Luck

Introduction

Let me introduce my idea of this blog post. This should be a little help for all you DBA and IT admins. Little tips and tricks on HOW TO..
So for now, I have to get my thoughts together and think of a couple of thing I did that made my life easier.

Best regards
Luka