Table CompCustom - Records missing

[FethS]

Hi,
finally I found a way to filter our reports not to show computers marked as ' Non-Active' in Custom Fields.
I was a bit surprised because all modified reports have been empty, means Green.

The cause is that in table 'CompCustom' the only existing records are the computers marked as 'Non-Active'. There are no records for 'Active' computers. When I mark a computer as 'Active' in 'Custom Fields' a record is created.
Is this normal?

I tried to create these records via SQL database scripts. When I execute the following SQL command the Lansweeper console is showing 'Command completed successfully'. But no records are created. SQL command:
INSERT INTO tblCompCustom
(computername)
SELECT computername
FROM tblComputers
where not exists (select * from tblCompCustom)

If I use the INSERT command and VALUES the records will be created.

Is there a way to create the records via SQL commands?

Any support is much appriciated.

Cheers
Sascha

[FethS]

That works fine.
Thanks a lot

[Hemoco]

try this:

Select Distinct dbo.tblComputers.Computername As Computer,
  dbo.tblOperatingsystem.Description, dbo.tblOperatingsystem.Caption,
  dbo.tblComputers.Lastseen As [Last seen]
From dbo.tblComputers Inner Join
  dbo.tblComputersystem On (dbo.tblComputers.Computername =
      dbo.tblComputersystem.Computername) Inner Join
  dbo.TsysLastscan On (dbo.tblComputers.Computername =
      dbo.TsysLastscan.Computername) Left Outer Join
  dbo.tblOperatingsystem On (dbo.tblComputers.Computername =
      dbo.tblOperatingsystem.Computername) Left Outer Join
  dbo.tblSoftware On (dbo.tblComputers.Computername =
      dbo.tblSoftware.ComputerName) Left Join
  tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername
Where dbo.tblComputers.Computername In (Select TsysLastscan_1.Computername
  From dbo.TsysLastscan TsysLastscan_1
  Where TsysLastscan_1.CFGname = 'Software') And tblComputersystem.Domainrole <
  '2' And
  dbo.tblComputers.Computername Not In (Select tblSoftware_1.ComputerName
  From dbo.tblSoftware tblSoftware_1
  Where
    tblSoftware_1.softwareName Like
    'Compatibility Pack for the 2007 Office 
system') And
  IsNull(tblCompCustom.State, 1) <> 2

[FethS]

Here you go:
Select Distinct dbo.tblComputers.Computername As Computer, dbo.tblOperatingsystem.Description, dbo.tblOperatingsystem.Caption, dbo.tblComputers.Lastseen As [Last seen] From dbo.tblComputers Inner Join dbo.tblComputersystem On (dbo.tblComputers.Computername = dbo.tblComputersystem.Computername) Inner Join dbo.TsysLastscan On (dbo.tblComputers.Computername = dbo.TsysLastscan.Computername) Left Outer Join dbo.tblOperatingsystem On (dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername) Left Outer Join dbo.tblSoftware On (dbo.tblComputers.Computername = dbo.tblSoftware.ComputerName) Left Outer Join dbo.tblProcesses On (dbo.tblComputers.Computername = dbo.tblProcesses.Computername) Inner Join tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername Where dbo.tblComputers.Computername In (Select TsysLastscan_1.Computername From dbo.TsysLastscan TsysLastscan_1 Where TsysLastscan_1.CFGname = 'Software') And tblComputersystem.Domainrole < '2' And dbo.tblComputers.Computername Not In (Select tblSoftware_1.ComputerName From dbo.tblSoftware tblSoftware_1 Where tblSoftware_1.softwareName Like 'Compatibility Pack for the 2007 Office system') And Not tblCompCustom.State = 2

[Hemoco]

You don't need to insert these records in table tblcompcustom.

Please post the report query that gives problems.

[FethS]

Thanks for the quick response.
I tried the SQL command:
INSERT INTO tblCompCustom
(computername)
SELECT tblComputers.computername
FROM tblComputers RIGHT JOIN tblCompCustom
ON tblComputers.computername = tblCompCustom.computername
where not exists (select * from tblCompCustom)

Result is saying 'Command completed successfully'. But the records are not created.

If use the following one:
INSERT INTO tblCompCustom
(computername)
SELECT tblComputers.computername
FROM tblComputers RIGHT JOIN tblCompCustom
ON tblComputers.computername = tblCompCustom.computername

then I get the following error message: Cannot insert the value NULL into column 'Computername', table 'lansweeperdb.dbo.tblCompCustom'; column does not allow nulls. INSERT fails

As I'm not a SQL guru any help would be much appreciated.
Thanks

[Hemoco]

FethS wrote:

The cause is that in table 'CompCustom' the only existing records are the computers marked as 'Non-Active'. There are no records for 'Active' computers. When I mark a computer as 'Active' in 'Custom Fields' a record is created.
Is this normal?

Yes

finally I found a way to filter our reports not to show computers marked as ' Non-Active' in Custom Fields.
I was a bit surprised because all modified reports have been empty, means Green.

You need to change the join between tblcomputer and tblcompcustom so that all records of tblcomputers are included.