LANSweeper : Specific Software Deployment Report

Use the following query to find a specific applictaion and its deployment data, change the softwareName and softwarePublisher fields to suit.

{code lang:sql showtitle:false lines:false hidden:false}Select tblAssets.AssetName,
tblAssets.Domain,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.Installdate

From tblAssets
    Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID

Where tblSoftwareuni.softwareName Like ‘%Helper Object%’ And tblSoftwareUni.SoftwarePublisher Like ‘%microsoft%’

Order By tblAssets.Domain, tblAssets.AssetUnique, tblSoftwareuni.softwareName{/code}

LANSweeper : Identify Non-standard Browsers

LANSweeper : Identify Non-standard Browsers

The following query, when copied into the report designer, will identify computers with non-standard browsers – i.e. users with browsers other than Internet Explorer. Adjust this to suit your environment, corporate standards are different everywhere.

{code lang:sql showtitle:false lines:false hidden:false}Select Top 1000000 tblComputers.ComputerName,tblComputers.ComputerUnique,tblSoftware.softwareNameAsProduct, tblComputers.Domain,tblComputers.Username

From tblSoftware
Inner Join tblComputers On tblSoftware.ComputerName=tblComputers.Computername
Inner Join web40ActiveComputers On tblComputers.Computername=web40ActiveComputers.Computername

Where tblSoftware.softwareName Like ‘%Google Chrome%’
OR tblSoftware.softwareName Like ‘%Safari%’
OR tblSoftware.softwareName Like ‘%Mozilla%’

Group By tblComputers.ComputerUnique,tblComputers.ComputerName,tblSoftware.softwareName,tblComputers.Domain,tblComputers.Username

Order By tblSoftware. softwareName{/code}

LANSweeper : Query SQL Server Version

LANSweeper : Query SQL Server Version

SQL version information is stored in the registry, its location varies depending on server architecture (x86/x64) and SQL Instance name.

This is how I achieved this….

Added the following Registry paths to be scanned by LANSweeper (you may not need all of these, could well need to add some of your own):

RegKey ValueName
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2005\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SHAREPOINT\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SOPHOS\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SHAREPOINT\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup Edition

Next, scan all of your machines.

Finally use the following code to return the information for your domain, by version:

{code lang:sql title:”LANSweeper Query SQL Version” lines:false hidden:false}Select Top 1000000 tblComputers.ComputerName, tblComputers.ComputerUnique As Machine , tblSoftware.softwareName As Product, tblRegistry.Value As ‘Version’,
tblComputers.Domain

From tblSoftware
Inner Join tblComputers On tblSoftware.ComputerName = tblComputers.Computername
Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername
Inner Join tblRegistry On tblComputers.Computername = tblRegistry.Computername

Where tblSoftware.softwareName Like ‘Microsoft SQL Server%’ And tblRegistry.Valuename = ‘Edition’
And tblSoftware.softwareName Not Like ‘%Native Client%’ And tblSoftware.softwareName Not Like ‘%Policies%’
And tblSoftware.softwareName Not Like ‘%Setup%’ And tblSoftware.softwareName Not Like ‘%Browser%’
And tblSoftware.softwareName Not Like ‘%VSS%’ And tblSoftware.softwareName Not Like ‘%Books%’
And tblSoftware.softwareName Not Like ‘%Compatibility%’ And tblSoftware.softwareName Not Like ‘%Analysis%’
And tblSoftware.softwareName Not Like ‘%Management Objects%’ And tblSoftware.softwareName Not Like ‘%Design Tools%’
And tblSoftware.softwareName Not Like ‘%Studio%’ And tblSoftware.softwareName Not Like ‘%Query Tools%’
And tblSoftware.softwareName Not Like ‘%Best Practice%’ And tblSoftware.softwareName Not Like ‘%CLR%’
And tblSoftware.softwareName Not Like ‘%Advisor%’ And tblSoftware.softwareName Not Like ‘%Reporting%’
And tblSoftware.softwareName Not Like ‘%Data Mining%’ And tblSoftware.softwareName Not Like ‘%Wizard%’
And tblSoftware.softwareName Not Like ‘%AdventureWorks%’ And tblSoftware.softwareName Not Like ‘%Transact-SQL%’
And tblSoftware.softwareName Not Like ‘%Refresh 3 Samples%’ And tblSoftware.softwareName Not Like ‘%Developer Tools%’

Group By tblComputers.ComputerUnique, tblComputers.ComputerName, tblSoftware.softwareName, tblRegistry.Value, tblComputers.Domain
Order By tblSoftware.softwareName{/code}

Modify the code if you want to return the version per computer or anything else for that matter… 🙂

LANSweeper : Unaligned Disk Report

LANSweeper : Unaligned Disk Report

The SQL below will display all disks, that are over 1GB in size and not a system boot disk, in an ‘un-aligned’ state.

{code lang:sql title:”Unaligned Disk Report” lines:false hidden:false}Select Top 1000000 tblComputers.ComputerUnique, tblComputers.Computername, tblComputers.Domain,
Web40OSName.OSname, tblOperatingsystem.Description, CAST(ROUND(((tblDiskPartition.size/1024)/1024)/1024,2,0) As Decimal(20,2)) As ‘Size (GB)’ , tblDiskPartition.StartingOffset,
tblDiskPartition.DeviceID,
CASE WHEN (tblDiskPartition.StartingOffset/4096 != ROUND(tblDiskPartition.StartingOffset/4096,0)) THEN ‘FALSE’ ELSE ‘TRUE’ END As ‘Is Aligned?’

From tblDiskPartition
Inner Join tblComputers On tblDiskPartition.Computername = tblComputers.Computername
Inner Join tblComputersystem On tblComputers.Computername = tblComputersystem.Computername
Inner Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername
Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername
Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername

Where tblDiskPartition.StartingOffset/4096 != ROUND(tblDiskPartition.StartingOffset/4096,0)
and (tblDiskPartition.bootpartition = 0 or tblDiskPartition.BootPartition = NULL)
and ROUND(((tblDiskPartition.size/1024)/1024)/1024,2) > 1

Group By tblComputers.ComputerUnique, tblComputers.Computername, tblComputers.Domain, Web40OSName.OSname,
tblOperatingsystem.Description, Web40OSName.Compimage, tblComputers.Computer, tblDiskPartition.StartingOffset,
tblDiskPartition.StartingOffset/4096, tblDiskPartition.size , tblDiskPartition.DeviceID

Order By dbo.tblComputers.Computer, Count(dbo.tblDiskPartition.Computername) Desc
{/code}