→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
runiuksp
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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.

View solution in original post

6 REPLIES 6
daly211
Engaged Sweeper
Worked perfectly!

Thanks
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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%')