Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-27-2013 07:44 PM
I would like pull a report exiting following code but with the specific wild card asset names beginiing with UKTRT only. But with the Last User Display name form the AD Display Name which shows the full name of the AD account last logged in.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By tblAssets.IPNumeric
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By tblAssets.IPNumeric
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-27-2013 08:02 PM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblADusers.Displayname
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetName Like '%UKTRT%'
Order By tblAssets.IPNumeric
This should work, sorry if it doesn't.
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblADusers.Displayname
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetName Like '%UKTRT%'
Order By tblAssets.IPNumeric
This should work, sorry if it doesn't.
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-28-2013 07:34 PM
Perfect, just wha tI was looking for
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-27-2013 08:02 PM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblADusers.Displayname
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetName Like '%UKTRT%'
Order By tblAssets.IPNumeric
This should work, sorry if it doesn't.
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblADusers.Displayname
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetName Like '%UKTRT%'
Order By tblAssets.IPNumeric
This should work, sorry if it doesn't.