Unauthorized Wifi Devices Report

[patrick_kelly]

I am attempting to generate a report which will include all the machines who have IP addresses on 3 specific IP locations I created based on 3 wifi networks. The machines that are not members of 1 of the 2 AD domains I am considering rouge machines and I want a report that includes their MAC addresses, machine names, types, OS, and IP address. Any ideas on this? So far I have the following which is not working out for me:

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypeIcon10 As icon,
tsysassettypes.AssetTypename,
tblassets.Domain,
tblassets.IPAddress,
tsysIPLocations.IPLocation,
tblassets.Lastseen,
tblassets.Lasttried,
tblassets.ManualMAC,
tblNetwork.MACaddress
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join lansweeperdb.dbo.tblNetwork On tblassets.AssetID =
tblNetwork.AssetID
Where (tblassets.Domain <> 'DOMAIN1' And tsysIPLocations.IPLocation =
'WIFINET1' And tblassetcustom.State = 1) Or
(tblassets.Domain <> 'DOMAIN2' And tsysIPLocations.IPLocation =
'WIFINET1' And tblassetcustom.State = 1)

[patrick_kelly · Accepted solution]

Figured it out:

Select Top 1000000 tblassets.AssetName As [Asset Name],
tsysAssetTypes.AssetTypename As [Asset Type],
tblassets.Domain As Domain,
tblassets.IPAddress As [IP Address],
tblassets.Mac As [Mac Address],
tsysIPLocations.IPLocation As [IP Location],
tblassets.Lastseen As [Last Seen],
tblassets.Lasttried As [Last Tried]
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join lansweeperdb.dbo.tsysAssetTypes On tblassets.Assettype =
tsysAssetTypes.AssetType
Where (tblassets.Domain != 'DOMAIN1' And tblassets.Domain != 'DOMAIN2'
And tsysIPLocations.IPLocation = 'WIFI1') Or
(tblassets.Domain != 'DOMAIN1' And tblassets.Domain != 'DOMAIN2' And
tsysIPLocations.IPLocation = 'WIFI2')

[patrick_kelly]

Updated my report and I'm getting closer but it's not showing devices that are not members of my domains. Still need help:

Select Top 1000000 tblassets.AssetName,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.Domain,
tblassets.IPAddress,
tsysIPLocations.IPLocation,
tblassets.Lastseen,
tblassets.Lasttried,
tblNetwork.MACaddress,
tblNetwork.Description,
tblassets.Assettype
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join lansweeperdb.dbo.tblNetwork On tblassets.AssetID =
tblNetwork.AssetID
Where (tblassets.Domain != 'DOMAIN1' And tblassets.Domain != 'DOMAIN2'
And tsysIPLocations.IPLocation = 'WIFI1') Or
(tblassets.Domain != 'DOMAIN1' And tblassets.Domain != 'DOMAIN2' And
tsysIPLocations.IPLocation = 'WIFI2' And
tblassetcustom.State = 1)

[Tharaka]

Can you explain what are DOMAIN1, DOMAIN2, WIFI1 and WIFI2 ?