Report with no duplicates

[HaxEJxuK]

Hi,

I need to run a asset report for my director and having some issue with it. I've created custom reports and it's way bigger than I expected and captured information that I do not need. It also had lot of duplicate assetname. I've checked couple of asset reports in reports but doesn't have all the information that I need. I've tried to edit it but once I do that, I get duplicates.

What I'm trying to do is create a report that includes computername, domain, model, who it belongs to, location(IPlocation seems to do the job) maybe warranty status too

I want to create possibly 5 reports based on it.

* Laptops, Tablet(like touch screen laptop not iPad) desktops and docking stations

* iphone, Android phones, Blackberry and iPads (Is this possible?

* Printers

* Monitors

* Servers

I'm assuming best way to get rid of duplicate is to create a report based on serial number? Since duplicate seems to be created by different warranty status and such? There could be only one serial so I don't think this will create duplicate.

Thank you very much

[Hemoco · Accepted solution]

Please use the report below:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetID,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssets.Domain,
tsysOS.OSname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Description,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Microsoft Office%'
Order By tblAssets.AssetName

[HaxEJxuK]

Hi,

Another adjustment came down on me from management. I need to add assettype, firstname of user, last name of user after AssetID. Can you assist? Also, is there field that I can add which version of MS Office it's running? Or at least anything that has "Microsoft Office"(assuming that will include everything for me to filter out with excel_ If so, can you add it after OSname? Below is report that I have now.

[Hemoco]

Please use the following report:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Order By tblAssets.AssetName

[HaxEJxuK]

Thank you very much!!!!

As this is first time I'm using it, I'm adding and removing till I get it right and input from management. Hoping that this is last one, Can you create following in order?

Thank you very much again!

AssetName
Model
Domain
OSname
Description
Username
Serialnumber
IPLocation
Warrantydate

[Hemoco]

We have added warrantydate and serialnumber to the report:

Select Distinct Top 1000000 tblAssets.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Username,
tsysIPLocations.IPLocation,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tsysAssetTypes.AssetTypename = 'printer'

Also vm and servers will be included in the report. The number of records that the report returns will depend on what value you filter for the field assettypename.

[HaxEJxuK]

Also, total number of found seems to be changing? It was initially around 1000 but now shows close to 1500? Is this because of how filter was set up? Or does it change depending on if unit is on or off?

Thank you

[HaxEJxuK]

Thank you very much

Most of things are what I'm looking for but below are what I found out.

* Serial number is missing

* Warranty information should be expiration date instead of state

* It looks like isn't pulling VMs and servers instead of just work stations(I replaced yellow text with windows)

Thank you very much for this.

[Hemoco]

Here is the report that you are looking for. Please change the yellow word with the name of the type that you want to filter on.

Select Distinct Top 1000000 tblAssets.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Username,
tsysIPLocations.IPLocation,
Case When tblAssetCustom.PurchaseDate <= GetDate() Then 'valid'
Else 'not valid' End As 'warranty state'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tsysAssetTypes.AssetTypename = 'printer'