Automatically Retrieve Dell Warranty Info and Import it into PDQ Inventory Custom Fields
One of my coworkers was tasked with pulling reports of old computers that need to be replaced which sent me down a rabbit hole of trying to automatically determine that based on data in PDQ Inventory. I couldn't find a great solution so I decided to automate importing some data into PDQ to make this work.
Here is the link to the GitHub repo: https://github.com/jlukenichols/KUDellWarrantyChecker
Basically, it does this:
- The script looks at a CSV file containing every Dell computer in your PDQ Inventory. It must have the Computer Name and Computer Serial Number fields, but it can contain additional fields as well if you want. I have this on an auto-report which saves the CSV file to a UNC path on a network share every night at 6:00 PM. If you do an auto report make sure to NOT include the date in the file path and make sure to overwrite old files. I have the filters on the report set to this:
- The script iterates through each service tag in the CSV file, performs a Dell API lookup to grab its warranty data, and saves that data into an output CSV file. Right now the data grabbed is "Ship Date" and the device's entitlement (warranty) end dates. Because a computer can have several entitlements it just grabs the maximum of all the dates returned. Some of my devices had a 1-year initial entitlement and a 3-year extended entitlement so I just wanted to know when all of its warranties would be expired.
- The script imports the Dell warranty data from the newly created CSV file and imports it into PDQ Inventory as custom fields. It assumes that you have already created the custom fields with date/time data types. If you want to mirror my setup then create the custom fields like this:
This code is available for anyone to use, although you will need to apply for your own Dell API key on Dell TechDirect: https://techdirect.dell.com/Portal/APIs.aspx
If you run into issues getting your API key you should email APIs_TechDirect@dell.com. I know my request to gain access to the API portal was denied several times with an utterly useless error code being emailed to me, and the issue turned out to be that I had no Dell service tags or Dell customer number associated with my account. I ended up reaching out to them via email and just giving them some of our Dell service tags with which they were able to look up my Dell customer number and associate it to my account. After that I got into the API program without issues.
If anyone has any questions about the script or needs help setting it up for their environment let me know.
Comments
David,
Thanks for sharing. I think both options have pros and cons and certainly you should do whatever works best for you in your environment. It was quite the headache working with Dell to get the API access sorted out so I understand wanting to avoid that.
I would note that using Dell Command Monitor requires you to install and manage an additional piece of software on every one of your endpoints and if you wanted to wrap that data into PDQ Inventory you would have to modify your default scan profile or add an additional one with a WMI or PowerShell scanner. I imagine that many PDQ customers enjoy the agentless functionality of the software and might take issue with the additional overhead of having to manage an additional piece of software on those machines, so in some ways I think my solution is more in keeping with the spirit of the software. With all that being said your solution is certainly viable and I'm sure it will be a better solution for some people depending on the nature of their environment.
Marc,
What is the value of your $LogRotationIntervalInDays in your CustomSettings.ps1 script?
Is set to 30, which I think is how it is straight from the GitHub repo
Could you take screenshots of your scheduled task configuration and post them here? If you don't want to post them publicly you can email me at J.Luke.Nichols@gmail.com instead
Have you ever gotten the error "Unable to save. Please verify the CSV is valid"?
Thanks,
Ryan
Ryan,
No, I haven't gotten that error. Where are you seeing the error? In the PowerShell console? In the log file?
Its showing in the PowerShell Console.
Is there a "DellWarrantyData.csv" file in the same directory as the script after running it? That is where the data is written to by default. The file is overwritten on the next run of the script but it is not deleted at the end of the script run. If that file does not exist then your script may be failing to generate it due to a permissions issue or something like that.
Yep, i'm running it locally, and manually filled in all the Variables and tried to just import the DellWarrantyData.csv file it's self and Its giving the error. Does it need to be a specific format?
I've removed the API Keys in the referenced Image.
Ok. Can you provide a snippet of the CSV file? If you are worried about posting sensitive info on the forum you can censor it.
Also can you provide a screenshot of your custom fields in PDQ Inventory?
Using the PDQ Import Wizard in the GUI, I was able to determine the issue was caused by a value already being in one of the custom fields. This error only happened on subsequent imports, not the initial one. I added -AllowOverwrite to the end of the command in the script and now it's working fine.
Ok thanks for figuring that out. I will modify that in the version on Github.
Thanks for the prompt responses.
Have a Great Day!
I have been assigned this exact task at my organization. I have run the KUDellWarrantyChecker.ps1 with no errors. The output DellWarrantyData.CSV does not include any data except for the Computer,Name,Purchase,Date,Warranty,End Date in row 1. I have a PDQ report setup, but it is not an auto-report. Am I missing something? Do I need to add custom fields to PDQ Inventory? Any assistance would be greatly appreciated.
Fareed,
Yes, you will need to add two custom fields. I have modified my original post to include a screenshot indicating what the fields should be called and what data type they should be.
I also added this info to the readme on the github repo.
Thanks. I posted my updates on GitHub.
I've found another way to retrieve this information, which IMO, is a little easier. Dell Command Monitor enables you to use WMI queries to find out all kinds of things about a Dell computer. We're using it for checking certain BIOS settings. There's also a way to check warranty status. Plus lots of other info about a Dell system.
With DCM installed on the computer, we're using this:
SELECT * FROM DCIM_AssetWarrantyInformation WHERE InstanceID='Root/MainSystemChassis/COOObject/COOWarranty:0'
Thank You, now to wait for my key.
Can you explain the order of operations in greater detail? I did the following;
1) Retrieved Dell API Key
2) Copied files to PDQ server
3) Created report to collect (Computer Name & Serial), this dumps to a network drive location where the downloaded files reside.
4) Added custom Fields within PDQ inventory (Warranty End Date + Purchase Date)
Essentially it looks like you run a report initially to grab the computer names and serials, then the script (KUDellWarrantyChecker) runs and collects the data via the API and dumps it back into inventory. My confusion is how to automate this to run itself once a week.
I have an auto-report setup to generate the CSV that collects the computer/serial and names the file "ExampleInput".csv & manually running the KUDellWarrantyChecker works.
Thanks for your input, this is extremely useful!!
Rich,
Those steps are all correct. The part you're missing is to set up a scheduled task to run the script automatically. Here is how I set up mine:
(I used my PDQ background service user for this, but you could theoretically use a different account. The account will need to be a member of the console users in PDQ Inventory as well as needing read permissions to the location where you save the auto report. You may also need administrator rights on the computer that you are running the script from but I am not sure since I have not tested that.)
Action: Start a program
Program/script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Add arguments: -command "C:\Scripts\KUDellWarrantyChecker\KUDellWarrantyChecker.ps1"
(Obviously you should replace the content within the quotes with the full path to the script on your system)
Start in: C:\Scripts\KUDellWarrantyChecker
(Again, change this path to the path to your script. This field is also probably completely unnecessary and can be left blank.)
(Most of these don't matter much but here is what I am using for reference)
Allow task to be run on demand
Run task as soon as possible after a scheduled start is missed
If the task fails, restart every 10 minutes
Attempt to restart up to 3 times
Stop the task if it runs longer than 1 hour
(this may have to be adjusted to a longer time if your import takes a while)
If the running task does not end when requested, force it to stop
Good luck, let me know if you have any more questions!
Perfect, thank you very much for the detailed response. I have configured this to run accordingly and its working flawlessly. This is going to save us a insane amount of time come refresh project time as we have a large amount of dated devices in the environment.
Many thanks!
Luke,
Would this script also work for monitors connected to the computer if instead of the computer serial, you put the monitor serial?
No problem Rich, glad it's working for you.
Nathano,
In theory you could modify the script to track monitor info as well but it would be more complicated than just replacing the computer serial in the report. The tricky thing about monitors is that they are not a 1:1 relationship with computers so you wouldn't really want to store it as a custom variable attached to the computer. In PDQ it looks like monitor info is stored in the HardwareDevices table but I don't see a field for the Dell service tag there and a quick spot check looks like nothing in the InstanceId field matches a Dell service tag either.
You could probably create a custom variable for the monitor serial number and do manual data entry or populate it another way but I am not sure how that work. Then you have the issue of how to tie a specific monitor serial number to a specific computer. There is also the aforementioned issue of computers:monitors not being a 1:1 relationship because you could have several monitors connected to a single computer.
Basically what I'm saying is it is theoretically possible but it would be quite involved and it's not really in the scope of what the script was designed to do. Feel free to fork the project and try to modify it yourself if you'd like but that is not a feature that I am likely to add to the project in the near future.
Hello,
I ran KUDellWarrantyChecker.ps1 with no errors. The DellWarrantyData.CSV output does not contain any data except Computer Name, Purchase Date, Warranty End Date in line 1.
I set up the custom fields in the PDQ Inventory.
Dominik,
Make sure your input file in CSV format. Your symptoms as described match another person's reported issues from the past and their issue ended up being their input report was on XLSX format instead of CSV format. The input file MUST be CSV:
https://github.com/jlukenichols/KUDellWarrantyChecker/issues/1
the import and export files are both in CSV format. In the export CSV I see the separators but no values. I get the information via the Get-DellWarranty function, so my APIs are valid.
Ok, that all looks fine. Can you post your latest log file? Feel free to redact any serial numbers if you want but please leave the file paths intact.
Edit: If you're not comfortable sharing any of that info publicly you can email it to me at J.Luke.Nichols@gmail.com
Edit2: I also just pushed a change to the code that might help your issue but I'm not sure. I suspect there is an issue with localization where your system is expecting a different list separator than the comma "," character. I modified the code to look up the system's regional list separator character by default but still allow the user to override it with a character if they want. You should pull the latest version then update your CustomSettings.ps1 file based on the changes to DefaultSettings.ps1.
Hi Luke,
I think i've got everything setup correctly in terms of PDQ inventory but when running the KuDellWarantyChecker.ps1 I am getting an error at the invoke-webrequest stage:
Does the secret need to be between () as it appears in the tech direct portal?
Marc,
No, the secret does not literally need to be in parentheses. Can you make sure you don't have an erroneous space or other character in your secret or api key?