Thursday, February 16, 2023

Reportserver query with wildcards

The community edition of Reportserver.net provide a large number of useful functions. This include managing user access, download in different formats, user freedom to customise the report and access from different databases.

Here is an example of string query with wildcard and parameters. The $P{email} is a parameter where you can enter a value.


SELECT user_id, email, registration_id, date, venue, type, status

FROM student_registration

WHERE email LIKE '%'||$P{email}||'%'

Another example to apply parameter ${timezone} which is the default handling of variable, in this case to add with date from created_at

SELECT user_id, email, registration_id, created_at::timestamp + ${timezone}::interval as created_at

FROM student_registration

Wednesday, February 15, 2023

Centos Stream 8 with Selinux and high CPU

In a server with only 1 CPU, there are times where Linux service fight to gain usage of resources. However, Selinux strict policies may cause a service to repeatedly demand usage that lead to 100% CPU usage.

Several ways to diagnose, which would include to determine the max connections and process running. The commands include

To view number of connection on the web server

ss -ant | grep -E ':80|:443' 

systemctl status httpd


To view number of processes running, sorted by CPU or memory.

top

Laravel and search for json content in postgresql database

This is a note on usage of JSON in Laravel 8.

Given a Postgresql database with a table name customer, that have a column name status it would look like this

status=[
{"id": 5, "name": "registered", "description": "Registered only","date":"20221002 013600}
]

The function to search for JSON includes;

whereJsonContains

orWhereJsonContains


Laravel sample code to retrieve all rows where status is either 2, 5, or 6 would look like this;

$filter = [2,5,6];

$customers = Customers::where(function ($q) use ($filter) {

    foreach ($filter as $val) {

        $q->orWhereJsonContains('customers.status', [['id' => $val]]);

    }

})->get( );


Blog Archive