cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
TelhioCU
Engaged Sweeper
I am trying to simply add a report to show the computers that do NOT have Trend Micro OfficeScan installed.

I have added the file to the scan options through the gui, and now am trying to add the report so it will show up in the web console.

I have been looking for an example of a report like this and have not found one.

Thanks,

TCU
5 REPLIES 5
quintinh
Engaged Sweeper
I don't know if anybody ever successfully finished this but there is a key ingredient missing from the above examples. If you want to know if it is installed or not, there is a field in tblFileVersions called Found that is of type bit. Type bit means there is a 0 for false and 1 for true. The statement below will give you all the comptuers where that file was not found and, therefore, is not installed.


SELECT TOP (100) PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblFileVersions.FileVersion, dbo.tblFileVersions.FileDescription, dbo.tblFileVersions.Filesize, dbo.tblFileVersions.Lastchanged
FROM dbo.tblComputers LEFT OUTER JOIN dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%PccNTMon.exe') AND (dbo.tblFileVersions.Found = 0)
ORDER BY Computer


You have to add the CREATE, GO and INSERT into tsysreports table for this to work in the gui console. There are plenty of examples in the other posts to guide you through that.
Hemoco
Lansweeper Alumni
Since you are a premium user it would be easier to just copy/paste the sql code in the query builder

SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblOperatingsystem.Description, dbo.tblFileVersions.FileVersion, dbo.tblFileVersions.FileDescription, dbo.tblFileVersions.Filesize, dbo.tblFileVersions.Lastchanged
FROM dbo.tblComputers INNER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe')
ORDER BY dbo.tblComputers.Computername


You also need to start the GUI management console and add "'%programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe" to the list of scanned files.
TelhioCU
Engaged Sweeper
I successfully created the scanned file, and the report.

However, in the web console the report shows up but I do not believe it is giving accurate results. I am pretty sure that there are a couple of machines that do not have Trend Micro Officescan on them.

Can you review this and see if I am accurate?

' This is a sample report that checks all computers if they have the latest version of OfficeScan Client
' The file that we need to check : %programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe

' Create the query to check for the installed application
' version is not relevant

CREATE VIEW dbo.web30repnotinstalledofficescan
AS
SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblOperatingsystem.Description, dbo.tblFileVersions.FileVersion, dbo.tblFileVersions.FileDescription, dbo.tblFileVersions.Filesize, dbo.tblFileVersions.Lastchanged
FROM dbo.tblComputers INNER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe')
ORDER BY dbo.tblComputers.Computername

GO

'Add the view to the reports table

INSERT INTO [tsysreports] ([Reportquery],[Reporttitle]) VALUES ('web30repnotinstalledofficescan','Not running OfficeScan')

GO

Thanks,

TCU
fleet
Engaged Sweeper
Hi,
Can you please help me import this script and tell me how and where can i see it?

thank you ,

DadiO
Perth Australia





TelhioCU wrote:
I successfully created the scanned file, and the report.

However, in the web console the report shows up but I do not believe it is giving accurate results. I am pretty sure that there are a couple of machines that do not have Trend Micro Officescan on them.

Can you review this and see if I am accurate?

' This is a sample report that checks all computers if they have the latest version of OfficeScan Client
' The file that we need to check : %programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe

' Create the query to check for the installed application
' version is not relevant

CREATE VIEW dbo.web30repnotinstalledofficescan
AS
SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblOperatingsystem.Description, dbo.tblFileVersions.FileVersion, dbo.tblFileVersions.FileDescription, dbo.tblFileVersions.Filesize, dbo.tblFileVersions.Lastchanged
FROM dbo.tblComputers INNER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%programfiles%\Trend Micro\OfficeScan Client\PccNTMon.exe')
ORDER BY dbo.tblComputers.Computername

GO

'Add the view to the reports table

INSERT INTO [tsysreports] ([Reportquery],[Reporttitle]) VALUES ('web30repnotinstalledofficescan','Not running OfficeScan')

GO

Thanks,

TCU
Hemoco
Lansweeper Alumni
This thread should help you : http://forum.lansweeper.com/yaf_postst611_Adobe-Reader-9-Help.aspx
First create the report using the report builder, afterwards add it to the interface using lsmanage