Custom date in Database monitor item query

[b3owu1f]

Greetings everyone,

I have a situation that I need to monitor values from a database using ODBC connector. I've managed to configure and retrieve the data correctly, the sql connection is all correct.

My problem is, I have to execute query over a database which the name changes daily.in the following format: Database_07142022.

My query is like: select name from Database_07142022 where name = Teste limit 1;

So everyday the database name is changed to current date in MMDDYYYY format. I need the item to be able to input the current date when it executes the query to be able to work everyday.

I've checked the macro {DATE} but is format is yyyy.mm.dd so it does not fit.

Any idea on how can I achieve that?

[Hamardaban]

Create a custom macro at the global level.
And change it daily via the API https://www.zabbix.com/documentation...o/updateglobal

[b3owu1f]

I'm trying to use this solution from this post: https://www.zabbix.com/forum/zabbix-...-key-parameter

I edited a little de code because it didn't have de format date I needed and I need just one specifc. I'm not a programmer so I'm not much used to js. I created the item as said below from the original idea, and I get this return message in information from the item:

Value "{"login":{"result":{"jsonrpc":"2.0","error":{"cod e":-32500,"message":"Application error.","data":"Incorrect user name or password or account is temporarily blocked."},"id":1},"status":200},"getMacro":[],"updateMacro":[],"logout":{}}" of type "string" is not suitable for value type "Numeric (unsigned)"

I've set the variables for user, pass and url in the template as it's said, and used a superadmin user. It does seems it's trying to connect as when I logged in in the Zabbix frontend I was informed there was failed attempts of login. So Idk what the issue can be. Zabbix is on version 5.0. Really any help is appreciated.

Follows bellow the solution from the other post but with my edited code.

First option if single item is needed - create dummy calculated item, schedule to run it every midnight. Add "1" in formula, it doesn't matter. In Preprocessing add JavaScript step with code (formatting gets messed up when pasting text here, sorry):

/**
/**
* Get date in predefined formats
* @param format
* Formats allowed:
* - mmddyyyy
* @param relativeDay Relative day number (0 for today, -1 for yesterday, +1 for tomorrow etc.)
*/
function getDate(format, relativeDay) {
var today = new Date();
if (relativeDay) {
today.setDate(today.getDate() + relativeDay);
}
var dd = today.getDate();
var mm = today.getMonth() + 1;
var yyyy = today.getFullYear();
if (dd < 10) {
// @ts-ignore
dd = '0' + dd;
}
if (mm < 10) {
// @ts-ignore
mm = '0' + mm;
}
return mm + dd + yyyy;
}


/**
* Make request to Zabbix API
* @param payload JSON data https://www.zabbix.com/documentation/current/manual/api
*/
function postToZabbixApi(payload) {
var resp;
try {
var req = new CurlHttpRequest();
req.AddHeader('Content-Type: application/json-rpc');
var result = JSON.parse(req.Post(zabbix_url, JSON.stringify(payload)));
resp = { result: result, status: req.Status() };
}
catch (error) {
Zabbix.Log(3, error);
resp = { error: "Error while calling '" + payload.method + "': " + error };
}
return resp;
}


/** Zabbix API url - will be resolved by Zabbix template level macro */
var zabbix_url = "{$ZABBIX.API.URL}";
/** Zabbix API username - will be resolved by Zabbix template level macro */
var username = "{$ZABBIX.API.USERNAME}";
/** Zabbix API password - will be resolved by Zabbix template level macro */
var password = "{$ZABBIX.API.PASSWORD}";
/** Global macros to update, name WITHOUT CURLY BRACES!!! */
var macros = [
{ name: "$GLOBAL.TODAY.MMDDYYYY", format: "MMDDYYYY", day: 0 }
];
var resp = {
login: {},
getMacro: [],
updateMacro: [],
logout: {}
};
var loginPayload = {
jsonrpc: "2.0",
method: "user.login",
params: {
user: username,
password: password
},
auth: null,
id: 1
};
resp.login = postToZabbixApi(loginPayload);
if (!resp.login.result || !resp.login.result.result) {
// @ts-ignore
return JSON.stringify(resp);
}
/** Zabbix auth token */
var auth_key = resp.login.result.result;
macros.forEach(function (macro) {
var getMacroPayload = {
jsonrpc: "2.0",
method: "usermacro.get",
params: {
output: "extend",
globalmacro: true,
filter: {
macro: "\{" + macro.name + "\}"
}
},
auth: auth_key,
id: 1
};
var _res = postToZabbixApi(getMacroPayload);
resp.getMacro.push(_res);
if (!_res.result || !_res.result.result || _res.result.result.length > 1 || !_res.result.result[0].globalmacroid) {
// @ts-ignore
return JSON.stringify(resp);
}
var updateMacroPayload = {
jsonrpc: "2.0",
method: "usermacro.updateglobal",
params: {
globalmacroid: _res.result.result[0].globalmacroid,
value: getDate(macro.format, macro.day)
},
auth: auth_key,
id: 1
};
resp.updateMacro.push(postToZabbixApi(updateMacroP ayload));
});
var logoutPayload = {
jsonrpc: "2.0",
method: "user.logout",
params: [],
auth: auth_key,
id: 1
};
resp.logout = postToZabbixApi(logoutPayload);
// @ts-ignore
return JSON.stringify(resp);

In my example return type is Text so you should use this as type of information. Save item. Now add macros {$ZABBIX.API.URL}, {$ZABBIX.API.USERNAME}, {$ZABBIX.API.PASSWORD} to same host/template + their values e.g. https://your-zabbix.com/zabbix/api_jsonrpc.php. Once per day Global Macro {$GLOBAL.TODAY.DDMMYYYY} + few others will be updated. Now you can use them in your items.