MSSQL template preprocessing errors.

[banff_rocky]

From my Zabbix Linux VM, I got the ODBC connection and the front end template working but the only data I am getting is job successful, version of sql server, last backup etc. For the rest of the items, I am getting these errors in the screen shot.
Host Macros:

{$MSSQL.DSN} = db01
{$MSSQL.INSTANCE} = MSSQL$db1
{$MSSQL.PASSWORD}
{$MSSQL.USER}

[banff_rocky]

Any tips here on the cause of the issue?

[cyber]

As error says "no data matches the specified path"... Verify, what is coming out to (master) item and is there all the data, what you actually try to extract.

[PeterZielony]

it could be permission issue for user used to query SQL

[banff_rocky]

As error says "no data matches the specified path"... Verify, what is coming out to (master) item and is there all the data, what you actually try to extract.

Get Performance counter is the master and a lot of the other items are dependent on that. There is no data in coming in for Performance counter. It has the follow SQL query:

SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Version' as counter_name,@@version as instance_name,0 as cntr_value
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Uptime' as counter_name,'' as instance_name,DATEDIFF(second,sqlserver_start_time ,GETDATE()) as cntr_value
FROM sys.dm_os_sys_info
UNION
SELECT '{$MSSQL.INSTANCE}:Databases' as object_name,'State' as counter_name,name as instance_name,state as cntr_value
FROM sys.databases
UNION
SELECT a.object_name,'BufferCacheHitRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Buffer cache hit ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Buffer cache hit ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager'
UNION
SELECT a.object_name,'WorktablesFromCacheRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Worktables From Cache Base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Worktables From Cache Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods'
UNION
SELECT a.object_name,'CacheHitRatio' as counter_name,'_Total' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Cache Hit Ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Cache Hit Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total'​

Should I execute this query on the database as the zbx_monitor user? Replace the {$MSSQL.INSTANCE} with the real instance name?

[PeterZielony]

Get Performance counter is the master and a lot of the other items are dependent on that. There is no data in coming in for Performance counter. It has the follow SQL query:

SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Version' as counter_name,@@version as instance_name,0 as cntr_value
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Uptime' as counter_name,'' as instance_name,DATEDIFF(second,sqlserver_start_time ,GETDATE()) as cntr_value
FROM sys.dm_os_sys_info
UNION
SELECT '{$MSSQL.INSTANCE}:Databases' as object_name,'State' as counter_name,name as instance_name,state as cntr_value
FROM sys.databases
UNION
SELECT a.object_name,'BufferCacheHitRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Buffer cache hit ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Buffer cache hit ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager'
UNION
SELECT a.object_name,'WorktablesFromCacheRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Worktables From Cache Base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Worktables From Cache Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods'
UNION
SELECT a.object_name,'CacheHitRatio' as counter_name,'_Total' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name='Cache Hit Ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Cache Hit Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total'​

Should I execute this query on the database as the zbx_monitor user? Replace the {$MSSQL.INSTANCE} with the real instance name?

instance should be set as macro, but i don't think you need to manipulate code. Does the user you using for query DB got permission to read master db? Also can you see metrics from performance counters in Windows for MSSQL?

On zabbix proxy/server used to query did you set STARTODBCPOLLERS?

[banff_rocky]

instance should be set as macro, but i don't think you need to manipulate code. Does the user you using for query DB got permission to read master db? Also can you see metrics from performance counters in Windows for MSSQL?

On zabbix proxy/server used to query did you set STARTODBCPOLLERS?

I edited the zabbix_server.conf file and uncommented:
StartODBCPollers
Number of pre-forked ODBC poller instances.

Mandatory: no
Range: 0-1000
Default:
StartODBCPollers=1

Once I restarted the service, it failed with an error code. Commenting them back let the service start as normal.

Yes, using performance monitor I can see memory usage for example. The zabbix user on MSSQL server has db_reader and public permissions.

[PeterZielony]

I edited the zabbix_server.conf file and uncommented:
StartODBCPollers
Number of pre-forked ODBC poller instances.

Mandatory: no
Range: 0-1000
Default:
StartODBCPollers=1

Once I restarted the service, it failed with an error code. Commenting them back let the service start as normal.

Yes, using a performance monitor I can see memory usage for example. The zabbix user on MSSQL server has db_reader and public permissions.

That's the problem. You need them to query SQL

Are you uncommenting the whole block?
it should look like this (depends how many SQL connections you need - 5 is lowest I'd use to query metrics)
#StartODBCPollers
[HASHTAG="t1783"]number[/HASHTAG] of pre-forked ODBC poller instances.
#
#Mandatory: no
#Range: 0-1000
#Default:
StartODBCPollers=5

If that's the case, are you getting list of databases when running manually "db.odbc.discovery" item from discovery?

I'm guessing you did setup ODBC driver on server?

[banff_rocky]

That's the problem. You need them to query SQL

Are you uncommenting the whole block?
it should look like this (depends how many SQL connections you need - 5 is lowest I'd use to query metrics)
#StartODBCPollers
[HASHTAG="t1783"]number[/HASHTAG] of pre-forked ODBC poller instances.
#
#Mandatory: no
#Range: 0-1000
#Default:
StartODBCPollers=5

If that's the case, are you getting list of databases when running manually "db.odbc.discovery" item from discovery?

I'm guessing you did setup ODBC driver on server?

Ok, just uncommenting StartODBCPollers=5 and restarting the service didn't result in any issues. Yes, I have ODBC driver installed and I can connect to the database using isql command. Still getting the preprocessing errors.

[Piotrekzielony]

Are you getting any results from any odbc SQL query item from frontend?
That would tell us if frontend can make queries to db.

It can be simple select statement

[banff_rocky]

Are you getting any results from any odbc SQL query item from frontend?
That would tell us if frontend can make queries to db.

It can be simple select statement

In the front end all I am gettting is last diff backup, last full backup, last log backup. I am also getting success, last runtime, next runtime on hundreds of jobs.

[Piotrekzielony]

So if you getting something via sql then your user doesnt have some permission to run this query it seems.

try running sql where you asked if you can replace instance macro with real instance as zbx user and see if you getting values then.

try in zabbix frontend and also using mssql studio.

Without seeing logs, errors or actual results that this query retuns we are just guessing