PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical. I can get you up and running with a 15 minute phone call.

The beauty of PowerShell is that I can write you a cmdlet to do whatever it is you want done. We then import those into your PowerShell ISE and then you can run it with different parameters as often as you want. The output you get back can be opened directly in Excel (if that is how you want it) by PowerShell. I can even write the cmdlet so that it emails the information around or in many other ways.

The reason I like PowerShell is because running the report is easy for those who don’t know PowerShell and yet I can make PowerShell do pretty much anything you need.

I will write your custom cmdlet quickly and for a fixed fee because, not only am I adept at writing PowerShell cmdlets and modules, but I know DMS data models very well.

I would estimate that most extraction cmdlets will be quoted in the $150 to $350 range but if you want a cmdlet to not only extract the data but send email reports or marketing it could (depending on how elaborate) cost upwards of $1,000 – $2,000.

Monthly support for the first 3 cmdlets is free and add $100 a month for each batch of 3 thereafter.

Email me now for a quote at jdonnelly at jdonnelly.com


InfinitiNet Data Access

Infiniti USA requires its dealers to use a certain software in managing their dealerships. It is called InfinitiNet. I haven’t worked with an Infiniti Dealer in a few years so they may have made some changes to the name, description and platform but I don’t think so.

InifinitiNet is an IBM AS400 based system which is good news for us data extraction and mining people. With the right connection string and Data Provider, we can access using Excel (Microsoft Query), Excel (VBA), PowerShell, ADO.Net etc

The file structure is fairly intuitive and is easily explored with IBM’s ISeries Navigator.

I have maps of many of the more commonly accessed tables in the InfinitiNet database if anyone is interested.

Here’s a sample SQL statement pulling Warranty RO information:


As you can see the WAA table holds most of what we need to know about warranty ROs. I wrote this SQL statement to help a dealership get a handle on their warranty claim metrics that Infiniti uses to determine if a dealer is a candidate for a warranty audit.

Here’s a sample list of the tables in the FILEZ database:

ADM: Name & Address Master File
ADS: Email Addresses
ANA: Null
APP: Service Related Info
ASROUT1: Some Type of System Table
ASU: Null
AUD: Null
BAB: Only 2 records maybe accts??
BAR: Has a lot of records BAR Date and BAR UID, BAR Name
BAS: Looks like Customer ID and Some EIN#s
BAT: Looks like a batch ID file
BHD: Null
DCBDY: Holds avail car body codes and their desc
DCCLR: Holds colors
DCINT: Holds interior colors
DCMAK: Holds car makes
DCMDC: Holds car model codes?
DCMDL: Holds car model names
DEA: Holds deals! – a zillion fields
DRA: Holds Chart of Accounts! Or might be a report file showing all the accounts going into a report
FFS: Think this is financial statement – holds lines and pages
FII: F&I Inventory Master Interface Download File
GAA: Accounting Header
GCA: Accounting Detail
GAH: General Ledger History File Summary (Just like Saturn’s GLMSTHPF)
GAM: General Ledger Accounts Master File
IAM: Parts Master File
VAM: Vehicle Master File
VRM: Vehicle Owner File
WAA: RO Header
WJA: RO Jobs
WJM: Job Master File
WPA: RO Parts
WEA: RO Expenses
WAS: RO Split Coverages
WSH: Service History Header
WDH: Service History Detail
WAP: RO Parts Expenses Totals
WAT: RO Totals
ESH: CRM Sales History
GAS: GL Schedule Reports
GAT: Period Date Table
GBA: GL Document Addt’l Info
GBT: Document Type Table
GCT: Table Title File
USM: User ID Master
UST: User ID Access Table
VAT: Vehicle Tables

If anyone has a particular issue they need data on, leave a comment and I will try to help.

Extracting Data from Car Dealership Computer Systems

Recently I received an inquiry as to my availability to consult for a company that needs to extract data from Automobile Dealership Computer Systems. They build websites for dealers and wanted to extract vehicle inventory data. Here is my response;

December 6, 2010

Dear Mr. Jones,

I enjoyed talking with you last week about your data extraction needs. I have been involved in data extraction while creating Business Intelligence solutions for auto dealerships for the past ten years. I currently am contracted with ADP to prepare extractions for their ADP ARG (AS400) clientele who are (were) mainly Saturn customers. I also have worked with a large dealership group here in Nevada building business intelligence Sharepoint solutions which are heavily dependent on data extraction. The following is a summary of my skills as they relate to this project:

Data Extraction Platform Experience:

  • ADP – UNIX, PICK – Both Telnet and DAP (Data Access Program)
  • ADP ARG – IBM AS400, DB2 – (System modified by EDS for Saturn Dealers)
  • Reynolds & Reynolds – Query Builder – Telnet
  • Arkona – IBM AS400, DB2
  • InfinitiNet – IBM AS400, DB2
  • With the advent of Microsoft’s Windows Communication Foundation and ADO.Net there’s really very few systems that can’t be mapped given access.

Data Extraction Methods and Languages Experience:

  • ADO.Net
  • Microsoft SQL Server 2008
  • PowerShell
  • XML
  • AS400 (DB2) SQL

Data Presentation Experience:

  • Sharepoint
  • Excel Services
  • ASP.Net
  • Silverlight

Based on our conversation last week I put together the following proposal.

That’s it. I include it here in case any one might be looking for similar abilities.

Happy New Year!