
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-30-2012 07:54 PM
Is there an easy way to include just the Version of MS Office in the existing report?
Computer1|Department|...|
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 04:42 PM
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
6 REPLIES 6

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 04:50 PM
Perfect. Thanks for the assistance.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 04:42 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 04:30 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 02:18 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 03:25 PM
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?
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?

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2012 04:25 PM
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.
