PowerShell/Sqlite Query Help

PowerShell Help
1

Hello,

I’m trying to create a password manager with the ability to send email notifications when accounts are expiring. I’m creating a PowerShell CRUD GUI with a Sqlite database backend. Also there will be a separate PowerShell service that runs in the background and checks the database and sends the notifications.

However I am having trouble querying the Sqlite database for accounts/passwords that are nearing expiration. I would like to have it send a notification when an account/password is within 15, 10, 5, 4, 3, 2, 1, 0 days of expiration.

I tried some suggestions from ChatGPT but they did not work. I’m a noob when it comes to databases/Sqlite.

I have tried these two queries with no success…

$Query = @"
    SELECT Name, 'Formatted Acct Expiration'
    FROM Accounts
    WHERE (julianday('Formatted Acct Expiration') - julianday('now', 'localtime')) IN (20, 15, 10, 5, 4, 3, 2, 1, 0)
"@

$query = @"
    SELECT Name, 'Formatted Acct Expiration'
    FROM Accounts
    WHERE strftime('%s', 'Formatted Acct Expiration') - strftime('%s', 'now', 'localtime') IN (15*24*60*60, 10*24*60*60, 5*24*60*60, 4*24*60*60, 3*24*60*60, 2*24*60*60, 1*24*60*60, 0)
"@

Note: The backend will take the date the user enters into the GUI and format it into a format that Sqlite will recognize and insert it into Formatted Acct Expiration.

Here is the data that is in the database and is what I am trying to get returned from the above query.
I am looking for Formatted Acct Expiration which in this example this account expires in 2 days so it should be returned in the query, but it is not and I’m not sure what I’m doing wrong.

image
image1427×92 6.15 KB

2

Hey Shane!

Correct me if I’m wrong, but this seems to be less PowerShell and more of a SQL question, despite PS being the ‘avenue’ which the queries are ran. This forum is really more for PS questions.

Any particular reason you’re using the julianday/strftime functions? Is the password expiration already in a date format in the database? See: Date Functions in SQL Server and MySQL (w3schools.com). Typically date is stored in SQL in a slightly different format than I see on your screen. Can you convert it for easy comparison directly? SQL compare dates - An easy guide to comparing dates in SQL Server (devart.com). I’ve not used SQLLite before but… i assume normal SQL applies. Also for getting the current date: How to Get the Current Date (Without Time) in T-SQL | LearnSQL.com. Additionally, you can use PowerShell to handle the current date instead of SQL and simply use that as a comparison, assuming you store the expiration date in the DB and can use comparisons properly.

Beyond the topic, there are a ton of password modules out there, and some of them even integrate with the powershell secrets module. I generally suggest not reinventing the wheel unless absolutely necessary. Just something to consider, I know in some circumstances it is beyond control.

3

Thanks for your reply. your right I probably should try a sql forum instead… However I changed strategies and instead of trying to filter the data with Sqlite queries, I used PowerShell to do it and got the result I wanted. As for the formatted date issue I’m no longer using that data anymore in the new method.

As for reinventing the wheel this project is side hobby and is to help me learn more about PowerShell and Sqlite databases and keep my PowerShell scripting skills sharp. you know what they say, don’t use it you lose it. However this project is also to help resolve an issue at my work. I’m a sub contractor on a help desk and am a developer of a PowerShell support tool for our group. One issue we have is our domain accounts expire every 6months and were lucky if our leads get notified of the expiration and extend our accounts. None of us have any means or access to implement any large scale changes in our domain. I don’t even have the means to keep a PC on 24/7 to setup any kind of server service. Hence I’m trying to make a local tool that can send us direct notifications for our account expirations but I decided to make it more challenging for myself and try and make a full on password manager just for the fun of it…

4

PowerShell FTW! Yeah I probably would have done the same myself - using PS to do it. Then it becomes a general SQL query, and you just use PowerShell to further manipulate the data, and it ends up being easier… usually anyway.

No worries on the using your own solution, I just felt like I had to mention it, just in case you didn’t know there were some other options out there that may potentially work for you, but definitely get the contraints you have at your org.