Mongoexport with --query option to export some columned for last calendar day

Working with Data Developer Tools
1

#!/bin/bash
last_date=date +%Y-%m-%d -d "yesterday"
mongoexport --host=10.234.242.29 --port=27018 --username=dba --password=MRKLg2p2+rByvcRrVmXRTIYk3HVO68AkMHys+ojaRYeqVzOQSdc56M4= --authenticationDatabase=admin --collection=Purchases --db=ProdSettlementCT --out=out.csv --query ‘{"$and" : [{ “CreationDate” : {"$gte": {"$date":"$last_date"}}}, {“Customer.SiteId” : {"$in" : [20017,297]}}]}’ --verbose=10 --fields=CreationDate,SQLTicketId,Customer.SiteId --type=csv

I need to export these fields (CreationDate,SQLTicketId,Customer.SiteId) for last calendar day but wil no success. Please help!!!

2

Could you please give me any update?

3

{"$and" : [{“CreationDate”:{ “$gte” :{"$date":“2021-10-06T00:00:00Z”}}},{“CreationDate”:{"$lt" : {"$date":“2021-10-07T00:00:00Z”}}},{“Customer.SiteId” : {"$in" : [20017,297]}}]

this is working but I have to change the date every day. I need to take only the last calendar date. Please assist?

4

Now I understand the issue.

It is all about bash variables. The tricky part is to have $last_date to be a bash variable and to NOT have all others like $gte, $date to be variables. You have to play with the quotes. I am pretty sure that if you put single quotes around $last_date it will work. But you still want the double quotes so the current double quotes around $last_date have to stay. To be clear you want to go from
"$last_date" to "'$last_date'" with no space between the double and single quotes (because you want --eval to be a single argument). The single quote just before $last_date will terminate the first single quote you have at the beginning of the query. So $last_date will be substituted by the shell. The single quote after $last_date will start a new string but still part of the single --eval argument.

5

I am also working for the same case,Mongoexport with –query option to export for last calendar day data automatically is not working.

mongoexport --host= --port=27018 --username=user --password= --db=Alteryx --collection=auditEvents --query="{‘Timestamp’:{’$gte’:{’$date’:’$last_date’}}}" --out=D:\data1.csv

Mongoexport with –query option to export for last calendar day data automatically is not working, Could you please help us with the correct format with automatic querying option.

Can anybody help me for the issue ?

6

Please apply the same solution. Make sure $last_date is interpreted as a shell variable and that $gte and $date are not. This being written. I notice that you are using Windows. This means that you might want to use %last_date or %last_date% as it is how, I think, Windows identifies variable.

7

@Latha_Karthikeyan, any update on that for Windows?

@Teodor_Chakalov, if my post helped pleased mark it as the solution so that the thread can be closed and others know they can follow the suggestion.

8

Hi @steevej,

No it didnt word, i am using batch script and i tried environment variable %DATE% but it throws error.
2021-11-25T08:52:19.210+0000 error validating settings: parsing time “Thu 11/25/2021” as “2006-01-02T15:04:05Z07:00”: cannot parse “Thu 11/25/2021” as “2006”
2021-11-25T08:52:19.211+0000 try ‘mongoexport --help’ for more information

Hope it is not taking the date as the required format. But not sure how to resolve this. If you could help for this it would be great.

9

You cannot used Windows default %DATE% since the format, as you have notice, is not appropriate.

You need to initialize your own variable with the date in the appropriate format.

You may use Get-Date (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Learn to get the date in the appropriate format.

10

Hi Steeve,

Somehow i found batch command to fetch current date, but not i have issue is if i give query like --query="{‘Timestamp’:{’$gte’:{’$date’:’$PresentData&Time’}}}" here it is $gte so there will not be any entry in database and if i give $lte it will fetch all the data from scratch.

So my requirement is like it should fetch the data from 12:01AM to 11.59PM, hope you understand.

Kindly help me how i can i accomplish this.

11

As already mentioned the $Variable is not the way to access variables in Windows. See

12

@Latha_Karthikeyan, we you able to pass your variable using Windows syntax?

If you did, mark on the post as the solution so that others know they can following same advice.

13

@steevej

SET MONGOEXPORT="%ProgramFiles%\Alteryx\bin\mongoexport.exe"
FOR /f %%a IN (‘WMIC OS GET LocalDateTime ^| FIND “.”’) DO SET DTS=%%a
SET Date=%DTS:~0,4%-%DTS:~4,2%-%DTS:~6,2%
SET Time=%DTS:~8,2%:%DTS:~10,2%:%DTS:~12,2%%DTS:~14,4%Z
SET /a tztemp=%DTS:~21%/60
SET tzone=UTC%tztemp%
SET DateTime=%DTS:~0,4%%DTS:~4,2%%DTS:~6,2%_%DTS:~8,2%%DTS:~10,2%%DTS:~12,2%

SET JSON="{‘Timestamp’:{’$gte’:{’$date’:’%Date%T00:00:00.000Z’}}}"

%MONGOEXPORT% --port=27018 --username=user --password=%PS% --db=AlteryxGallery --collection=auditEvents --query=%JSON% >>%OUTPUT%AuditEvents%datetime%.csv

I was using this batch script to export audit events in Mongodb4.0. now my environment is migrated to 4.2 and 4.4 . If i run the query now it s giving below error.

2022-09-14T12:33:33.389+0100 connected to: mongodb://localhost:27018/
2022-09-14T12:33:33.390+0100 Failed: error parsing query as Extended JSON: invalid JSON input

Can you clarify why and what modification should i do