On week 3 of the script update month I am releasing a major code update to the Create-DMARC report script. In this release of the DMARC Report tool I am adding a lot of new major functionalities, some minor functionality’s and a few bug fixes. The biggest improvement is the support for the most wanted PowerBI integration so next to the offline HTML report you are now able to create rich visual reports.
You can find the script in the TechNet gallery or under the script section of this site.
The full explanation of this function can be found on the release page here:
Release notes V2.0:
New features:
- GEO Source IP lookup to include the following new reports:
- Per country spoof report for each domain analyzed
- Per Country source IP Spoof report for each domain analyzed
- Country information on the source IP main report
- PowerBI support
- PowerBI incremental data upload support
- Extended GEO information for PowerBI ( Country, City, Region, longitude, latitude )
- PowerBI report template with added calculated columns.
- Per day timestamps of received dmarc reports
- Reporting option to report only on Failed DMARC items.
- DMARC Alignment data reports
Fixes /Update:
- Fixed Reverse lookup IP table sometimes truncated the hostname to only 1 character.
- Fixed export of Reverse lookup IP table did not incremental export causing a attempt to create duplicate keys during the import. This was a minor issue as the Hash table already did not allow it.
HTML changes / enhancements:
- Added Reports total of DMARC passed VS DMARC failed.
- Added tables for DMARC alignment of SPF.
- Added tables for DMARC alignment of DKIM
New report in action
The new full GEO enhanced HTML report run against my domains can be viewed here: http://www.tech-savvy.nl/wp-content/dmarc/report/
A small preview:
A full Sample of a rich PowerBI report can be found here: PowerBI Report
A small preview:
As you can not share a PowerBI report publicly that utilizes calculated columns from the API a PowerPoint showing these functionality`s is included in the download of the tool.
Parameter changes:
To support the new functionality`s the script take 3 new parameters to control if the new functionality should be enabled or not. These new parameters are “-geolookupenabled”, “-PowerBIuploadenabled” and “-DMARCfailedonly”. As their names already reveal they correspond to the new functions. The parameters can be used separate or together.
Let me know what you think of the new changes or if you have any other thoughts.
Have fun with PowerBI
Regard
Martijn
The instructions that come with this for powerbi configuration are missing steps (especially for powerbi newbies).
1. Will powerbi free suffice, or do you need a license that includes powerbi
2. When setting up datasets, the screenshot shows, 1,2,3 as clicks but when you clieck create, you’re prompted- do you use a microsoft appsource (org/services), import or connect to data(files/databases). Then what?
Would be good to see a quick walkthrough youtube on this script setup/powerbi integration.
PowerBI instructions:
In Workspace, click Datasets, then + to add. Select Streaming dataset and choose API.
Name the report whatever you’d like.
Populate these values:
“Realprocessdate” :”2018-04-20T19:46:37.759Z”,
“processdate” :”AAAAA555555″,
“orgname” :”AAAAA555555″,
“dmarcdomain” :”AAAAA555555″,
“sourceip” :”AAAAA555555″,
“sourcedomain” :”AAAAA555555″,
“sourceipcount” :98.6,
“dmarcdisposition” :”AAAAA555555″,
“dmarcspf” :”AAAAA555555″,
“dmarcdkim” :”AAAAA555555″,
“headerfrom” :”AAAAA555555″,
“dkimdomain” :”AAAAA555555″,
“spfscope” :”AAAAA555555″,
“spfresult” :”AAAAA555555″,
“latitude” :98.6,
“longitude” :98.6,
“country_name” :”AAAAA555555″,
“region_name” :”AAAAA555555″,
“city” :”AAAAA555555″
Hit the selector to turn on historical data analysis and then click create
Copy the push URL and paste it into line ~374. $endpoint = “your push url”
Once you run the script with the powerbi switch enabled, go back to powerbi and see last refresh of your data set.
Note – you still have to design your reports. they don’t magically appear this pretty.
While processing the script, things worked fine until I had to press enter to continue it seemed. Soon as I pressed enter the script continued to step 8 and immediately printed on the screen Directory: c:\dmarc\reports.. mode lastwrite length name
it then completed.
My other feedback to automate this, would be great to include a mail module and send mail to a reporting address so this can be task scheduled to run daily.
on my wish list – add date to the report, not just year/month. Create a new folder for every report run. reports currently run during same month over write themselves.
On Geolookup.. around 1750 in the code – the geocounter++, I think we die in the geolookup loop, once you exceed 9950 lookups, the counter isn’t cleared in the while loop, so you exit the while loop after 6 sleeps and then you re-enter it again directly because geocounter remains above 9950.
do you have this code on git?
I’ve added around line ~1754 a new variable $geocountertotal and a reset in the loop to $geocounter to see if this code behaves nicer and to also give more insight to how many records have been looked up.
$geocountertotal++
if ($geocounter -gt 9950)
{
Write-Warning -Message “Total Records queried thus far is: $geocountertotal”
#Reset $geocounter to get out of here after 60 mins
$geocounter = 0
Yeah the code is on Git to
https://github.com/martijnvangeffen/DMARC-report
Nice, thank for spotting this one, I fixed this one and its ready for the next release 2.2+
Might be one of the nicest bits of code I have seen in a very long time. So very well documented as well, excepting how to setup PowerBI.
Is it possible for you to be a bit more specific on setting up the PowerBI?