Month: June 2024
Cannot get “$_.hiddenfromaddresslistsenabled -eq $true” to work with other parameters.
Hi Cannot get this command to work. Tried different variations and commands.
Get-Mailbox | Where-Object {$_.IsLicensed -eq $true -and $_.BlockCredential -eq $false -and $_.hiddenfromaddresslistsenabled -eq $true}
The below works fine:
Get-Mailbox | Where-Object {$_.IsLicensed -eq $true -and $_.BlockCredential -eq $false}
Can anyone help?
Hi Cannot get this command to work. Tried different variations and commands. Get-Mailbox | Where-Object {$_.IsLicensed -eq $true -and $_.BlockCredential -eq $false -and $_.hiddenfromaddresslistsenabled -eq $true} The below works fine:Get-Mailbox | Where-Object {$_.IsLicensed -eq $true -and $_.BlockCredential -eq $false} Can anyone help? Read More
Playlist Videos in Sharepoint list shows “Request Access” but i can be played upon clicking it
Hi Team,
We created a playlist and have shared the playlist videos to specific users. However, the playlist shows that they have to request for access. If the select the video, it can be played.
Why the “Request Access” is displayed though access is available. It misleads the user.
Hi Team, We created a playlist and have shared the playlist videos to specific users. However, the playlist shows that they have to request for access. If the select the video, it can be played. Why the “Request Access” is displayed though access is available. It misleads the user. Read More
Understanding SharePoint Online Storage
Understanding SharePoint Online storage used to be easy. Then applications like Loop arrived. Other influences like retention and archive can affect storage too. It’s a complicated situation before you throw OneDrive for Business into the mix and consider that Microsoft has removed unlimited OneDrive storage while an increasing number of apps store files in OneDrive. It’s a complicated situation.
https://office365itpros.com/2024/06/10/sharepoint-online-storage-2/
Understanding SharePoint Online storage used to be easy. Then applications like Loop arrived. Other influences like retention and archive can affect storage too. It’s a complicated situation before you throw OneDrive for Business into the mix and consider that Microsoft has removed unlimited OneDrive storage while an increasing number of apps store files in OneDrive. It’s a complicated situation.
https://office365itpros.com/2024/06/10/sharepoint-online-storage-2/ Read More
The End for Office 365 Connectors Comes Into Sight
Office 365 Connectors bring data from external sources into Microsoft 365 apps like Teams and Outlook. Workflows and Power Automate are replacing Connectors for Microsoft 365 Groups (Outlook groups) and SharePoint Online. Connectors are still available in Teams but for how long? No one knows, but it does seem like Microsoft is rationalizing no-code automation around Power Automate.
https://office365itpros.com/2024/06/11/office-365-connectors-end/
Office 365 Connectors bring data from external sources into Microsoft 365 apps like Teams and Outlook. Workflows and Power Automate are replacing Connectors for Microsoft 365 Groups (Outlook groups) and SharePoint Online. Connectors are still available in Teams but for how long? No one knows, but it does seem like Microsoft is rationalizing no-code automation around Power Automate.
https://office365itpros.com/2024/06/11/office-365-connectors-end/ Read More
Outlook Web based.
Good day!
I am hoping someone can assits me.
My dowloaded Outlook (Left) picks up all my messages but when I log into outlook on the microsoft website (right) it does not pick up any messages?
Good day! I am hoping someone can assits me.My dowloaded Outlook (Left) picks up all my messages but when I log into outlook on the microsoft website (right) it does not pick up any messages? Read More
Save the Date for Global DevOps Experience on June 15!
The Global DevOps Bootcamp, an annual event series that has captured the attention of the DevOps community worldwide by hosting local events to share the latest DevOps and relevant technologies as well as case studies, is being rebranded as the “Global DevOps Experience” (GDEX). This exciting event will take place on Saturday, June 15, 2024.
This event series will feature insights on DevOps, Platform Engineering, and Developer Experience (DevEx), leveraging technologies such as Azure, AI, and GitHub. DevOps communities from around the globe will come together to share their knowledge and experience. Microsoft MVPs and Regional Directors are planning various local events, eagerly anticipating interactions with participants as organizers and speakers.
Visit the event page to learn more about the event story and details of local community events in different countries. Don’t miss out on the opportunity to join an event that interests you.
Microsoft Tech Community – Latest Blogs –Read More
seek demo px4/PX4HITLSimulationFixedWingPlantSimulinkExample
when i use command openExample(‘px4/PX4HITLSimulationFixedWingPlantSimulinkExample’),matlab shows "PX4HITLSimulationFixedWingPlantSimulinkExample" cannot find. So does somebody know how to find this demo? Thanks.when i use command openExample(‘px4/PX4HITLSimulationFixedWingPlantSimulinkExample’),matlab shows "PX4HITLSimulationFixedWingPlantSimulinkExample" cannot find. So does somebody know how to find this demo? Thanks. when i use command openExample(‘px4/PX4HITLSimulationFixedWingPlantSimulinkExample’),matlab shows "PX4HITLSimulationFixedWingPlantSimulinkExample" cannot find. So does somebody know how to find this demo? Thanks. simulationfixedwingplant, simulink, examles MATLAB Answers — New Questions
How do I clean a test file which size stayed big enough?
I’ve got a test file that was linked to larga data files and now those files were reduced in size. Due to the linkage, the test file size was 1.28gb; the fact is that now that the data files were reduced, the test file carries on with the old size and I cannot find a way to "clean it"I’ve got a test file that was linked to larga data files and now those files were reduced in size. Due to the linkage, the test file size was 1.28gb; the fact is that now that the data files were reduced, the test file carries on with the old size and I cannot find a way to "clean it" I’ve got a test file that was linked to larga data files and now those files were reduced in size. Due to the linkage, the test file size was 1.28gb; the fact is that now that the data files were reduced, the test file carries on with the old size and I cannot find a way to "clean it" test file, testing, size MATLAB Answers — New Questions
Need help with FFT of sensor data
Hello.
For my thesis, I am required to analyse one of the five links of 5 DOF robotic arm. The said link has also been designed in solidworks and was imported in Ansys software to find first 4 natural frequencies/mode shapes through Finite Element Analysis (FEA). The natural frequencies are as follows :
0.05934
188.22
476.19
558.74
Ansys also provides the Displacement Time data which can be exported for further analysis. I am currently trying to obtain a magnitude – frequency graph through FFT to find the natural frequencies of the link through exported data.
My understanding is, if I can recreate the natural frequencies given by Ansys through my MATLAB program, I will be able to find acceptable natural frequencies of any link by aquiring acceleration time data through sensors. But in order to do that, I need a program that finds close to accurate natural frequencies through FFT.
I have made/replicated a program, which is attached, by researching online. However, I am obtaining peaks at incorrect frequencies. Furthermore, my plot does not cover all the frequencies on the X-Axis as well.
Summary : I want to make a program that can give me a Magnitude Frequency graph through FFT for a link of a 5 DOF robotic arm. I want to recreate the results comparable or similar to precalculated and correct values of natural frequencies through my program. However, the results through my program are incorrect.
I am sure I must be doing something wrong but I cannot seem to figure it out.
Would appreciate any and all help.
Cheers.
X=readmatrix(‘accl.xlsx’) %importing data
fs=20; % sampling frequency
L=138; % length of signal
nfft=2^nextpow2(L); % next power of 2 from length of freq signal
f=(fs/2)*linspace(0,1,nfft/2+1);
timefft = fft(X(:,2),nfft)/L;% frf of displacement
fre=2*abs(timefft(1:nfft/2+1));% frf of displacement
figure(1),plot(f,fre); grid on % fig A
% Natural frequencies without any scale adjustment
frf=fft(X(:,2));
figure(2),plot((abs(frf))); % fig BHello.
For my thesis, I am required to analyse one of the five links of 5 DOF robotic arm. The said link has also been designed in solidworks and was imported in Ansys software to find first 4 natural frequencies/mode shapes through Finite Element Analysis (FEA). The natural frequencies are as follows :
0.05934
188.22
476.19
558.74
Ansys also provides the Displacement Time data which can be exported for further analysis. I am currently trying to obtain a magnitude – frequency graph through FFT to find the natural frequencies of the link through exported data.
My understanding is, if I can recreate the natural frequencies given by Ansys through my MATLAB program, I will be able to find acceptable natural frequencies of any link by aquiring acceleration time data through sensors. But in order to do that, I need a program that finds close to accurate natural frequencies through FFT.
I have made/replicated a program, which is attached, by researching online. However, I am obtaining peaks at incorrect frequencies. Furthermore, my plot does not cover all the frequencies on the X-Axis as well.
Summary : I want to make a program that can give me a Magnitude Frequency graph through FFT for a link of a 5 DOF robotic arm. I want to recreate the results comparable or similar to precalculated and correct values of natural frequencies through my program. However, the results through my program are incorrect.
I am sure I must be doing something wrong but I cannot seem to figure it out.
Would appreciate any and all help.
Cheers.
X=readmatrix(‘accl.xlsx’) %importing data
fs=20; % sampling frequency
L=138; % length of signal
nfft=2^nextpow2(L); % next power of 2 from length of freq signal
f=(fs/2)*linspace(0,1,nfft/2+1);
timefft = fft(X(:,2),nfft)/L;% frf of displacement
fre=2*abs(timefft(1:nfft/2+1));% frf of displacement
figure(1),plot(f,fre); grid on % fig A
% Natural frequencies without any scale adjustment
frf=fft(X(:,2));
figure(2),plot((abs(frf))); % fig B Hello.
For my thesis, I am required to analyse one of the five links of 5 DOF robotic arm. The said link has also been designed in solidworks and was imported in Ansys software to find first 4 natural frequencies/mode shapes through Finite Element Analysis (FEA). The natural frequencies are as follows :
0.05934
188.22
476.19
558.74
Ansys also provides the Displacement Time data which can be exported for further analysis. I am currently trying to obtain a magnitude – frequency graph through FFT to find the natural frequencies of the link through exported data.
My understanding is, if I can recreate the natural frequencies given by Ansys through my MATLAB program, I will be able to find acceptable natural frequencies of any link by aquiring acceleration time data through sensors. But in order to do that, I need a program that finds close to accurate natural frequencies through FFT.
I have made/replicated a program, which is attached, by researching online. However, I am obtaining peaks at incorrect frequencies. Furthermore, my plot does not cover all the frequencies on the X-Axis as well.
Summary : I want to make a program that can give me a Magnitude Frequency graph through FFT for a link of a 5 DOF robotic arm. I want to recreate the results comparable or similar to precalculated and correct values of natural frequencies through my program. However, the results through my program are incorrect.
I am sure I must be doing something wrong but I cannot seem to figure it out.
Would appreciate any and all help.
Cheers.
X=readmatrix(‘accl.xlsx’) %importing data
fs=20; % sampling frequency
L=138; % length of signal
nfft=2^nextpow2(L); % next power of 2 from length of freq signal
f=(fs/2)*linspace(0,1,nfft/2+1);
timefft = fft(X(:,2),nfft)/L;% frf of displacement
fre=2*abs(timefft(1:nfft/2+1));% frf of displacement
figure(1),plot(f,fre); grid on % fig A
% Natural frequencies without any scale adjustment
frf=fft(X(:,2));
figure(2),plot((abs(frf))); % fig B natural frequencies, fft, fourier transform MATLAB Answers — New Questions
How to get the planner task Progress using power automate?
I am trying to filter the planner tasks based on the Progress column:-
i tried those 3 actions, but none of them will return or allow us to filter based on the Progress column:
1) List all Tasks
2) Get Task Details
3) Get a Task
any advice?
Thanks
I am trying to filter the planner tasks based on the Progress column:- i tried those 3 actions, but none of them will return or allow us to filter based on the Progress column: 1) List all Tasks2) Get Task Details3) Get a Task any advice?Thanks Read More
Can’t find password to email address on my pc
I’m having trouble accessing my email account on my Windows 11 computer. I’ve forgotten my password and can’t seem to recover it. I’ve tried using the password recovery feature on the email provider’s website, but it’s not working for me. I’ve checked my PC’s settings and can’t find any information on where my email password might be stored. Now, I’m stuck and unable to access my email account.
I’m looking for advice on how to recover my email password or reset it if possible.
I’m having trouble accessing my email account on my Windows 11 computer. I’ve forgotten my password and can’t seem to recover it. I’ve tried using the password recovery feature on the email provider’s website, but it’s not working for me. I’ve checked my PC’s settings and can’t find any information on where my email password might be stored. Now, I’m stuck and unable to access my email account. I’m looking for advice on how to recover my email password or reset it if possible. Read More
Recent changes could not be saved. Refresh the page to undo the changes and continue working
Hello,
Over the past few days I have experienced challenges with the Project for the web part.
It consists of changes not saved in the project folder. Instead, this error message appears.
“Recent changes could not be saved. Refresh the page to undo the changes and continue working. Correlation ID: 6883f5fd-35f0-4bef-8423-8e5bf2dfd36f”
What does this mean and what must be done so that I can use Project for the web again?
Best regards,
Hello, Over the past few days I have experienced challenges with the Project for the web part.It consists of changes not saved in the project folder. Instead, this error message appears.”Recent changes could not be saved. Refresh the page to undo the changes and continue working. Correlation ID: 6883f5fd-35f0-4bef-8423-8e5bf2dfd36f” What does this mean and what must be done so that I can use Project for the web again? Best regards, Read More
How pricing works on Azure Stack HCI?
Hi Experts,
We are planning to use Azure Stack HCI, one of our questions is how the pricing works for SQL on a VM and SQL using PaaS on HCI?
In our understanding SQL on a VM requires: SQL license, OS license, compute and storage cost.
Paas SQL on HCI requires compute and storage cost.
is this correct and which one is the most cost effective and if can you expound why?
Thank you in advance.
Hi Experts, We are planning to use Azure Stack HCI, one of our questions is how the pricing works for SQL on a VM and SQL using PaaS on HCI?In our understanding SQL on a VM requires: SQL license, OS license, compute and storage cost.Paas SQL on HCI requires compute and storage cost. is this correct and which one is the most cost effective and if can you expound why? Thank you in advance. Read More
matching excel row data to filename based on matching data left of underscore
Hoping somebody can help with what I believe can be done either using Power Query or VBA.
I have an Excel file that includes thousands of entries – each entry has an object number and SOME objects will have an associated file on the hard drive (or SharePoint / remote server). I would like the script to be able to review the data from the file name left of the first underscore to match with the object number and record the filename in the correct field “demo file path” in the attached sample file. E.g. object number 123 is related to file 123_has-extra_from_3733-AREA_RULE_Progress_Report_Feb._231955.pdf
I am OK if the system needs to place a message in the related entry that states “no match found”, it is a bonus but not required.
Hopefully with the sample data that includes a second tab of the file that are in the existing folder and the above details somebody can help out.
If the solution cannot read directly from a specific folder / site and needs a worksheet with all the file names, I can easily work with that solution.
Thank you.
Sample File Hoping somebody can help with what I believe can be done either using Power Query or VBA. I have an Excel file that includes thousands of entries – each entry has an object number and SOME objects will have an associated file on the hard drive (or SharePoint / remote server). I would like the script to be able to review the data from the file name left of the first underscore to match with the object number and record the filename in the correct field “demo file path” in the attached sample file. E.g. object number 123 is related to file 123_has-extra_from_3733-AREA_RULE_Progress_Report_Feb._231955.pdf I am OK if the system needs to place a message in the related entry that states “no match found”, it is a bonus but not required. Hopefully with the sample data that includes a second tab of the file that are in the existing folder and the above details somebody can help out. If the solution cannot read directly from a specific folder / site and needs a worksheet with all the file names, I can easily work with that solution. Thank you. Read More
How can I mute and unmute yourself from Windows taskbar in Microsoft Teams?
Specifically, I’m trying to find a way to mute and unmute myself directly from the Windows taskbar. I’ve noticed that sometimes my audio is turned on and I’m accidentally broadcasting my background noise to the team. I’d like to be able to quickly mute and unmute myself without having to navigate through the Microsoft Teams app or desktop interface.
Does anyone know how to access the audio settings from the Windows taskbar, or if there’s a shortcut or hotkey that allows me to quickly mute and unmute myself in Microsoft Teams?
Specifically, I’m trying to find a way to mute and unmute myself directly from the Windows taskbar. I’ve noticed that sometimes my audio is turned on and I’m accidentally broadcasting my background noise to the team. I’d like to be able to quickly mute and unmute myself without having to navigate through the Microsoft Teams app or desktop interface.Does anyone know how to access the audio settings from the Windows taskbar, or if there’s a shortcut or hotkey that allows me to quickly mute and unmute myself in Microsoft Teams? Read More
Generating a list of Dates using nested array inside SEQUENCE function?
Hello,
I am trying to generate a list of dates with different intervals using an array inside of a single SEQUENCE function. How do I do this? Cell references are in bracket below and the results I’m looking for.
Ex:
Start Date End Date Day Interval
(A1) 2024-02-15 (B1) 2024-03-07 (C1) 7
(A2) 2024-02-23 (B2) 2024-03-22 (C2) 14
Looking to generate
2024-02-15
2024-02-22
2024-02-29
2024-03-07
2024-02-23
2024-03-08
2024-03-22
Basically it generates a list of values going through each start date in the array (A1:A2) and their respective interval (C1:C2). I am trying to use one instance of the SEQUENCE function to do this nesting arrays inside instead of using a SEQUENCE function for each start date. Any ideas? Thank you in advance.
Hello, I am trying to generate a list of dates with different intervals using an array inside of a single SEQUENCE function. How do I do this? Cell references are in bracket below and the results I’m looking for. Ex:Start Date End Date Day Interval(A1) 2024-02-15 (B1) 2024-03-07 (C1) 7(A2) 2024-02-23 (B2) 2024-03-22 (C2) 14 Looking to generate 2024-02-152024-02-222024-02-292024-03-072024-02-232024-03-082024-03-22 Basically it generates a list of values going through each start date in the array (A1:A2) and their respective interval (C1:C2). I am trying to use one instance of the SEQUENCE function to do this nesting arrays inside instead of using a SEQUENCE function for each start date. Any ideas? Thank you in advance. Read More
Retrieve deleted videos from Stream(classic)
Hello,
Our team had a few meeting recordings which were saved on Microsoft Stream (Classic). Recently when we try to access those videos, it appears not accessible. We realize that the Stream (Classic) has been retired since 15 April. Is there any way to retrieve the videos? Please advise.
Regards,
Manoj
Hello, Our team had a few meeting recordings which were saved on Microsoft Stream (Classic). Recently when we try to access those videos, it appears not accessible. We realize that the Stream (Classic) has been retired since 15 April. Is there any way to retrieve the videos? Please advise. Regards,Manoj Read More
Office Script to add image
Hi! I am using the following code to add image into excel sheet automatically. However, it only works for JPG file and not for JPEG and PNG file. May I modify the following code and let it works for 3 types of image files? Thank you very much.
function main(workbook: ExcelScript.Workbook, base64ImageString: string,imageName: string) {
// get table row count
let sheet1 = workbook.getWorksheet(‘In progress’);
let table1 = workbook.getTable(‘Inprogress’);
let rowCount = table1.getRowCount();
let n1:number = rowCount + 1;
let range1:string = ”;
let imageAddress:string = ”;
let nameAddress:string = ”;
imageAddress = ‘J’ + n1.toString();
nameAddress = ‘N’ + n1.toString();
range1 = n1 + ‘:’ + n1
sheet1.getRange(range1).getFormat().setRowHeight(230);
let range = sheet1.getRange(imageAddress);
let image = sheet1.addImage(base64ImageString);
image.setName(imageName);
image.setTop(range.getTop());
image.setLeft(range.getLeft());
image.setWidth(300);
image.setHeight(225);
sheet1.getRange(nameAddress).setValue(imageName)
}
Hi! I am using the following code to add image into excel sheet automatically. However, it only works for JPG file and not for JPEG and PNG file. May I modify the following code and let it works for 3 types of image files? Thank you very much. function main(workbook: ExcelScript.Workbook, base64ImageString: string,imageName: string) {
// get table row count
let sheet1 = workbook.getWorksheet(‘In progress’);
let table1 = workbook.getTable(‘Inprogress’);
let rowCount = table1.getRowCount();
let n1:number = rowCount + 1;
let range1:string = ”;
let imageAddress:string = ”;
let nameAddress:string = ”;
imageAddress = ‘J’ + n1.toString();
nameAddress = ‘N’ + n1.toString();
range1 = n1 + ‘:’ + n1
sheet1.getRange(range1).getFormat().setRowHeight(230);
let range = sheet1.getRange(imageAddress);
let image = sheet1.addImage(base64ImageString);
image.setName(imageName);
image.setTop(range.getTop());
image.setLeft(range.getLeft());
image.setWidth(300);
image.setHeight(225);
sheet1.getRange(nameAddress).setValue(imageName)
} Read More
RAG on structured data with PostgreSQL
RAG (Retrieval Augmented Generation) is one of the most promising uses for large language models. Instead of asking an LLM a question and hoping the answer lies somewhere in its weights, we instead first query a knowledge base for anything relevant to the question, and then feed both those results and the original question to the LLM.
We have many RAG solutions out there for asking questions on unstructured documents, like PDFs and Word Documents. Our most popular Azure solution for this scenario includes a data ingestion process to extract the text from the documents, chunk them up into appropriate sizes, and store them in an Azure AI Search index. When your RAG is on unstructured documents, you’ll always need a data ingestion step to store them in an LLM-compatible format.
But what if you just want users to ask questions about structured data, like a table in a database? Imagine customers that want to ask questions about the products in a store’s inventory, and each product is a row in the table. We can use the RAG approach there, too, and in some ways, it’s a simpler process.
To get you started with this flavor of RAG, we’ve created a new RAG-on-PostgreSQL solution that includes a FastAPI backend, React frontend, and infrastructure-as-code for deploying it all to Azure Container Apps with Azure PostgreSQL Flexible Server. Here it is with the sample seed data:
We use the user’s question to query a single PostgreSQL table and send the matching rows to the LLM. We display the answer plus information about any of the referenced products from the answer. Now let’s break down how that solution works.
Data preparation
When we eventually query the database table with the user’s query, we ideally want to perform a hybrid search: both a full text search and a vector search of any columns that might match the user’s intent. In order to perform a vector search, we also need a column that stores a vector embedding of the target columns.
This is what the sample table looks like, described using SQLAlchemy 2.0 model classes. The final embedding column is a Vector type, from the pgvector extension for PostgreSQl:
class Item(Base):
__tablename__ = “items”
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
type: Mapped[str] = mapped_column()
brand: Mapped[str] = mapped_column()
name: Mapped[str] = mapped_column()
description: Mapped[str] = mapped_column()
price: Mapped[float] = mapped_column()
embedding: Mapped[Vector] = mapped_column(Vector(1536))
The embedding column has 1536 dimensions to match OpenAI’s text-embedding-ada-002 model, but you could configure it to match the dimensions of different embedding models instead. The most important thing is to know exactly which model you used for generating embeddings, so then we can later search with that same model.
To compute the value of the embedding column, we concatenate the text columns from the table row, send them to the OpenAI embedding model, and store the result:
items = session.scalars(select(Item)).all()
for item in items:
item_for_embedding = f”Name: {self.name} Description: {self.description} Type: {self.type}”
item.embedding = openai_client.embeddings.create(
model=EMBED_DEPLOYMENT,
input=item_for_embedding
).data[0].embedding
session.commit()
We only need to run that once, if our data is static. However, if any of the included columns change, we should re-run that for the changed rows. Another approach is to use the Azure AI extension for Azure PostgreSQL Flexible Server. I didn’t use it in my solution since I also wanted it to run with a local PostgreSQL server, but it should work great if you’re always using the Azure-hosted PostgreSQL Flexible Server.
Hybrid search in PostgreSQL
Now our database table has both text columns and a vector column, so we should be able to perform a hybrid search: using the pgvector distance operator on the embedding column, using the built-in full-text search functions on the text columns, and merging them using the Reciprocal-Rank Fusion algorithm.
We use this SQL query for hybrid search, inspired by an example from the pgvector-python repository:
vector_query = f”””
SELECT id, RANK () OVER (ORDER BY embedding <=> :embedding) AS rank
FROM items
ORDER BY embedding <=> :embedding
LIMIT 20
“””
fulltext_query = f”””
SELECT id, RANK () OVER (ORDER BY ts_rank_cd(to_tsvector(‘english’, description), query) DESC)
FROM items, plainto_tsquery(‘english’, :query) query
WHERE to_tsvector(‘english’, description) @@ query
ORDER BY ts_rank_cd(to_tsvector(‘english’, description), query) DESC
LIMIT 20
“””
hybrid_query = f”””
WITH vector_search AS (
{vector_query}
),
fulltext_search AS (
{fulltext_query}
)
SELECT
COALESCE(vector_search.id, fulltext_search.id) AS id,
COALESCE(1.0 / (:k + vector_search.rank), 0.0) +
COALESCE(1.0 / (:k + fulltext_search.rank), 0.0) AS score
FROM vector_search
FULL OUTER JOIN fulltext_search ON vector_search.id = fulltext_search.id
ORDER BY score DESC
LIMIT 20
“””
results = session.execute(sql,
{“embedding”: to_db(query_vector), “query”: query_text, “k”: 60},
).fetchall()
That hybrid search is missing the final step that we always recommend for Azure AI Search: semantic ranker, a re-ranking model that sorts the results according to the original user queries. It should be possible to add a re-ranking model, as shown in another pgvector-python example, but such an addition requires loadtesting and possibly an architectural change, since re-ranking models are CPU-intensive. Ideally, the re-ranking model would be deployed on dedicated infrastructure optimized for model running, not on the same server as our app backend.
We get fairly good results from that hybrid search query, however! It easily finds rows that both match the exact keywords in a query and semantically similar phrases, as demonstrated by these user questions:
Function calling for SQL filtering
The next step is to handle user queries like, “climbing gear cheaper than $100.” Our hybrid search query can definitely find “climbing gear”, but it’s not designed to find products whose price is lower than some amount. The hybrid search isn’t querying the price column at all, and isn’t appropriate for a numeric comparison query anyway. Ideally, we would do both a hybrid search and add a filter clause, like WHERE price < 100.
Fortunately, we can use an LLM to suggest filter clauses based on user queries, and the OpenAI GPT models are very good at it. We add a query-rewriting phase to our RAG flow which uses OpenAI function calling to come up with the optimal search query and column filters.
In order to use OpenAI function calling, we need to describe the function and its parameters. Here’s what that looks like for a search query and single column’s filter clause:
{
“type”: “function”,
“function”: {
“name”: “search_database”,
“description”: “Search PostgreSQL database for relevant products based on user query”,
“parameters”: {
“type”: “object”,
“properties”: {
“search_query”: {
“type”: “string”,
“description”: “Query string to use for full text search, e.g. ‘red shoes'”
},
“price_filter”: {
“type”: “object”,
“description”: “Filter search results based on price of the product”,
“properties”: {
“comparison_operator”: {
“type”: “string”,
“description”: “Operator to compare the column value, either ‘>’, ‘<‘, ‘>=’, ‘<=’, ‘='”
},
“value”: {
“type”: “number”,
“description”: “Value to compare against, e.g. 30”
}
}
}
}
}
}
}
We can easily add additional parameters for other column filters, or we could even have a generic column filter parameter and have OpenAI suggest the column based on the table schema. For my solution, I am intentionally constraining the LLM to only suggest a subset of possible filters, to minimize risk of SQL injection or poor SQL performance. There are many libraries out there that do full text-to-SQL, and that’s another approach you could try out, if you’re comfortable with the security of those approaches.
When we get back the results from the function call, we use it to build a filter clause, and append that to our original hybrid search query. We want to do the filtering before the vector and full text search, to narrow down the search space to only what could possibly match. Here’s what the new vector search looks like, with the additional filter clause:
vector_query = f”””
SELECT id, RANK () OVER (ORDER BY embedding <=> :embedding) AS rank
FROM items
{filter_clause}
ORDER BY embedding <=> :embedding
LIMIT 20
“””
With the query rewriting and filter building in place, our RAG app can now answer questions that depend on filters:
RAG on unstructured vs structured data
Trying to decide what RAG approach to use, or which of our solutions to use for a prototype? If your target data is largely unstructured documents, then you should try out our Azure AI Search RAG starter solution which will take care of the complex data ingestion phase for you. However, if your target data is an existing database table, and you want to RAG over a single table (or a small number of tables), the try out the PostgreSQL RAG starter solution and modify it to work with your table schema. If your target data is a database with a multitude of tables with different schemas, then you probably want to research full text-to-SQL solutions. Also check out the llamaindex and langchain libraries, as they often have functionality and samples for common RAG scenarios.
Microsoft Tech Community – Latest Blogs –Read More
Boosting Code Security with GHAS Code Scanning in Azure DevOps & GitHub
Code scanning, a pipeline-based tool available in GitHub Advanced Security, is designed to detect code vulnerabilities and bugs within the source code of ADO (Azure DevOps) repositories. Utilizing CodeQL as a static analysis tool, it performs query analysis and variant analysis. When vulnerabilities are found, it generates security alerts.
CodeQL
CodeQL is a powerful static analysis tool used for showing vulnerabilities and bugs in source code. It enables developers to write custom queries that analyze codebases, searching for specific patterns and potential security issues. By converting code into a database format, CodeQL allows for sophisticated, database-like queries to detect flaws.
CodeQL in Action
1. Preparing the Code
Create a CodeQL Database: Extract and structure the code into a database for analysis.
2. Running CodeQL Queries
Execute Queries: Run predefined or custom queries against the database to find potential issues.
3. Interpreting the Query Results
Review Findings: Analyze the results to find, prioritize, and address vulnerabilities and code quality issues.
Reference: – About the CodeQL CLI – GitHub Docs
Sample Code Scanning Azure DevOps Pipeline
Once the GitHub Advanced security is configured for the ADO Repo we can then create and run a dedicated Code scanning pipeline to detect vulnerability & generate query results & alerts.
Below is a generic sample Code scanning pipeline
Prerequisites: –
GitHub Token (GitHub token): Required Pipeline Variable for authenticated operations with GitHub.
CodeQL Results File Path (codeql_results_file): Predefined in the pipeline YAML variable to specify where the analysis results are stored.
SARIF SAST Scans Tab extension: Need to install it from Azure DevOps Marketplace to view query results
# Author: Debjyoti
# This pipeline uses default CodeQL queries for code scanning
trigger: none
pool:
vmImage: ‘windows-latest’
variables:
codeql_results_file: ‘$(Build.ArtifactStagingDirectory)/results.sarif’
steps:
– task: AdvancedSecurity-Codeql-Init@1
displayName: ‘Initialize CodeQL’
inputs:
languages: ‘python’
loglevel: ‘2’
env:
GITHUB_TOKEN: $(githubtoken)
– task: AdvancedSecurity-Codeql-Autobuild@1
displayName: ‘AutoBuild’
– task: AdvancedSecurity-Codeql-Analyze@1
displayName: ‘Perform CodeQL Analysis’
inputs:
outputFile: ‘$(codeql_results_file)’
– task: PublishBuildArtifacts@1
displayName: ‘Publish CodeQL Results’
inputs:
pathToPublish: ‘$(codeql_results_file)’
artifactName: ‘CodeQLResults’
For further insights and detailed guides, please refer to the following articles:
Default setup of Code Scanning in GitHub Repository
Requirements for Using Default Setup
GitHub Actions: Must be enabled.
Recommendations
Enable default setup if there is any chance of including at least one CodeQL-supported language in the future.
Default setup will not run or use GitHub Actions minutes if no CodeQL-supported languages are present.
If CodeQL-supported languages are added, default setup will automatically begin scanning and using minutes.
Customizing Default Setup
Start with default setup.
Evaluate code scanning performance.
Customize if needed to better meet security needs.
Configuring Default Setup for a Repository
Automatic Analysis: All CodeQL-supported languages will be analyzed.
Successful Analysis: Languages analyzed successfully will be retained.
Unsuccessful Analysis: Languages not analyzed successfully will be deselected.
Failure Handling: If all analyses fail, default setup stays enabled but inactive until a supported language is added, or setup is manually reconfigured.
Steps to Enable Default Setup
Navigate to Repository: Go to the main page of the repository.
Access Settings:
Click on “Settings” under the repository name.
If “Settings” is not visible, select the dropdown menu and click “Settings”.
Security Settings:
In the “Security” section of the sidebar, click “Code security and analysis”.
4. Setup Code Scanning: In the “Code scanning” section, select “Set up” and click “Default”.
Review Configuration:
A dialog will summarize the automatically created code scanning configuration.
Optionally, select a query suite in the “Query suites” section.
Extended query suite runs additional, lower severity and precision queries.
Enable CodeQL: Review settings and click “Enable CodeQL” to trigger a workflow.
View Configuration: After enablement, view the configuration by selecting the relevant choice.
CodeQL Analysis Run: Once CodeQL is set up, it will run on the repository to check for vulnerabilities in the supported language code. You can view more information by clicking on the “View last scan” option.
View Security Alerts: It will run its default built-in queries on the repository code for the supported language (in this case, Python) and will generate alerts for any detected vulnerabilities.
Reference Link for more insights –
https://docs.github.com/en/code-security/code-scanning/enabling-code-scanning/configuring-default-setup-for-code-scanning
https://docs.github.com/en/code-security/code-scanning/managing-your-code-scanning-configuration/python-built-in-queries
Benefits of Running Code QL for Developers
Responsibilities and Burdens
Initial Setup and Learning Curve: Requires time to set up and learn how to use effectively.
Maintenance of Queries: Custom queries may need updates as the codebase evolves.
False Positives: May generate false positives that need to be reviewed and addressed.
Integration Effort: Integrating Code QL into existing CI/CD pipelines can require significant effort.
Microsoft Tech Community – Latest Blogs –Read More