Powershell GetADUser Name vs DisplayName

When using GetADUser usually we are using the default properties it return. This usually not the case and when we were using it as the property Name it doesn’t update when user’s name got updated. To facilitate this use DisplayName.

$user= Get-ADUser -Filter {UserPrincipalName -eq ’[email protected]’ -or EmailAddress -eq ’[email protected]’} -Properties EmailAddress,DisplayName

$user.DisplayName

PowerApps get user’s email

There is some instances that users have different userprincipalname vs their actual email due to an alias was used for some users. Because of this the following provide different values:

  1. User().Mail
  2. Office365Users.MyProfileV2().mail

I would recommend to factor in this scenarios to use the second as this provide coverage on users with alias on their account

Backup and Restore MSSQL Database with Temporal Tables

Some of us are now using Temporal Tables in most purpose to have historical information on our records. Its interesting that the current SSMS doesn’t have this available and using the said feature will result that Temporal Tables are not restored correctly.

To backup database with Temporal tables use the following code:

BACKUP DATABASE YourDatabase
TO DISK = ‘C:\Backup\YourDatabase.bak’
WITH FORMAT;

To restore the database use the following:

RESTORE DATABASE YourDatabase
FROM DISK = ‘C:\Backup\YourDatabase.bak’
WITH REPLACE;

Add another AD account as admin on AAD joined machine

There is a superuser article that was able to resolve this using the following steps:

  1. Login to the PC as the Azure AD user you want to be a local admin. This gets the GUID onto the PC.
  2. Log out as that user and login as a local admin user.
  3. Open a command prompt as Administrator and using the command line, add the user to the administrators group. As an example, if I had a user called John Doe, the command would be net localgroup administrators AzureAD\JohnDoe /add.

https://superuser.com/questions/982336/how-do-i-add-azure-active-directory-user-to-local-administrators-group

SQL: Run SQL Job via Stored Procedure

Here is stored procedure that you can use so that you can run your SQL Job manually via script. It already factored in to check the SQL JOB if its running already or not so that it will just wait that run to finish instead of queuing another one.

CREATE PROC [dbo].[usp_SQLJOB_EXEC] @JobName NVARCHAR(MAX)
AS
	DECLARE @JobID UNIQUEIDENTIFIER;

    -- Get the Job ID
    SELECT @JobID = job_id
    FROM msdb.dbo.sysjobs
    WHERE name = @JobName;

    -- Check if the job exists
    IF @JobID IS NOT NULL
    BEGIN
		DECLARE @IsJobRunning BIT = 1;
		SELECT @IsJobRunning = CASE WHEN EXISTS (
                SELECT 1
                FROM msdb.dbo.sysjobactivity
                WHERE job_id = @JobID
                AND run_requested_date >= DATEADD(dd, -1, GETDATE())
                AND stop_execution_date is null
            ) THEN 1 ELSE 0 END;
        -- Start the job
		IF(@IsJobRunning = 0)
		BEGIN
			EXEC msdb.dbo.sp_start_job @job_name = @JobName;
			SET @IsJobRunning = 1
		END
        

        -- Loop to check the job status
        WHILE @IsJobRunning = 1
        BEGIN
            -- Check if the job is still running
            SELECT @IsJobRunning = CASE WHEN EXISTS (
                SELECT 1
                FROM msdb.dbo.sysjobactivity
                WHERE job_id = @JobID
                AND run_requested_date >= DATEADD(dd, -1, GETDATE())
                AND stop_execution_date is null
            ) THEN 1 ELSE 0 END;

            -- Break the loop if the job is completed
            IF @IsJobRunning = 0
                BREAK;

            -- Wait for a few seconds before checking again
            WAITFOR DELAY '00:00:05'; -- Adjust the delay time as needed
        END;
    END
    ELSE
    BEGIN
        PRINT 'Job not found';
    END

SQL Job using LINK Server using Service Account

There are usual errors when using a LINK Server while also using a Service account on SQL Job. Usual errors are as follows:

  1. Access to the Remote Server is Denied Because the Current Security Context is not Trusted
  2. EXECUTE AS USER failed for the requested [user] dbo in the [database]
  3. After resolving those 2 issue its still failed

Assumptions of your setup:

  1. You have created a credential object under Security > Credentials
  2. You have created a proxy that is using that credential under SQL Server Agent > Proxies > Operating System (CmdExec)

Here are the steps you need to do:

  1. Change database TRUSTWORTY to ON

    ALTER DATABASE SQLDB0 SET TRUSTWORTHY ON
    GO

  2. Reapply database owner. This is needed because the SID recorded is now mismatch. c/o Rob Reid on this post.

    ALTER AUTHORIZATION ON Database::[database] TO [domain\user]
  3. Update your SQL Job step to use CmdExec instead of T-SQL

    Job Step Properties
    – Type: Operating system (CmdExec)
    – Run as: Your service account credentials using a proxy
    – Command
    SQLCMD -I -b -S $(ESCAPE_DQUOTE(SRVR)) -d DatabaseName -Q"EXEC usp_YourStoredProcedure"

PowerBI: Change calculation on child value of Pivot

Measure = 
VAR IsLowestLevel =
    HASONEVALUE(Table[RowColumn1]) &&
    HASONEVALUE(Table[RowColumn2]) &&
    HASONEVALUE(Table[RowColumn3]) &&
    HASONEVALUE(Table[RowColumn4]) &&
    HASONEVALUE(Table[RowColumn5])

VAR Is2ndToTheLowestLevel =
    HASONEVALUE(Table[RowColumn1]) &&
    HASONEVALUE(Table[RowColumn2]) &&
    HASONEVALUE(Table[RowColumn3]) &&
    HASONEVALUE(Table[RowColumn4]) 



RETURN
IFERROR(IF (
    IsLowestLevel,
    [BaseColumnToCompute1]/
    SUMX(
        FILTER(
            ALL(Table),
            Table[GlobalFilter1] = SELECTEDVALUE(Table[GlobalFilter1]) &&            
            Table[RowColumn1] = SELECTEDVALUE(Table[RowColumn1]) &&
            Table[RowColumn2] = SELECTEDVALUE(Table[RowColumn2]) &&
            Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn3]) &&
            Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn4])
        ),
        [BaseColumnToCompute1]+500 /*Sample custom compute*/
    ),
    IF (
        Is2ndToTheLowestLevel,
        [BaseColumnToCompute1]/
        (SUMX(
            FILTER(
                ALL(Table),
                Table[GlobalFilter1] = SELECTEDVALUE(Table[GlobalFilter1]) &&            
                Table[RowColumn1] = SELECTEDVALUE(Table[RowColumn1]) &&
                Table[RowColumn2] = SELECTEDVALUE(Table[RowColumn2]) &&
                Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn3])
            ),
            [BaseColumnToCompute1]+1000 /*Sample custom compute*/
        )),
        [DefaultBaseColumnToCompute1]
    )
),0)

Active Directory via C#

There is a requirement to perform CRUD operation on an Active Directory and we need to create it on C#. The following code will help an engineer accordingly:

Create

using (DirectoryEntry directoryEntry = new DirectoryEntry("LDAP:\\url\ou", "username", "password"))
                {
                    using (DirectoryEntry newUser = directoryEntry.Children.Add("CN=Name", "User"))
                    {
                        if (!DirectoryEntry.Exists(newUser.Path))
                        {
                            newUser.Properties["property1"].Add(propertyValue);
                            newUser.CommitChanges();
                            ret = true;
                        }
                    }
                }

Update

using (DirectoryEntry directoryEntry = new DirectoryEntry("LDAP:\\url\ou", "username", "password"))
                {
                    using (DirectorySearcher search = new DirectorySearcher(directoryEntry))
                    {
                        search.Filter = String.Format("(IdentifierProperty={0})", identifier);
                        search.PropertiesToLoad.Add("property1");
                        SearchResult result = search.FindOne();
                        if (result != null)
                        {
                            using (DirectoryEntry entryToUpdate = result.GetDirectoryEntry())
                            {
                                entryToUpdate.Properties["property1"].Value = "property1value";
                                entryToUpdate.CommitChanges();
                                ret = true;
                            }
                        }
                    }
                }

Delete

using (DirectoryEntry directoryEntry = new DirectoryEntry("LDAP:\\url\ou", "username", "password"))
                {
                    using (DirectorySearcher search = new DirectorySearcher(directoryEntry))
                    {
                        search.Filter = String.Format("(IdentifierProperty={0})", identifier);
                        search.PropertiesToLoad.Add("property1");
                        SearchResult result = search.FindOne();
                        if (result != null)
                        {
                            using (DirectoryEntry entryToUpdate = result.GetDirectoryEntry())
                            {
                                directoryEntry.Children.Remove(entryToUpdate);
                                directoryEntry.CommitChanges();
                                ret = true;
                            }
                        }
                    }
                }

Tip: Healthcard doesn’t cover Pandemic

Did you know that COVID-19 was already declare by WHO as Pandemic. Currently DOH has not yet declared it as Pandemic.

If DOH happened to declare COVID-19 as Pandemic, healthcard will not cover it anymore.

Source: https://economictimes.indiatimes.com/wealth/insure/health-insurance/your-health-insurance-policy-might-not-cover-coronavirus-in-these-situations/articleshow/74570246.cms

MSSQL Grant Execute Stored Procedure only

There are cases that we only want to grant read-write only access then also enable users to execute stored procedure with in the SQL Server database.

In order to do this we will need to:

  1. Create a new Role named, db_exec_storedprocedure

    CREATE ROLE db_exec_storedprocedure
  2. Grant that role with execute stored procedure

    GRANT EXECUTE TO db_exec_storedprocedure
  3. Then add the user as a member of that role.

That’s it you can now grant specific permission to execute stored procedure.

God Bless!
Thanks,
Thomie

Azure DevOps Export to Excel Error TF400051

When requesting an Open in Excel a pop-up shows this error.

TF400051: Cannot process URL when attempting to open a query in Excel….

Azure DevOps

This is maybe your installed Visual Studio (VS) that is a lower version recently. Example. Before you’ve install VS 2017 and then VS 2019, but recently you’ve install VS 2010 due to a legacy project support. That sometimes changes a registry that produce this error.

To fix it follow the following:

  1. Open RegEdit
  2. Navigate to

    Computer\HKEY_CLASSES_ROOT\tfs\shell\open\command
  3. Change the value to the following

    C:\Program Files\Common Files\Microsoft Shared\Team Foundation Server\15.0\TfsProtocolHandler.exe “%1”
  4. Save the changes.

That’s it. Try exporting again and it should work now.

God Bless!
Thanks,
Thomie

Dynamic Object in C#

In my experience there was a need to create an object and its properties dynamically. Namely as follows:

thisIsMyObject.MyProperty1 = "AnyValueOfAnyType";
thisIsMyObject.MyProperty2 = true;
thisIsMyObject.MyProperty3 = 1;

To do this there could be different approaches like using a dictionary

Dictionary<string,object> c = new Dictionary<string,object>();
//adding a value
c.Add("MyProperty1", "AnyValueOfAnyType");
c.Add("MyProperty2", true);
c.Add("MyProperty3", 1);
//getting the value
c["MyProperty1"]; //AnyValueOfAnyType
c["MyProperty2"]; //true
c["MyProperty3"]; //1

But you may also use a dynamic object like as follows

var c = new System.Dynamic.ExpandoObject() as IDictionary<string, Object>; //namespace for note only
//adding a value
c["MyProperty1"] = "AnyValueOfAnyType";
c["MyProperty2"] = true;
c["MyProperty3"] = 1;
//getting the value
c["MyProperty1"]; //AnyValueOfAnyType
c["MyProperty2"]; //true
c["MyProperty3"]; //1

This way its more presentable and like using an array.

God Bless!

Thanks,
Thomie

Visual Studio Extension -Ngrok

Ngrok is now widely being used. From simple web development up to complex development. Is hard to run Ngrok everytime you are coding specially when using IIS Express that if not configured to use a specific IP it will generate random port which is a hard to map.

Thankfully there is a plug-in for our beloved Visual Studio to integrate this.

Ngrok Extensions

With Ngrok Extension we only open our Visual Studio solution and navigate to Tool>Start ngrok Tunnel and it will create a tunnel for the websites under your solution.

Try it now!

God Bless!

Thanks,
Thomie

Ngrok – Free Web Tunneling

In the verge of today’s web development we encounter cases that in order to proceed we need to have a public accessible URL. Thankfully, Ngrok provide a free service to do this.

What it does is tunnel your machine to a Ngrok URL with a specific port. We just need to download the ngrok.exe from their website and run the command like

ngrok http 80


With this command your service on your local machine that is running on port 80 will be mapped and accessible publicly for FREE. For a full documentation view it here.

God Bless!

Thanks,
Thomie

Want to convert your load to cash?

I was once a user of load that expires. Expiring load is a past as with the ability to convert load to cash emerges with LoadToCash

LoadToCash support the following:

  1. All SIMs
  2. Postpaid and Prepaid
  3. Unlimited conversions
  4. Several options to cashout

Availing the service is just a app away. Download LoadToCash via Play Store. Register. Convert. and then Wait.

LoadToCash allows cashout to the following:

  1. BDO
  2. BPI
  3. GCash
  4. Coins.ph
  5. Paymaya

Just a tip your consumables is not allowed to be pasaload. But they can be converted via LoadToCash.

God Bless!

Thanks,
Thomie

Adding Proxy on Web.Config

Hi,

There are some cases when developing your web application on .net you may experience proxy issues when you are accessing APIs on your application. To fix this you may add this on your web.config/app.config under the configuration section

 

<system.net>
<defaultProxy useDefaultCredentials=”true”>
<proxy usesystemdefault=”true” proxyaddress=”http://proxy:8080″ bypassonlocal=”true” />
<bypasslist />
</defaultProxy>
</system.net>

God Bless!

Thanks,
Thomie

Compress files on windows via Command Line

Hi,

There are time we want to perform things via CLI. Today I encountered to perform compression via CLI for my windows machine as it has problem performing the task via the UI. Here are the codes:

  • Compression

compact /c /s:”full path of folder” /i /Q

  • Uncompress

compact /u /s:”full path of folder” /i /Q

That’s it

Source: https://www.tenforums.com/tutorials/26340-compress-uncompress-files-folders-windows-10-a.html

Review: Tab Manager

In a world of development cycle we open several tabs for research and development, requirement gathering, and even when presenting. In cases like this we want to open tabs simultaneously. Most of us will do this by creating folder on our Bookmarks and open them up. For me I use Tab Manager on chrome to do this. Its so easy.

  1. Open the tabs you want
  2. Name them on Tab Manager
  3. Then click the + sign

Thats it. It will save that as a group and with just one click you will open them all. The best part of it is its free and it sync the data along with your google account. Download it now at https://chrome.google.com/webstore/detail/tab-manager/mcidendbndlekegaphipeeoaemckemhm

God Bless!

Thanks,
Thomie

Fix SignalR OnDisconnected not working

This just come up and after searching and testing it like to Fix SignalR OnDisconnected if not working is to implement on the client at least one method so that this method will be called. Think of it like a requirement.

Example for the SignalR residing on the same server

<!– Reference to jQuery –>
<!– Reference to SignalR –>
<script src=”~/hubs/signalr“></script>
<script>
var onlineHubProxy = $.connection.onlineHub;

onlineHubProxy.client.void = function(){
//do nothing
};

$.connection.start();

</script>

Then try again now and the Default methods of hubs will now be working properly.

God Bless!

Thanks,
Thomie