TSQL - Simple Table Size Query

Simple query for table size information.

SELECT t.Name,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.Name
HAVING SUM(a.used_pages) > 0
ORDER BY TotalSpaceKB Desc

TSQL - Set A Variable While Updating

I needed to set the value in a variable and update a field at the same time This is the update query.

DECLARE @numberOfCountersNeeded as int;
DECLARE @newcounterval as bigint;
SET @numberOfCountersNeeded = 3
UPDATE c --get the counter interval and update the current count
SET @newcounterval = somefield = somefield + @numberOfCountersNeeded
FROM sometable c
WHERE conditions

TSQL - Delete Duplicate Rows

This was the solution to a problem that was posted on Reddit. The OP asked how to delete a duplicate row. In the question, they just needed to delete a single duplicate. But then I wondered about cases where there was more than a single duplicate. In the example, EmployeeID is the primary key.

This is the finale SQL:

DECLARE @nC int; --number of records to delete
DECLARE @id int = 5; --key

SELECT @nc = count(*)-1 from [dbo].[TestTable]
WHERE EmployeeID = @id

DELETE top (@nc) from [dbo].[TestTable]
WHERE EmployeeID = @id

Salesforce - Error When Testing an Email in Flow

I was working on a flow in Salesforce and I was get an error in the email template. The error was: Error Occurred: We don't recognize the field prefix Opportunity. Associate a record that matches the prefix or update the template to remove the merge field from the body, subject, or letterhead. The error occured because I didn't have the Related Record ID set on the Send Email element.

Golang - Create Azure User

Function to ccreate a new user account in Azure using msgraph-sdk-go:

// creates a user in Azure with a minimal number of properties set
func CreateBaseAzureUser(strDisplayName, strMailNickName, strUserPrincipleName, strPassword, strEmployeeId string)(error){
	logger.NLog.Info().Msg("CreateBaseAzureUser Started: dn: " + strDisplayName + " mnn: " + strMailNickName + " upn: " + strUserPrincipleName + " eid: " + strEmployeeId)
	//employee id check: check if there is already a current user with the same employee id 
	//get the base azure user properties by employeeId
	user, err := AzureGetBaseUsersByPropertyFilter("employeeId", strEmployeeId)
	if user != nil {
		if len(user.GetValue()) != 0 {
			logger.NLog.Error().Err(err).Msg("CreateBaseAzureUser Error: " + strEmployeeId)
			strM := "An Azure user with the employeeID " + strEmployeeId + " already exits."
			utils.PostMessageT("it-help@internews.org", "Azure Account Creation Error for Emp. ID: " + strEmployeeId, strM)
			return errors.New(strM)
		}
	} 	
	requestBody := graphmodels.NewUser()
	accountEnabled := true
	// Enable the account
	requestBody.SetAccountEnabled(&accountEnabled) 
	// Display Name
	requestBody.SetDisplayName(&strDisplayName) 
	// Mail Nick Name
	requestBody.SetMailNickname(&strMailNickName) 
	// User Principal Name
	requestBody.SetUserPrincipalName(&strUserPrincipleName)
	// Employee ID
	requestBody.SetEmployeeId(&strEmployeeId)
	// Password
	passwordProfile := graphmodels.NewPasswordProfile()
	forceChangePasswordNextSignIn := true
	passwordProfile.SetForceChangePasswordNextSignIn(&forceChangePasswordNextSignIn) 
	passwordProfile.SetPassword(&strPassword) 
	requestBody.SetPasswordProfile(passwordProfile)

	if azureGgraphConnector == nil{
		err := InitializeAzureGraph()
		if err != nil{
			logger.NLog.Error().Err(err).Msg("CreateBaseAzureUser InitializeAzureGraph Error")
			utils.PostMessageT("it-help@internews.org", "CreateBaseAzureUser InitializeAzureGraph Error for Emp. ID: " + strEmployeeId, err.Error())
			return err
		}
	}
	
	users, err := azureGgraphConnector.AppClient.Users().Post(context.Background(), requestBody, nil)

	if err != nil {
        logger.NLog.Error().Err(err).Msg("CreateBaseAzureUser error: " + strEmployeeId + " " + strDisplayName)
		utils.PostMessageT("it-help@internews.org", "Azure Account Creation Error: " + strEmployeeId + " " + strDisplayName, err.Error())
        return err
    } else {
		logger.NLog.Info().Msg("CreateBaseAzureUser user created: " + *users.GetDisplayName())
	}
	return nil
}

TSQL - Update Using A List of Values

I needed to update around 800 addresses from a list of addresses in a spreadsheet. I saved the spreadsheet as csv and then formated the csv fields into a list of values. Then I wrote an SQL select statement that took the list of values and made it into a subquery that I could join on the address table.

This is the finale SQL with just a subset of the values:

update aglA 
set aglA.telephone = lh.phone
from address aglA 
join (select * from (values('100000','1 707 822 4226'),('100010','1 707 826 9681'),('100030','1 707 445 8248'))as x( res_id, phone))lh on lh.res_id = aglA.res_id 

Salesforce - Export Developer Console Query Results

After the query is run, rught click on the query results and select Inspect. Look for the table tag, and then click Copy and then Copy element. Paste the copied results into Excel.

Logitech C920 - Not Recognized by Windows 11

For me, the issue was caused by the USB ports. The webcam would not work on the USB hub that I was using with my laptop, a ThinkPad, and it would only work on certain ports on the ThinkPad USB-C Dock Gen 2.

Windows 10 Polish Key Combinations

Install the Polish language pack. Then use the following key combinations for the Polish characters:

Right-Alt + Z = ż
Right-Alt + X = ź
Right-Alt + S = ś
Right-Alt + C = ć
Right-Alt + L = ł
Right-Alt + O = ó
Right-Alt + E = ę
Right-Alt + A = ą
Right-Alt + N = ń

A Simple Collatz Proof

A Simple Collatz Proof Proof by exhaustion and reductio ad absurdum.

1. It has already been proven that the Collatz conjecture is true for a large set of odd numbers. This is the set Po. The largest number in the set is Pn.
2. The Collatz series for all numbers n repeats at the interval 4n + 1. This set, the extended set of the Collatz series for all odd numbers, is the set Pi. For Pn, there are an infinite subset of numbers in Pi that are greater than Pn. This is the set, Pm.
3. Consider the next highest odd number, an odd number that is the largest number in the Po set plus two: N = Pn + 2.
4. Either some number in the Collatz series for N will be equal to a number in the Pm set or a number in the Po set, or they will not. If no number in the Collatz series for N is equal to a number in either set, there would exist an infinite set of odd numbers where the Collatz conjecture is false, with a series of numbers that never intersect with the series of numbers where the conjecture has been proven true. Given the size of the Pm set and the Po set, this is impossible! Therefore, the conjecture is true for all numbers.
5. Q.E.D.

Note: I separated out the Pm set to show that for any odd number that is tested, there will always be an infinite set of odd numbers known to be true for Collatz that are greater than the number being tested. So, even when the numbers in a series are rising, they can still intersect with a number that, indirectly, has been proven true for the conjecture.

Example for step 2: For n = 27, the Collatz series will be the same for 27, 4(27) + 1, 4(4(27) +1) + 1, 4(4(4(27) +1) + 1) + 1, and so on. And this would extend with all of the numbers that make up the Collatz series for 27: 41, 31, 47, 71, 107, 161, 121, 91, 137, 103, 155, 233, 175, 263, 395, 593, 445, 167, 251, 377, 283, 425, 319, 479, 719, 1079, 1619, 2429, 911, 1367, 2051, 3077, 577, 433, 325, 61, 23, 35, 53, 5, and 1.

Windows - Microsoft Edge browser pops up after logging in

I've been dealing with this issue on a new laptop and had an aha moment this morning. The lock screen settings in MS Windows might cause the browser to open after you log in when the lock screen preview background is set to Windows spotlight. This behavior seems to be inconsistant.

Golang - "no required module provides package" error

We all make simple mistakes every now and then. In my case, I missplelled a file name, so the file extension was not ".go". It took me some time to figure out because of the error message.

Math - My Collatz Conjecture Proof

While doing some math courses on Coursera, I came accross the Colatz conjecture and decided to give a try at the proof. I show that the Collatz function is equal to te function f(n) = 3n + b, where n is the number and b is the least significant bit (LSB).
Link to my proof.

jQuery - AJAX to change the value in an Input box

I made a form with two input boxes, one with an id of vaue2 and another with an id of value3. The value in the value3 input gets loaded with a value from the database when I tab from value2 to value3. This is the form:

<form action="{{.Page}}" name="form1" id="form1" method="POST" target="iframename" onsubmit="event.stopPropagation(); event.preventDefault();">
<table id="input4Form" style="vertical-align: middle;">
	<tr>
		<td></td><td colspan="4"><b>{{.Title}}</b></td>
	</tr>
	<tr>
		<td style="text-align: right;">{{.Label1}}</td>
		<td><input id="value1" type="text" name="value1" title="{{.In1_Title}}" maxlength="60" onkeydown="if (event.keyCode == 13) document.getElementById('input2search').click()"/></td>
		<td>  </td>
		<td style="text-align: right">{{.Label2}}</td>
		<td><input id="value2" type="text" name="value2" title="{{.In2_Title}}" maxlength="60" onkeydown="if (event.keyCode == 13) document.getElementById('input2search').click()"/></td>
		<td>  </td>
		<td style="text-align: right">{{.Label3}}</td>
		<td><input id="value3" type="text" name="value3" title="{{.In3_Title}}" maxlength="60" onkeydown="if (event.keyCode == 13) document.getElementById('input2search').click()"/></td>
		<td>  </td>
		<td><input type="button" id="input2search" value="   Go   " onclick="event.stopPropagation(); event.preventDefault();loadBySubmit2('form1');"/>
		</td>
	</tr>
</table>
</form>
<script type="text/javascript">
	document.getElementById('value2').addEventListener('keydown', getdefAD);
	document.getElementById('value1').focus();

	function getdefAD(val1){	
		if (val1.keyCode == 9) {
			$.post('erp/getDefaultValue?attr=' + $("input#value2").val() +'&id=' + $("input#value1").val(), function(data) {
			if (data) $("input#value3").val(data);
   		});	
		}
	}
</script>

This is the response handler to return a value from the database:

//Get default value for attribute
func getDefaultValue(w http.ResponseWriter, r *http.Request) {
	sA := "attr"
	sP := r.URL.Query().Get(sA)
	sA = "id"
	sI := r.URL.Query().Get(sA)
	if dataAccess.IsStringInSlice(sP, dataAccess.SlcAD_Default) {
		sR := dataAccess.GetERPADDefault(sP, sI)
		fmt.Fprintf(w, "%s", sR)
	} else {
		//sR = dataAccess.GetERPADDefault(sP, sI)
		//fmt.Fprintf(w, "%s", sR)
		// fmt.Println("Not found")
		return
	}
}

Golang - Display Active Directory thumbnailPhoto attribute as HTML IMG

I'm working on an application to list all of the active directory attributes that are set for a user. The application uses "encoding/base64" and "github.com/go-ldap/ldap". To display the tumbnai imag, I used this HTML:

"<div style='width:400px;'><img src='data:image/jpg;base64," + b64.StdEncoding.EncodeToString(attrE.ByteValues[0]) + "'/></div>"

This is the code that iterates through all of the returned AD attributes and creates the HTML text:

var sHTML string
var sA []string
for _, entry := range strcAttrs.Entries {
	for _, attr := range entry.Attributes {
		sA = append(sA, attr.Name)
	}
}
sort.Strings(sA)
for _, attE := range sA {
	for _, entry := range strcAttrs.Entries {
		for _, attrE := range entry.Attributes {
			if attrE.Name == attE {
				if ldapAccess.IncSlcStdAttrs(attrE.Name) {
					sHTML = sHTML + "<span style='text-decoration: bold; background-color: green'>"
				} else {
					sHTML = sHTML + "<span style='text-decoration: bold;'>"
				}
				sHTML = sHTML + "attribute: " + attrE.Name + "</span><br />"
				var attr string = ""
				for _, attr = range attrE.Values {
					if ldapAccess.Include(attrE.Name) {
						if ldapAccess.IsDate64(attrE.Name) {
							sHTML = sHTML + "Date: " + ldapAccess.ConvertStringToDate64(attr)
						} else {
							if ldapAccess.IsDateZ(attrE.Name) {
								sHTML = sHTML + "Date: " + ldapAccess.ConvertStringToDateZ(attr)
							} else {
								sHTML = sHTML + attr
							}
						}
					} else {
						if (attrE.Name) == "thumbnailPhoto" {
							sHTML = sHTML + "<div style='width:400px;'><img src='data:image/jpg;base64," + b64.StdEncoding.EncodeToString(attrE.ByteValues[0]) + "'/></div>"
						} else {

							sHTML = sHTML + "Not Displayed"
						}
					}
					sHTML = sHTML + "<br/>"
				}
			}
		}
	}
	sHTML = sHTML + "<br/>"
}

Golang - Converting from Java and JSP to Golang and a Template

I'm working on converting a Java web application to Golang. Converting the JSP to a template was fairly straight forward.
In Java, I have this function to load the form:

@RequestMapping("/valueinputDateLoadAD")
public ModelAndView valueinputDateLoadAD() {
	Map myModel = new HashMap();
	myModel.put("page", "ldap/dateLoadAD/");
	myModel.put("label1", "Filter:");
	myModel.put("title", "Last update date in the form YYYYMMDD or Res. ID");
	myModel.put("input1_title", "AD Last Update Date or Res. ID");
	return new ModelAndView("input2Form", myModel);
}

The JSP form:

<form action="${page}" name="form1" id="form1" method="POST" target="iframename" onsubmit="event.stopPropagation(); event.preventDefault();">
<table id="input2" style="vertical-align: middle;">
  <tr>
	<td></td><td colspan="3"><b>${title}</b></td>
  </tr>
  <tr>
	<td style="text-align: right;">${label1}</td>
	<td><input style="min-width: 300px;" id="value1" type="text" name="value1" title="${input1_title}" maxlength="60" onkeydown="if (event.keyCode == 13) document.getElementById('input2search').click()"/></td>
	<td>  </td>
	<td><input type="button" id="input2search" value="   Go   " onclick="event.stopPropagation(); event.preventDefault();loadBySubmit2('form1');"/>
	<input type="hidden" name="${_csrf.parameterName}" value="${_csrf.token}"/>
	</td>
  </tr>
</table>
</form>

In Golang, I have this structure and function to load the template:

type StrcP1 struct {
	Page      string
	Label1    string
	Title     string
	In1_Title string
}

func valueinputDateLoadAD(w http.ResponseWriter, req *http.Request) {
	logger.NLog.Info().Msg("valueinputDateLoadAD called")
	strcP := StrcP1{Page: "dateLoadADGroup", Label1: "Filter:", Title: "Last update date in the form YYYYMMDD or Res. ID", In1_Title: "AD Last Update Date or Res. ID"}
	tmpl, err := template.ParseFiles("_resources/html/form/input2Form.html")
	if err != nil {
		logger.NLog.Error().Err(err).Msg("valueinputDateLoadAD: file parse error")
		http.Error(w, "Something went wrong", http.StatusInternalServerError)
		return
	}
	err = tmpl.Execute(w, strcP)
	if err != nil {
		logger.NLog.Error().Err(err).Msg("valueinputDateLoadAD: template execute error")
		http.Error(w, "Something went wrong", http.StatusInternalServerError)
	}
}

And this is my template:

<form action="{{.Page}}" name="form1" id="form1" method="POST" target="iframename" onsubmit="event.stopPropagation(); event.preventDefault();">
<table id="input2" style="vertical-align: middle;">
  <tr>
	<td></td><td colspan="3"><b>{{.Title}}</b></td>
  </tr>
  <tr>
	<td style="text-align: right;">{{.Label1}}</td>
	<td><input style="min-width: 300px;" id="value1" type="text" name="value1" title="{{.In1_Title}}" maxlength="60" onkeydown="if (event.keyCode == 13) document.getElementById('input2search').click()"/></td>
	<td>  </td>
	<td><input type="button" id="input2search" value="   Go   " onclick="event.stopPropagation(); event.preventDefault();loadBySubmit2('form1');"/>
	</td>
  </tr>
</table>
</form>

Golang - Working with active directory date attributes

Some simple functions I wrote to convert AD date attributes to dates as stings.

//Converts a string passed in with the millisecond count to the date in the form yyyy-MM-dd HH:mm:ss as a string
func convertStringToDate64(strDate string) (string, error) {
	uD, err := strconv.ParseInt(strDate, 0, 64)
	if err == nil {
		return time.Unix((uD/(10000000))-11644473600, 0).Format("2006-01-02 15:04:05"), nil
	}
	return "", nil
}

//Converts a string passed in the form yyyyMMddHHmmss.0Z to the date in the form yyyy-MM-dd HH:mm:ss as a string
func convertStringToDateZ(strDate string) (string, error) {
	uD, err := time.Parse("20060102150405", strings.Replace(strDate, ".0Z", "", 1))
	if err == nil {
		return uD.Format("2006-01-02 15:04:05"), nil
	}
	return "", nil
}

Golang - Query Salesforce by using the Salesforce API

I've been learning how to query Salesforce by using the Salesforce API. Here is a simple example that uses the Salesforce query from my last post to query a custom object for values from two related tables. I am using the simpleforce package.

1 - Function to connect to Salesforce:

var (
	SfURL      = "https://mysite.my.salesforce.com/" //Custom or instance URL, for example, 'https://na01.salesforce.com/'
	SfUser     = "me@email.org"           //User name of the Salesforce account                                                                                    //"Username of the Salesforce account."
	SfPassword = "MyPassword"                         //Password of the Salesforce account.
	SfToken    = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"            //Security token, could be omitted if Trusted IP is configured.
)

func CreateClient() *simpleforce.Client {
	client := simpleforce.NewClient(SfURL, simpleforce.DefaultClientID, simpleforce.DefaultAPIVersion)
	if client == nil {
		// handle the error
		fmt.Println("Unable to create client.")
		return nil
	}

	err := client.LoginPassword(SfUser, SfPassword, SfToken)
	if err != nil {
		// handle the error
		fmt.Println("Unable to log in.")
		fmt.Println(err)
		return nil
	} else {
		fmt.Println("SF logged in!")
	}
	// Do some other stuff with the client instance if needed.

	return client
}

2 - Function to query Salesforce. I pass in the query from my last blog post, "Select Project__r.Name, Geographic_Area__r.Country_Code__c from Project_Geographic_Area__c"

//Query Salesforce using the passed in SOQL script
func GetByS(strQ string) {
	client := CreateClient()
	result, err := client.Query(strQ) // Note: for Tooling API, use client.Tooling().Query(q)
	if err != nil {
		// handle the error
		fmt.Println(err)
		return
	}

	for _, record := range result.Records {
		// access the record as SObjects.
		fmt.Println(record)
		//Example 1
		ifvP := record.InterfaceField("Project__r") //get the interface
		strP := ifvP.(map[string]interface{})["Name"] //get the string
		fmt.Println("Project:", strP)
		//Eample 2
		strC := record.InterfaceField("Geographic_Area__r").(map[string]interface{})["Country_Code__c"] //get the string from the interface
		fmt.Println("Country", strC)
	}
}

Salesforce - SOQL query for values from relate objects

Unlike TSQL, SOQL does not use joins on related tables (objects). Instead, the related object is referenced directly in the query by replacing the 'c' at the end of the custom object name with 'r'. In the following example I have three custom objects, Project_Geographic_Area__c, Project__c, and Geographic_Area__c. The Project_Geographic_Area__c has a foreign key field, Project__c, to the Project__c table, and a foreign key field, Geographic_Area__c, to the Geographic_Area__c table. I want to query Salesforce for the Name field from the Project__c table and the Country__Code__c field from the Geographic_Area__c table. So, replacing the ‘c’ with an ‘r’ in the names of our related tables and appending the field names, the resulting query is “Select Project__r.Name, Geographic_Area__r.Country_Code__c from Project_Geographic_Area__c.”

Golang - Add a user to an active directory group

A simple Golang function that I wrote to add a user to a group in AD.

//func ModifyGroup
//	strUDN: User DN
//	strGCN: Group CN
//	strType: flag to Remove or Add the user from the group
func ModifyGroup(strUDN, strGCN, strType string) bool {
	var bR bool = false
	ldapConn = ldap_Bind()     //Get a connection
	strGDN, _ := GetDN(strGCN) //Get the group DN using the group CN
	modify := ldap.NewModifyRequest(strGDN, []ldap.Control{})
	log.Println("ModifyGroup on User: ", strUDN, ", Group: ", strGCN, ", Type: ", strType)
	if strType == "Remove" {
		modify.Delete("member", []string{strUDN})
	}
	if strType == "Add" {
		modify.Add("member", []string{strUDN})
	}
	err := ldapConn.Modify(modify)
	if err != nil {
		log.Println(err)
	} else {
		bR = true
	}
	return bR
}