→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
Is there an easy way to include just the Version of MS Office in the existing report?

Computer1|Department|...|

SELECT TOP 1000000 tblcomputers.computername,
tblcomputers.computerunique,
web40osname.compimage AS icon,
web40osname.osname,
tblcomputers.sp AS [Service Pack],
tblcompcustom.location,
tblcompcustom.department AS [Research Unit],
tblcompcustom.custom1 AS [User Name],
tsyschassistypes.chassisname AS Type,
tblcompcustom.barcode AS [Inventory Number],
tblbios.releasedate,
tblsystemenclosure.serialnumber
FROM tblcomputers
LEFT OUTER JOIN web40processorcapacity
ON tblcomputers.computername =
web40processorcapacity.computername
INNER JOIN web40activecomputers
ON tblcomputers.computername = web40activecomputers.computername
LEFT JOIN web40correctmemory
ON web40correctmemory.computername = tblcomputers.computername
LEFT JOIN web40osname
ON web40osname.computername = tblcomputers.computername
LEFT JOIN tblcomputersystemproduct
ON tblcomputers.computername =
tblcomputersystemproduct.computername
LEFT JOIN tblcompcustom
ON tblcomputers.computername = tblcompcustom.computername
LEFT JOIN tblbios
ON tblcomputers.computername = tblbios.computername
LEFT JOIN tblsystemenclosure
ON tblcomputers.computername = tblsystemenclosure.computername
LEFT JOIN tsyschassistypes
ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
WHERE tblcompcustom.department LIKE '%Unit%'
ORDER BY dbo.tblcomputers.computername


The following gives me any instance of "Office"

Computer1|Department|...|Office 2007
Computer1|Department|...|Office File Validation Add-In
Computer1|Department|...|Compatibility Pack for Office 2007

SELECT TOP 1000000 tblcomputers.computername,
tblcomputers.computerunique,
web40osname.compimage AS icon,
web40osname.osname,
tblcomputers.sp AS [Service Pack],
tblcompcustom.location,
tblcompcustom.department AS [Research Unit],
tblcompcustom.custom1 AS [User Name],
tsyschassistypes.chassisname AS Type,
tblcompcustom.barcode AS [Inventory Number],
tblbios.releasedate,
tblsystemenclosure.serialnumber,
tblsoftware.softwarename
FROM tblcomputers
LEFT OUTER JOIN web40processorcapacity
ON tblcomputers.computername =
web40processorcapacity.computername
INNER JOIN web40activecomputers
ON tblcomputers.computername = web40activecomputers.computername
LEFT JOIN web40correctmemory
ON web40correctmemory.computername = tblcomputers.computername
LEFT JOIN web40osname
ON web40osname.computername = tblcomputers.computername
LEFT JOIN tblcomputersystemproduct
ON tblcomputers.computername =
tblcomputersystemproduct.computername
LEFT JOIN tblcompcustom
ON tblcomputers.computername = tblcompcustom.computername
LEFT JOIN tblbios
ON tblcomputers.computername = tblbios.computername
LEFT JOIN tblsystemenclosure
ON tblcomputers.computername = tblsystemenclosure.computername
LEFT JOIN tsyschassistypes
ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
INNER JOIN tblsoftware
ON tblcomputers.computername = tblsoftware.computername
WHERE tblcompcustom.department LIKE '%Unit%'
AND tblsoftware.softwarename LIKE '%office%'
ORDER BY dbo.tblcomputers.computername
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Could you try running the report as shown below instead.
Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], Convert(Varchar(10),tblBIOS.ReleaseDate,20) As ReleaseDate, tblSystemEnclosure.SerialNumber, tblSoftware.softwareName, tblSoftware.softwareName As softwareName1 From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblCompCustom.Department Like '%Unit%' And tblSoftware.softwareName Like 'microsoft office%' And tblSoftware.softwareName Like '%20%' Order By dbo.tblcomputers.computername

View solution in original post

6 REPLIES 6
harringg
Champion Sweeper
Perfect. Thanks for the assistance.
Hemoco
Lansweeper Alumni
Could you try running the report as shown below instead.
Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], Convert(Varchar(10),tblBIOS.ReleaseDate,20) As ReleaseDate, tblSystemEnclosure.SerialNumber, tblSoftware.softwareName, tblSoftware.softwareName As softwareName1 From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblCompCustom.Department Like '%Unit%' And tblSoftware.softwareName Like 'microsoft office%' And tblSoftware.softwareName Like '%20%' Order By dbo.tblcomputers.computername
harringg
Champion Sweeper
When in the Report builder Results tab, it's the date, when in the Web Interface, it's the date/time and when I export to Excel it's date/time
Hemoco
Lansweeper Alumni
You could try something like this:
Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], tblBIOS.ReleaseDate, tblSystemEnclosure.SerialNumber, tblSoftware.softwareName From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblCompCustom.Department Like '%Unit%' And tblSoftware.softwareName Like 'microsoft office%' And tblSoftware.softwareName Like '%20%' Order By dbo.tblcomputers.computername
harringg
Champion Sweeper
That got me closer. I can dump to Excel and Filter from there to show only the titles I need.


Quick followup to that script. It does show the date field in the BIOS with the time as well.

2009-8-25 00:00

Is there a way to show only:

2009-8-25 on export?
Hemoco
Lansweeper Alumni
harringg wrote:
Quick followup to that script. It does show the date field in the BIOS with the time as well.

Time should not be included in the tblBIOS.ReleaseDate field. Could you double-check the report results.