by Contributed | Dec 15, 2020 | Business, Dynamics 365, Microsoft 365, Technology
This article is contributed. See the original author and article here.
With many retailers extending online holiday sales, consumers are taking advantage of the ease of shopping from home. Online shopping can also enable more impulse buying, which in turn increases the likelihood of returns and cancelations and can open the door to abuse and fraud. Merchants need the right tools and controls to help reduce fraud and maximize their holiday revenue.
Here are some proactive and reactive ways to mitigate fraud and related losses during the high-volume holiday sales season.
Do a fraud check before the order is shipped
While it is common to do a fraud check at the time of checkout for online purchases, another effective method to protect against fraud is to do an assessment before the order is ready to be shipped. This will ensure that any change in the status of orders, during the time elapsed since checkout, gets accounted for. To get an idea of the fraud vulnerabilities that exist in your system, try to identify a user placing several orders repeatedly and then canceling the orders.
Doing this additional fraud check also helps prevent the loss of goods and saves the associated shipping costs. It’s especially important for your retail business, when most of what you sell are physical goods the fraud loss could influence the overall profit efficiency of your business.
To reduce loss, you can create a segment of high purchase value products and apply a distinctive fraud strategy for that segment. In addition, if you have a manual review process in place for reviewing orders, you can control the overall manual review volume by choosing to send orders for products that experience a high-fraud rate category instead of sending all orders for review.
Frequently look for patterns in consumer purchases
While proactive ways to stop fraud are ideal, another way to detect fraud is to reactively look for patterns in bulk data from the most recent orders.
Running anomaly detection on purchase orders placed in the last hour, identifying outliers in the data, and sending those specific outliers for further investigation is a good control that complements the real-time fraud checks.
This is also a good feedback loop for the real-time systems that use artificial intelligence to improve precision and accuracy in identifying fraudulent orders. A reactive fraud strategy is a good way to supplement overall fraud controls that are already in place.
Deriving insights through reporting
In addition to responding proactively and reactively, having robust reporting can provide additional insights and enable analytics. The ability to drill down to a specific product level to review transaction lines, see the specific reason code applied, and having access to the entire journal would enable fraud analysts and investigations teams to make decisions rapidly. Setting up alerts on when specific conditions and thresholds are met for products (or markets) that have been traditionally subject to fraud gives you the ability to respond quickly and adjust fraud strategies rapidly.
How Microsoft can help reduce post-purchase fraud loss
Reactive fraud assessment features can help to identify specific vulnerabilities existing at the store, point of sale, product levels, and so on. The Dynamics 365 Fraud Prevention tool provides a risk-based ranking of staff and point of sale terminals, analyzes underlying transaction data, and helps drive investigations. It provides the ability to drill down to specific transaction lines and review what reason codes were applied by the staff at the point of sale to order. This helps fraud investigators understand which business process such as returns, cancellations, or price overrides is the most susceptible to abuse. It also helps to identify where controls are needed the most (such as region, district, or store). Running your reactive fraud processes frequently will help your overall fraud strategy to be more effective over time.
Next steps
If you are currently using Dynamics 365 Fraud Protection, you can get a fraud check in real-time. The scorecard gives you a real-time view of the performance and support tool that helps to search and investigate all transactions including risk information and history. You can also enable loss prevention, which is based on anomaly detection for protecting against the abuse that happens in relation to returns and cancellations.
The post Ways to mitigate post-purchase fraud during the holidays appeared first on Microsoft Dynamics 365 Blog.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
by Contributed | Dec 14, 2020 | Business, Microsoft, Technology, Tips and Tricks
This article is contributed. See the original author and article here.
Hi there, I am Matt Balzan, and I am a Microsoft Modern Workplace Customer Engineer who specializes in SCCM, APP-V and SSRS/PowerBI reporting.
Today I am going to show you how to write up SQL queries, then use them in SSRS to push out beautiful dashboards, good enough to send to your boss and hopefully win that promotion you’ve been gambling on!
INGREDIENTS
- Microsoft SQL Server Management Studio (download from here)
- A SQL query
- Report Builder
- Your brain
Now that you have checked all the above, let us first go down memory lane and do a recap on SQL.
WHAT EXACTLY IS SQL?
SQL (Structured Query Language) is a standard language created in the early 70s for storing, manipulating and retrieving data in databases.
Does this mean that if I have a database with data stored in it, I can use the SQL [or T-SQL] language to grab the data based on conditions and filters I apply to it? Yep, you sure can!
A database is made up of tables and views and many more items but to keep things simple we are only interested in the tables and views.
Your table/view will contain columns with headers and in the rows is where all the data is stored.
The rows are made up of these columns which contain cells of data. Each cell can be designed to have text, datetime, integers. Some cells can have no values (or NULL values) and some are mandatory to have data in them. These settings are usual setup by the database developer. But thankfully, for reporting we need not worry about this. What we need is data for our reports, and to look for data, we use t-SQL!
But wait! – I have seen these queries on the web, and they all look double-Dutch to me!
No need to worry, I will show you how to read these bad boys with ease!
ANATOMY OF A SQL QUERY
We have all been there before, someone sent you a SQL query snippet or you were browsing for a specific SQL query and you wasted the whole day trying to decipher the darn thing!
Here is one just for simplicity:
select Name0,Operating_System_Name_and0 from v_R_System where Operating_System_Name_and0 like ‘%server%’
My quick trick here is to go to an online SQL formatter, there are many of these sites but I find this one to be perfect for the job: https://sql-format.com/
Simply paste the code in and press the FORMAT button and BOOM!
Wow, what a difference!!! Now that all the syntax has been highlighted and arranged, copy the script, open SSMS, click New Query, paste the script in it and press F5 to execute the query.
This is what happens:
- The SELECT command tells the query to grab data, however in this case it will only display 2 columns (use asterisk * to get all the columns in the view)
- FROM the v_R_System view
- WHERE the column name contains the keyword called server using the LIKE operator (the % tells the filter to bring back zero, one, or multiple characters)
- The rows of data are then returned in the Results pane. In this example only all the server names are returned.
TIPS!
Try to keep all the commands in CAPITALS – this is good practice and helps make the code stand out for easier reading!
ALWAYS use single quotes for anything you are directing SQL to. One common mistake is to use code copied from the browser or emails which have different font types. Paste your code in Notepad and then copy them out after.
Here is another example: Grab all the rows of data and all the columns from the v_Collections view.
SELECT * FROM v_Collections
The asterisk * means give me every man and his dog. (Please be mindful when using this on huge databases as the query could impact SQL performance!)
Sometimes you need data from different views. This query contains a JOIN of two views:
SELECT vc.CollectionName
,vc.MemberCount
FROM v_Collections AS vc
INNER JOIN v_Collections_G AS cg ON cg.CollectionID = vc.CollectionID
WHERE vc.CollectionName LIKE ‘%desktop%‘
ORDER BY vc.CollectionName DESC
OK, so here is the above query breakdown:
- Grab only the data in the column called vc.CollectionName and vc.MemberCount from the v_Collections view
- But first JOIN the view v_Collections_G using the common column CollectionID (this is the relating column that both views have!)
- HOWEVER, only filter the data that has the word ‘desktop‘ in the CollectionName column.
- Finally, order the list of collection names in descending order.
SIDE NOTE: The command AS is used to create an alias of a table, view or column name – this can be anything but generally admin folk use acronyms of the names (example: v_collections will be vc) – Also noteworthy, is that when you JOIN tables or VIEWS you will need to create the aliases, they probably might have the same column names, so the alias also solves the problem of getting all of the joined columns mixed up.
T-SQL reference guide: https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15
OK, SO WHAT MAKES A GOOD REPORT?
It needs the following:
- A script that runs efficiently and does not impact the SQL server performance.
- The script needs to be written so that if you decide to leave the place where you work, others can understand and follow it!
- Finally, it needs to make sense to the audience who is going to view it – Try not to over engineer it – keep it simple, short and sweet.
SCENARIO: My customer wants to have a Task Sequence report which contains their company logo, the task sequence steps and output result for each step that is run, showing the last step run.
In my lab I will use the following scripts. The first one is the main one, it will join the v_r_System view to the vSMS_TaskSequenceExecutionStatus view so that I can grab the task sequence data and the name of the device.
SELECT vrs.Name0
,[PackageID]
,[AdvertisementID]
,vrs.ResourceID
,[ExecutionTime]
,[Step]
,[GroupName]
,[ActionName]
,[LastStatusMsgID]
,[LastStatusMsgName]
,[ExitCode]
,[ActionOutput]
FROM [vSMS_TaskSequenceExecutionStatus] AS vtse
JOIN v_R_System AS vrs ON vrs.ResourceID = vtse.ResourceID
WHERE AdvertisementID = @ADVERTID
ORDER BY ExecutionTime DESC
The second script is for the @ADVERTID parameter – When the report is launched, the user will be prompted to choose a task sequence which has been deployed to a collection. This @ADVERTID parameter gets passed to the first script, which in turn runs the query to grab all the task sequence data rows.
Also highlighted below, the second script concatenates the column name pkg.Name (this is the name of the Task Seq) with the word ‘ to ‘ and also with the column name col.Name (this is the name of the Collection), then it binds it altogether as a new column called AdvertisementName.
So, for example purposes, the output will be: INSTALL SERVER APPS to ALL DESKTOPS AND SERVERS – this is great, as we now know which task sequence is being deployed to what collection!
SELECT DISTINCT
adv.AdvertisementID,
col.Name AS Collection,
pkg.Name AS Name,
pkg.Name + ‘ to ‘ + col.Name AS AdvertismentName
FROM v_Advertisement adv
JOIN (SELECT
PackageID,
Name
FROM v_Package) AS pkg
ON pkg.PackageID = adv.PackageID
JOIN v_TaskExecutionStatus ts
ON adv.AdvertisementID = (SELECT TOP 1 AdvertisementID
FROM v_TaskExecutionStatus
WHERE AdvertisementID = adv.AdvertisementID)
JOIN v_Collection col
ON adv.CollectionID = col.CollectionID
ORDER BY Name
LET US BEGIN BY CREATING THE REPORT
- Open Microsoft Endpoint Configuration Manager Console and click on the Monitoring workspace.
- Right-click on REPORTS then click Create Report.
- Type in the name field – I used TASK SEQUENCE REPORT
- Type in the path field – I created a folder called _MATT (this way it sits right at the top of the folder list)
- Click Next and then Close – now the Report Builder will automatically launch.
- Click Run on the Report Builder popup and wait for the UI to launch. This is what it looks like:
STEP 1 – ADD THE DATA SOURCE
To get the data for our report, we need to make a connection to the server data source.
Right-click on Data Sources / Click Add Data Source… / Click Browse… / Click on Configmr_<yoursitecode> / Scroll down to the GUID starting with {5C63 and double-click it / Click Test Connection / Click OK and OK.
STEP 2 – ADD THE SQL QUERIES TO THEIR DATASETS
Copy your scripts / Right-click on Datasets / click on Add Dataset… / Type in the name of your dataset (no spaces allowed) / Select the radio button ‘Use a dataset embedded in my report’ / Choose your data source that was added in the previous step / Paste your copied script in the Query window and click OK / Click the radio button ‘Use the current Window user’ and click OK.
STEP 3 – ADJUST THE PARAMETER PROPERTIES
Expand the Parameters folder / Right-click the parameter ADVERTID / Select the General tab, under the Prompt: field type DEPLOYMENT – leave all the other settings as they are.
Click on Available Values / Click the radio button ‘Get values from a query’ / for Dataset: choose ADVERTS, for Value field choose AdvertisementID and for Label field choose AdvertisementName / Click OK.
Now when the report first runs, the parameter properties will prompt the user with the DEPLOYMENT label and grab the results of the ADVERTS query – this will appear on the top of the report and look like this (remember the concatenated column?):
OK cool – but we are not done yet. Now for the fun part – adding content!
STEP 4 – ADDING A TITLE / LOGO / TABLE
Click the label to edit your title / change the font to SEGOE UI then move its position to the centre / Adjust some canvas space then remove the [&ExecutionTime] field.
From the ribbon Insert tab / click Image, find a space on your canvas then drag-click an area / Click Import…, choose ALL files (*.*) image types then find and add your logo / Click OK.
Next click on Table, choose Table Wizard… / Select the TASKSEQ dataset and click Next / Hold down shift key and select all the fields except PackageID, AdvertisementID & ResourceID.
Drag the highlighted fields to the Values box and click Next / Click Next to skip the layout options / Now choose the Generic style and click Finish.
Drag the table under the logo.
STEP 5 – SPIT POLISHING YOUR REPORT
* A Placeholder is a field where you can apply an expression for labels or text, you wish to show in your report.
In my example, I would like to show the Deployment name which is next to the Task Sequence title:
In the title text box, right-click at the end of the text TASK SEQUENCE: / Click Create Placeholder… / under Value: click on the fx button / click on Datasets / Click on ADVERTS and choose First(AdvertisementName).
Finally, I wanted the value in UPPERCASE and bold. To do this I changed the text value to: =UCASE(First(Fields!AdvertisementName.Value, “ADVERTS”)) , click on OK.
I then selected the <<Expr>> and changed the font to BOLD.
Do not forget to save your report into your folder of choice!
Once you have finished all the above settings and tweaks, you should be good to run the report. Click the Run button from the top left corner in the Home ribbon.
If you followed all the above step by step, you should have now a report fit for your business – this is the output after a Deployment has been selected from the combo list:
- To test the Action Output column, I created a task sequence where I intentionally left some errors in the Run Command Line steps, just to show errors and draw out some detail.
- To avoid massive row height sizes, I set the cell for this column to font size 8.
- To give it that Azure report style look and feel, I only set the second row in the table with the top border being set. You can change this to your own specification.
Please feel free to download my report RDL file as a reference guide (Attachment on the bottom of this page)
LESSONS LEARNED FROM THE FIELD
- Best advice I give to my customers is to start by creating a storyboard. Draw it out on a sheet of blank or grid paper which gives you an idea where to begin.
- What data do you need to monitor or report on?
- How long do these scripts take to run? Test them in SQL SERVER MANAGEMENT STUDIO and note the time in the Results pane.
- Use the free online SQL formatting tools to create proper readable SQL queries for the rest of the world to understand!
- Who will have access to these reports? Ensure proper RBAC is in place.
- What is the target audience? You need to keep in mind some people will not understand the technology of the data.
COMING NEXT TIME…
My next blog will go into a deep dive in report design. I will show you how to manipulate content based on values using expressions, conditional formatting, design tips, best practices and much more….Thanks for reading, keep smiling and stay safe!
DISCLAIMER
The sample files are not supported under any Microsoft standard support program or service. The sample files are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample files and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the files be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
Matt Balzan | Modern Workplace Customer Engineer | SCCM, Application Virtualisation, SSRS / PowerBI Reporting
Recent Comments