1

I am trying to delete the contents of an Exchange 2013 mailbox before today's date. It must be specific to the second I call the Get-Date cmdlet.

This code runs without error:

Search-Mailbox myID -SearchQuery Received:<Get-Date -DeleteContent -Force

When executing this, I see the search progress bar in PowerShell, except no data is found. I have triple checked the mailbox I am working with and it has data older than 4/24/14/ hh:mm:ss.

I have tried countless variations, an example being:

Search-Mailbox whism_j -SearchQuery "Received:<$((Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ"))" -DeleteContent -Force`

This command does not execute, as I get an The property keyword isn't supported error.

Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
Johnrad
  • 2,637
  • 18
  • 58
  • 98

4 Answers4

2

Why your code is not working:

Search-Mailbox myID -SearchQuery Received:<get-date -deleteContent -force

This doesn't work because get-date is parsed as a literal string; you're not interpolating the results of the Get-Date cmdlet. -SearchQuery is a string parameter, and PowerShell implicitly interprets arguments to string parameters as double-quoted strings. To interpolate the results of Get-Date, use -SearchQuery Received:<$(Get-Date). 

Search-Mailbox whism_j -SearchQuery "Received:<$((get-date).toString("yyyy-MM-ddTHH:mm:ssZ"))" -deleteContent -force

This doesn't work because the date format isn't valid. You might infer that it should be based on the AQS documentation, but in fact the date in Search-Mailbox queries needs to be in a format that conforms to the Exchange server's regional settings.

How to do it:

You might be able to get away with simply interpolating the results of Get-Date:

Search-Mailbox id_attribute -SearchQuery "Received:<$(Get-Date)" -DeleteContent -Force

However, this will only work if the format used when a DateTime object is interpolated into a string, which is MM/dd/yyyy HH:mm:ss, matches the regional format. To ensure that you're getting the right format, use Get-Culture to determine the right format string, and supply that to Get-Date's -Format parameter:

If you want to use today's date, you could use Get-Date and then convert it to a string as RickH suggested, but that's not necessary, because AQS support named relative dates, including today:

$format = (Get-Culture).DateTimeFormat.ShortDatePattern + ' ' + (Get-Culture).DateTimeFormat.LongTimePattern
Search-Mailbox myID -SearchQuery "Received:<$(Get-Date -Format $format)" -DeleteContent -Force

Note that, as implied by what I said above, the external double quotes aren't strictly necessary if the argument doesn't contain spaces that aren't within parentheses, but I think it's good practice to include them.

For completeness and the benefit of future searchers, it's worth mentioning that AQS also accepts named relative dates such as today, this week, or last month, so this should also work:

Search-Mailbox id_attribute -SearchQuery "Received:<today" -DeleteContent -Force

I didn't provide that as part of the answer because in a previous (deleted) version of the question the OP specifically asked how to search from messages before an exact time, and today is just the current date without a time.

Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
  • I appreciate your answer! `Search-Mailbox id_attribute -SearchQuery "Received:<$(Get-Date)" -DeleteContent -Force` this is giving me the same ol' keyword error, and not running. `Search-Mailbox whism_j -SearchQuery "Received:<$(Get-Date -Format ((Get-culture).DateTimeFormat.ShortDatePattern))" -DeleteContent -Force` this runs successfully, but finds no data. It seems as when I finally get something to run successfully, no data is produced. – Johnrad Apr 24 '14 at 19:20
  • I have come to the conclusion as of now that it does not like appending the Time on with the date. `Search-Mailbox whism_j -SearchQuery "Received:<$(get-date -Format d)" -DeleteContent -Force` this runs (while still not returning anything). Searching for a `get-date -format G` does not. It throws the keyword error. – Johnrad Apr 24 '14 at 19:33
  • 1. Try entering `"Received:<$(Get-Date -Format ((Get-culture).DateTimeFormat.ShortDatePattern))"` at the prompt by itself and tell me what results you get. 2. What happens if you try "Sent" instead of "Received"? 3. What do you mean when you say "no data is produced" or "not returning anything"? Do you mean you get a **ResultItemsCount** of 0, or no results at all? The latter shouldn't happen--you should get a result report even if nothing is found. – Adi Inbar Apr 24 '14 at 19:45
  • This may sound like a silly question, but it doesn't hurt to ask...if it's the former, are you 1000% sure there still *is* data in the mailbox? If you're using **-DeleteContent**, you'd only get a positive item count the first time, and 0 thereafter unless new items are added. Have you been checking the mailbox to make sure there's still data from before today in it? Maybe you didn't notice that the command succeed once, and that's why it's not finding anything more to delete. – Adi Inbar Apr 24 '14 at 19:46
  • Again, your help is awesome, thank you. Not a silly question, I have 7 items in this test mailbox that were put in there earlier this afternoon. When I simply run `-deleteContent` I get a positive `ResultItemCount`. I have re-populated the inbox of this mailbox. When trying to add a `SearchQuery` my `ResultItemsCount` and `ResultItemsSize` is 0. This is what I mean by "no data". If it makes it any easier, when I run a `get-culture` the `LCID` is `1033`,` Name` is `en-US` and `DisplayName` is `English (United States)` – Johnrad Apr 24 '14 at 19:53
  • Also, to answer your previous question. When I run `"Received:<$(Get-Date -Format ((Get-culture).DateTimeFormat.ShortDatePattern))` I once again get 0 `resultItemsCount`. This may be a silly question, but what is the difference between `sent` and `received`. – Johnrad Apr 24 '14 at 19:54
  • Unfortunately I do not have access to an Exchange server at this time. Is it possible to search for anything that gives a result, and then reference the format of the date/time that is sent to you by the server? Then search for that, and if you feed it what it's giving you and it still says 0 results then I wonder if there are other issues somewhere. – TheMadTechnician Apr 24 '14 at 20:00
  • I can do a search based on Subject and delete content. When logging this, I see my date format is as such `2/5/2014 9:48:35 PM` – Johnrad Apr 24 '14 at 20:24
  • @Johnrad If the culture is en-US, then you *should* be able to just interpolate `$(Get-Date)` without specifying a format (that was to make the search work universally; though it shouldn't *hurt* to include the format even if the culture is en-US). Note also that I changed it to **LongDatePattern**, since your example includes seconds. – Adi Inbar Apr 24 '14 at 20:25
  • "Sent" is the time the message was accepted by the sender's mail server from the sender's client. "Received" is the time the message was delivered to the recipient's mailbox. That suggestion was for diagnostic purposes. "Received" absolutely should be a valid property, but since you were getting the `property keyword isn't supported` error, I thought it might be worth seeing what happens if you search on a different datetime property. – Adi Inbar Apr 24 '14 at 20:26
  • A new interesting development... If I remove the `<` sign from my query, I find all of the items from todays date, and it deletes them. However it doesn't follow the format to the HH:mm:ss... This is better than before. But not what I am wanting – Johnrad Apr 24 '14 at 20:34
  • @Johnrad You say that you've repopulated the inbox. Are you doing that by sending new items, or are you moving older items into it? If you send an item, even of you're forwarding an older email, the Received date will be today. If you *do* have older items, what happens if you use this query: `Received:>1/1/1601`? That *should* delete everything. – Adi Inbar Apr 24 '14 at 20:43
  • I am physically sending the mailbox new mail when I re-populate it. `Received:>1/1/1601` did not delete anything. I tried `Received:4/25/2014` and it deleted all of the items I sent to it today... – Johnrad Apr 24 '14 at 20:57
  • Is the date on your system correct? It's not 4/25/2014 yet unless you're in east Asia or further east, and it sounds like you're saying that deleted items sent earlier today... – Adi Inbar Apr 24 '14 at 21:48
  • THank you for your help. I am going to make this as a correct answer so you will get credit for the help you have given me. I think I am going to have to cut my losses though and look for a different alternative. This just will not accept anything with the less than symbol for me. According to everywhere online, it should. Is it something to do with e2k13? – Johnrad Apr 25 '14 at 19:23
  • @Johnrad My Exchange servers are 2K10, so it's possible that there's some bug in 2K13 that I can't reproduce. Have you installed the recently released SP1? Also, I googled around to see if there's some known issue with searches in Ex2K13 and found [this](http://social.technet.microsoft.com/Forums/exchange/en-US/4f00b8a7-04e4-4ad0-be3b-7f699582a5b3/searchmailbox-searchquery-does-not-work?forum=exchangesvrgenerallegacy). The links in the first answer might be helpful in determining/fixing the problem, though what's described there sounds like it might be more trouble than it's worth. – Adi Inbar Apr 25 '14 at 19:44
  • BTW...since you say you're looking for alternative solutions, if your goal is to always delete messages older than one day (as opposed to doing this only at specific times), have you considered retention policies? By default they only run once per day, but you *can* change the frequency (though it probably wouldn't be good for server performance to run them every minute...). There's also Online (server-side) Archiving, though they make you buy special licenses for that and I don't have experience with it, so I can't tell you if it can do what you need. – Adi Inbar Apr 25 '14 at 20:17
2

This was a top result when I was trying to do date range searches on Office 365 ( Exchange Online ) Adding the SearchQuery to find email older than a date, I was getting ResultItemsCount 0.

So here is what I eventually found through trial and error:

Recommend using the ISO dash-not-slash format: yyyy-mm-dd

VALID searches:

-SearchQuery Sent:2016-01-01..2018-01-01
-SearchQuery Sent<2016-01-01
-SearchQuery Sent<=2016-01-01

-SearchQuery "Received>=2016-01-01 AND Received<=2018-01-01"
-SearchQuery Received<2016-01-01
-SearchQuery Received<=2016-01-01

BROKEN searches:

-SearchQuery Received:2016-01-01..2018-01-01  #This style range only valid for Sent.
-SearchQuery Sent:<2016-01-01                 #Putting a : colon before < > less or greater than returns nothing.
-SearchQuery Recieved<2016-01-01              #i before e except after c, no errors for mispelt properties!
WhoIsRich
  • 4,053
  • 1
  • 33
  • 19
  • 1
    Weird, but queries with ":<" in it do return some data occasionally. Hit this post while searching for why my queries don't work, and has a query like `Received:>"2018-01-01"` return me some messages. Not sure for the reason, still this means MS did a great job obscuring the KQL parser details. Upvoted for valid examples. – Vesper Jun 01 '18 at 09:33
1

I think the issue you are seeing is that your date string has whitespace in it, so it is interpreting it as another property to filter by. So it's reading:

"Received:<$((get-date).tostring("yyyy-MM-ddTHH:mm:ssZ"))"

which expands out to something like:

"Received:<2014-04-24 10:51:23-08"

It's seeing two properties here I believe, Received: and 10: because your date isn't enclosed in double quotes. This should work for you:

Search-Mailbox whism_j -SearchQuery Received:<"$((get-date -f "yyyy-MM-ddTHH:mm:ss").tostring())" -deleteContent -force

Edit: Sorry, my mistake, for some reason I thought T was a tab, but it just translates as the letter "T". Also, just the item to search for is enclosed in quotes, not the entire thing. Updated answer. Exchange should see the query as:

Received:<"2014-04-24T10:51:23"

The time zone offset is optional, as all times are supposed to be in UTC. From Microsoft's MSDN page:

All date/time values must be specified according to the UTC (Coordinated Universal Time), also known as GMT (Greenwich Mean Time) time zone. The UTC time zone identifier (a trailing "Z" character) is optional.

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
  • With that I am getting another `the property keyword isn't supported`. I totally understand what you are saying, but not sure why I see this error so often. I can't even put in a specific date like "4/24/2014" – Johnrad Apr 24 '14 at 18:02
  • It for some reason is not liking when I add the date and time...`Received:<"2014-04-24T10:51:23"` gives me the keyword error. When I remove the hh:mm:ss and just use `Received:<"2014-04-24"`, it runs successfully but does not actually return anything. – Johnrad Apr 24 '14 at 19:06
  • Actually, the whitespace isn't a problem. AQS seems to be pretty intelligent in how it parses the queries, and from what I've seen only a space followed by a sequence of letters followed by `:` is interpreted as a new property. However, I've found that **Search-Mailbox** can't handle ISO-8601 date formats, and requires a format conforming with the regional settings. I have no problem searching with queries like `-SearchQuery "Received:<24/04/2014 15:09:37 kind:Email"`. – Adi Inbar Apr 24 '14 at 19:14
  • To qualify that, the error message the OP posted, `The property keyword isn't supported`, *does* seem to imply that PowerShell is interpreting something as an invalid property name; the error I get for invalid date formats is`Please adjust the date time. Make sure it is in the correct format.` or `There is an unexpected character in the query. Please re-type the query and try again.` However, he's probably tried so many things by now that I'm wondering if he might have mixed up which error message was for which command. With his code, I get the "unexpected character" error. – Adi Inbar Apr 24 '14 at 19:20
  • @AdiInbar I have never received any error aside from the property keyword error. But you are correct, I have tried what seems like dozens of different solutions. Without a searchQuery I can delete all content, when adding a searchQuery I get nothing. – Johnrad Apr 24 '14 at 19:23
  • @AdiInbar Hm, the documentation says that a string must not contain white space. (which is moot since the T is literally interpreted and not white space as I had thought originally) – TheMadTechnician Apr 24 '14 at 19:26
  • I am not entirely sure that the searchQuery will accept the time in hours, minutes, and seconds. As formatted in your answer, it doesn't like it. If I reformat the get date to `yyyy/MM/ddTHHmmss` even though it looks silly, it actually works. I don't think it likes the colon. Either way, when I just search by `yyyy/MM/dd` even, no results are found when I am actually seeing 7 objects it should be deleting in the mailbox. – Johnrad Apr 24 '14 at 19:38
  • @TheMadTechnician Correct, the documentation says that a *string* must not contain whitespace. However, the data type for the **Received** property is not a string, it's an absolute date. Absolute dates have a space between the date and the time (if you're specifying a time). – Adi Inbar Apr 24 '14 at 20:52
1

I use this format and it works fine -SearchQuery "Received:> $(‘22/09/2014 02:15:00’) AND Received:< $(‘22/09/2014 03:15:00’)"

So this should work: Search-Mailbox whism_j -SearchQuery "Received:> $(‘22/09/2014 02:15:00’) AND Received:< $(‘22/09/2014 03:15:00’)" -deleteContent

If you want to search a date range without being specific to the second, it is much easier: Search-Mailbox whism_j -SearchQuery "Received:20/09/2014..23/09/2014" -deleteContent

Also, I have the issue with doing a Search-Mailbox with a SearchQuery and it doesn't find any results, but Search-Mailbox with NO SearchQuery shows the message. There are two things to check that seem to work. First, move the database to another server, then wait (minutes/hours/day?) then search again. Usually I can find the message. Next is to check if it's actually indexed with this command Get-FailedContentIndexDocuments test@test.com | where {$_.subject -match "some words"} |ft Subject, Description -AutoSize

Ryan
  • 11
  • 1