Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 general forum
Answered

XRMToolBox - FetchXML Builder

Posted on by 85

Hi, 

How can you export to excel your FetchXML results?

Thanks

Categories:
  • RaviKashyap Profile Picture
    RaviKashyap 55,406 on at
    RE: XRMToolBox - FetchXML Builder

    Not sure. Did you check if the audit record for that user exists. As long as the records are there, it should appear in the list.

  • Teach Me Profile Picture
    Teach Me 85 on at
    RE: XRMToolBox - FetchXML Builder

    Hi Ravi,

    I have added a condition to eliminate the record limit issue:

      <condition entityname="SystemUser" attribute="createdon" operator="last-x-months" value="12" />

    I could then execute the fetchXML, however when I checked the results against the Audit log on CRM I found that the last logons are not correct. I have users logged on to CRM today and showing in the data table that they were not logged on since April. Any idea what went wrong?

  • Teach Me Profile Picture
    Teach Me 85 on at
    RE: XRMToolBox - FetchXML Builder

    Thank you Ravi. I tried in FetchXML Builder and it gave me this error:

    AggregateQuueryRecordLimit exceeded. Cannot perform this operation.

  • Suggested answer
    RaviKashyap Profile Picture
    RaviKashyap 55,406 on at
    RE: XRMToolBox - FetchXML Builder

    Hi,

    try this:

    ===========

    <fetch distinct="false" no-lock="false" mapping="logical" aggregate="true" >

     <entity name="audit" enableprefiltering="1" >

       <attribute name="action" groupby="true" alias="action" />

       <attribute name="createdon" alias="LastLogonDate" aggregate="max" />

       <filter type="and" >

         <condition attribute="action" operator="eq" value="64" />

       </filter>

       <link-entity name="systemuser" from="systemuserid" to="objectid" link-type="outer" alias="SystemUser" >

         <attribute name="fullname" groupby="true" alias="fullname" />

         <attribute name="windowsliveid" groupby="true" alias="liveid" />

         <attribute name="businessunitid" groupby="true" alias="buid" />

         <attribute name="isdisabled" groupby="true" alias="disabled" />

       </link-entity>

     </entity>

    </fetch>

    =============

  • Teach Me Profile Picture
    Teach Me 85 on at
    RE: XRMToolBox - FetchXML Builder

    Hi Ravi,

    It did work with the Advanced FetchXML Tester and this way I could export it to excel too. 

    Thank you! :-)

    Do you know by any chance how can adjust the Fetch XML to only display max of the createdon for all users? I'm hoping this way I could have the last log on date for all users. 

    Many thanks, 

  • Verified answer
    RaviKashyap Profile Picture
    RaviKashyap 55,406 on at
    RE: XRMToolBox - FetchXML Builder

    Hi,

    I tried Advance Fetch XML Tester tool and it worked as expected i.e. fetch returned me the results and I was also able to export them. It is possible that tou have more records and the tools is not able to handle those many records. Try putting a top count on your fetchxml and see if it works.

    ========

    <fetch distinct="false" no-lock="false" mapping="logical" top="50" >

    <entity name="audit" >

      <attribute name="createdon" />

      <attribute name="action" />

    ..

    ..

    ..

    =============

    3288.audit.png

    Hope this helps.

  • Teach Me Profile Picture
    Teach Me 85 on at
    RE: XRMToolBox - FetchXML Builder

    Hi Ravi,

    Sure, here is it:

    <fetch distinct="false" no-lock="false" mapping="logical" >

     <entity name="audit" >

       <attribute name="createdon" />

       <attribute name="action" />

       <attribute name="userid" />

       <attribute name="objectid" />

       <attribute name="objecttypecode" />

       <order attribute="createdon" descending="true" />

       <filter type="and" >

         <condition attribute="action" operator="eq" value="64" />

       </filter>

       <link-entity name="systemuser" from="systemuserid" to="objectid" link-type="outer" alias="SystemUser" >

         <attribute name="fullname" />

         <attribute name="windowsliveid" />

         <attribute name="businessunitid" />

         <attribute name="isdisabled" />

       </link-entity>

     </entity>

    </fetch>

    I had this idea from here:

    www.concurrency.com/.../creating-an-audit-report-for-user-logins-in-dynami

    Basically the end goal would be to be able to export to excel the users with their log dates and times.

    Many thanks,

  • RaviKashyap Profile Picture
    RaviKashyap 55,406 on at
    RE: XRMToolBox - FetchXML Builder

    Can you share the fetchxml you are trying? It is also possible that the data returned is too huge and the tools are experiencing timeout?

  • Teach Me Profile Picture
    Teach Me 85 on at
    RE: XRMToolBox - FetchXML Builder

    I can't see the results in Excel when I open the XML in Excel, only the query.

    With the XRM Tool Box the Export to Excel doesn't have an option to select Audit so I can't work from my FetchXML. I tried the Data Exporter, I could paste the FetchXML, but it was running for a few minutes and then it gave me a runtime error.

  • RaviKashyap Profile Picture
    RaviKashyap 55,406 on at
    RE: XRMToolBox - FetchXML Builder

    Are you saying you are not getting the option to ecport to excel in any of these plugin?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans