Finding server roles (active directory and others)

[SystemsIT]

Hello,

I know that LanSweeper already has reports for DNS and DHCP servers.

I was curious of the field it uses

= N'DNS'

what terms can be used there to get other Windows roles installed could be?

I am looking to gather a list of servers and all roles / features they have installed on them to save me having to check one by one.

I have checked single servers under Features and it lists items, I have tried to edit the reports with those fields but i do not get any results, for example

IIS-WebServer
DirectoryServices-DomainController

Any help is hugely appreciated.

[sbukovic]

You are probably looking for a query like this for IIS Feature Roles

Essentially you can review the Software > Features Section of any asset and grab the text and populate the tblFeatureUni.featureCaption OR tblFeatureUni.featureName fields to search for like strings.


Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
tblFeatureUni.featureName,
tblFeatureUni.featureCaption,
tblFeatureUni.addedDate As DateAdded,
tblFeature.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFeature On tblAssets.AssetID = tblFeature.AssetId
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblFeatureUni.featureCaption = 'Internet Information Services' And
tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName,
tblFeatureUni.featureCaption

[Dogeron]

@SystemIt and @MikeMc

I needed exactly the same report so took a look at your posts and had a poke around.

Roles are shown on Config->Windows->System page so it was just a question of getting the report.

Here is the SQL of my version :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.Roles,
tblAssets.Lastseen
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.OScode Like '%s' And tblAssetCustom.State = 1
Order By tblAssets.AssetName



Hopefully this may help someone in future.

Dog.

[MikeMc]

Sorry for not replying. Anyway, this table lumps roles and features together by the looks of things. You'd have to use a where filter to either exclude the items you don't want or only include the items you are wanting to see.

[SystemsIT]

Thank you MikeMC,

Perfect.

Question with this one, do Roles and the Features use the same FeatureUni ?

Could we separate out Roles from the Features

This way we could exclude the .net installs and other features?

[MikeMc]

This should get you started:

Select Top 10000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFeatureUni.featureName,
  tblFeatureUni.featureCaption
From tblAssets
  Inner Join tblFeature On tblFeature.AssetId = tblAssets.AssetID
  Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 2
Order By tblAssets.AssetName