I am created a script that queries a SQL database we use for our ticketing system. The script connects to an ODBC and queries the selected groups tickets and is supposed to search from date ranges (I am trying just a few days but it could be from a few weeks to months). I have it all complete except the fact when I query tickets it pulls every ticket instead of the ranges. The database does date/time in epoch format and since I am fairly new to SQL and PS I am not sure how to fit that into a SQL select function.
Can anyone please offer some suggestions?
Here is my code:
#When attempting queries - Most are HPD:Help Desk - That will not work here. It needs to be #HPD_Help_Desk ## Login Information - Using CAC Credentials doesn't work yet - have to use Username and Password $id = Get-Credential "domain\$("username")" $userId = $id.UserName.split("\").tolower() $password = $id.GetNetworkCredential().Password write-host "Running Query" ##### Start the database connection and set up environment $DbString="DSN=ODBC Driver;UID=$userID;PWD=$password" add-type -path 'PathtoDll' $DBConnection=New-Object System.Data.Odbc.OdbcConnection ($SelectStatement) $DBCommand=New-Object System.Data.Odbc.OdbcCommand $DBConnection.ConnectionString=$DbString $DBConnection.Open() $DBCommand.Connection=$DBConnection ##What You Want to Query $DBCommand.CommandText="SELECT Incident_Number, Assignee, Reported_Date FROM HPD_Help_Desk WHERE Assigned_Group ='1' OR Assigned_Group ='2' OR Assigned_Group ='3' OR Assigned_Group ='4' AND Reported_Date = 'to date'" ## Execute Query $DBResult=$DBCommand.ExecuteReader() $UserTable=New-Object system.data.datatable $UserTable.load($DBResult) $UserTable | Format-Table
Any help or suggestions would be appreciated!
Which epoch? There are several in computing and depending on which one your database uses it may be easy or hard(ish) to calculate.
If it uses seconds since the UNIX epoch (January 1st 1970) then it's straightforward as you can use Get-Date and the -UFormat parameter to return the number of seconds elapsed since epoch for any given date:
#Number of seconds since UNIX epoch right now Get-Date -UFormat %s #Number of seconds since UNIX epoch on Jan 2nd 1970 Get-Date '02/01/1970' -UFormat %s
You can assign the result to a variable and include it in your query:
$date = Get-Date '23/03/2017' -UFormat %s ##What You Want to Query $DBCommand.CommandText="SELECT Incident_Number, Assignee, Reported_Date FROM HPD_Help_Desk WHERE Assigned_Group ='1' OR Assigned_Group ='2' OR Assigned_Group ='3' OR Assigned_Group ='4' AND Reported_Date = '$date'"
Since I am exporting it to a CSV I changed the way the $UserTable is used on the end to:
Where $DateRange is I have it to where the user is able to enter however many days they want to go back. I am going to create another version of what you provided too.
You must be logged in to reply to this topic.