Simple software report

[runiuksp]

Hello,

I'm not very familiar with SQL hence asking here

I need a report that will show all computers with software called P2 installed

Also it will need to show username, IP location, PC model and make

Thanks

[daly211]

Worked perfectly!

Thanks

[Hemoco]

Please use the following report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged,
tblAssets.Username
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where
tblRegistry.Regkey Like '%HKEY_LOCAL_MACHINE\SOFTWARE\Sema Group\p2\client'
And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[runiuksp]

Many thanks for the report.


I've created another one that I need only IPLocation added:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged,
tblAssets.Username
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where
tblRegistry.Regkey Like '%HKEY_LOCAL_MACHINE\SOFTWARE\Sema Group\p2\client'
And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

Could someone possibly help?

Regards

[Hemoco · Accepted solution]

Use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As Login,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%P2%')

To use the report above, do the following:
•Open the report builder under Reports/Create New Report.
•Paste the SQL code we provided at the bottom of the page.
•Left-click somewhere in the upper section of the page so the code applies.
•Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.

[daly211]

Lansweeper wrote:
Use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As Login,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%P2%')

To use the report above, do the following:
•Open the report builder under Reports/Create New Report.
•Paste the SQL code we provided at the bottom of the page.
•Left-click somewhere in the upper section of the page so the code applies.
•Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.

I took this report to find assets among my group that have the program "Java" installed by just changing the P2 to Java and it worked perfectly! My questions is, how can I make this report tell me what assets DON'T have java installed?

[Hemoco]

daly211 wrote:

Lansweeper wrote:
Use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As Login,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%P2%')

To use the report above, do the following:
•Open the report builder under Reports/Create New Report.
•Paste the SQL code we provided at the bottom of the page.
•Left-click somewhere in the upper section of the page so the code applies.
•Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.

I took this report to find assets among my group that have the program "Java" installed by just changing the P2 to Java and it worked perfectly! My questions is, how can I make this report tell me what assets DON'T have java installed?

Please use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As Login,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%Java%')