locked
Run logout script as part of the query RRS feed

  • Question

  • I am using Power Query to load the results of calls to the NeonCRM Rest API. All of the API calls use WebContent, passing in a URL and setting the query parameters. They return JSON which I have been able to handle. This API requires you to get a user session id by calling a login API call and passing in an identifier and the name of the location you are visiting.  You use that using session id in all of your queries. I have been successful in running my query.

    Here is my problem.  There is a logout API call that involves passing in the user session id.  I'm puzzled as to how to do this.  Basically, after I run my query and get my data, I want to be able to call logout.

    How do you structure your M code to do that?  Is there a better way to structure this?

    I am using Excel 2013.  I am developing for a non-profit, so I am not sure if they are going to consider the Power BI route.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, March 28, 2016 6:45 PM

Answers

  • Logoff would get the same userSessionId that is used in the Web.Contents call. However, calling Logoff as part of the call to GetJsonDocument will logout before getting the json document. Here's an alternative you could try:

    let
     userSessionId = UserSessionID("MYKEY","MYORG"),
     getJsonResult = (url as text) => Json.Document(Web.Contents(url, [Query=[responseType="json", userSessionId=userSessionId]])),
     members = getJsonResult("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms"),
     logout = getJsonResult("https://api.neoncrm.com/neonws/services/api/common/logout"),
     returnFirstArg = (x, y) => x,
     result = returnFirstArg(members, logout)
    in
     result

    This ensures that the members call is made prior to the logout call.

    Ehren

    • Marked as answer by Russ Loski Tuesday, March 29, 2016 8:33 PM
    Tuesday, March 29, 2016 6:54 PM
  • Thank you for your help with this. Your point about logging off too soon was spot on. Here is my complete solution. I have broken this out into multiple queries (can I have one query for all of the functions? I have one per function).

    Logging in (UserSessionID:

    let
      UserSessionID = (apikey, location) => 
      let Source = Json.Document(Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/common/login"
        , [Query=[login.apiKey=apikey,login.orgid=location]])),
        loginResponse = Source[loginResponse],
        UID= loginResponse[userSessionId]
       in UID
    in
        UserSessionID

    Next this is the LogOff function.  It takes the UserSessionID that comes from the previous function:

    let
      Logoff = (userSessionID) => 
      let Source = Json.Document(Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/common/logout"
        , [Query=[userSessionId=userSessionID, responseType="json"]])),
       cnt = Record.FieldCount(Source)
       in cnt
    in
        Logoff

    I needed to return something that I could act on in my FirstArg function:

    FirstArg is what I am going to call to force this LogOff function to run:

    let
      FirstArg= (x, y) =>
      let Source = x
       in Source
    in
        FirstArg

    This puts it all together:

    let
        userSessionId = UserSessionID("MyUserID","MyOrg"),
        members=  Json.Document(
        Web.Contents("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]])),
        listMembershipTermsResponse= FirstArg(members[listMembershipTermsResponse],LogOff(userSessionId)),
         membershipTerms = listMembershipTermsResponse[membershipTerms],
        #"Converted to Table" = Record.ToTable(membershipTerms),
        #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
        #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"termInfo", "isParentTerm"}, {"Value.termInfo", "Value.isParentTerm"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"}),
        #"Expanded Value.termInfo" = Table.ExpandRecordColumn(#"Removed Columns", "Value.termInfo", {"id", "name"}, {"Value.termInfo.id", "Value.termInfo.name"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Value.termInfo",{{"Value.termInfo.id", "id"}, {"Value.termInfo.name", "name"}, {"Value.isParentTerm", "isParentTerm"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "TestCustom", each userSessionId)
    in
      #"Added Custom"

    A couple comments. My first pass was to break up the Json.Document and Web.Contents call.

    webContents =  Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]]),    
    
    members=  Json.Document(FirstArg(webContents,LogOff(userSessionId))),
        listMembershipTermsResponse= members[listMembershipTermsResponse], 

    As you pointed out, it logged out before calling the listMembershipTerms API.  So I moved it down to the listMembershipTermsResponse line.  It gets the data an logs off.

    For debugging purposes, I added the userSessionId as a column to the output.  I then use Chrome to manually make the logout call.  With this sequence of code, it appears to be logging off.

    Thank you for your help.  This would have taken me much longer to figure out, if I could have at all.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Russ Loski Tuesday, March 29, 2016 8:33 PM
    Tuesday, March 29, 2016 8:33 PM

All replies

  • Order of execution in M is demand-based, so if you wanted to call a logout API you would need to ensure the expression doing the logout was "demanded" (i.e. asked for) after all your other calls were complete.

    If you could paste the formulas you're using to accomplish this (minus any sensitive data), it would be helpful.

    Is it absolutely necessary to logout? What happens if you don't?

    Ehren

    Monday, March 28, 2016 11:08 PM
  • First, the call that I make to logout:

    https://api.neoncrm.com/neonws/services/api/common/logout?
    userSessionId=fff000333&responseType=json

    I honestly don't know what happens if I don't log out.  I think that there is a slight chance someone might be able to use my userSessionId, which identifies me to the NeonCRM server, if they can read the id somehow.  At some point it will expire.  I just prefer to close things down.

    Here is what I was thinking.  I have the  following code (this is repeated over and over with different API calls):

    let
        userSessionId = UserSessionID("MYKEY","MYORG"),
        members= Json.Document(Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]]))

    I thought about creating a logout script that just receives the userSessionId.  It makes the API call above.  I would also have a function like this:

    let
     GetJsonDocument (webcontent, logout) =>
     let
        members= Json.Document(webcontent)
    in members


    Then my main query would be

    let
        userSessionId = UserSessionID("MYKEY","MYORG"),
        web= Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]]),
        members = GetJsonDocument (web, Logoff(userSessionId)),
    ...

    My questions about that.  Would Logoff get a new userSessionId or would it use the same userSessionId that is used in the Web.Contents call ( I would need it to be the same)?  Would the Logoff function be called even if its value were never used?

    I am still a newby with M.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, March 28, 2016 11:56 PM
  • Logoff would get the same userSessionId that is used in the Web.Contents call. However, calling Logoff as part of the call to GetJsonDocument will logout before getting the json document. Here's an alternative you could try:

    let
     userSessionId = UserSessionID("MYKEY","MYORG"),
     getJsonResult = (url as text) => Json.Document(Web.Contents(url, [Query=[responseType="json", userSessionId=userSessionId]])),
     members = getJsonResult("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms"),
     logout = getJsonResult("https://api.neoncrm.com/neonws/services/api/common/logout"),
     returnFirstArg = (x, y) => x,
     result = returnFirstArg(members, logout)
    in
     result

    This ensures that the members call is made prior to the logout call.

    Ehren

    • Marked as answer by Russ Loski Tuesday, March 29, 2016 8:33 PM
    Tuesday, March 29, 2016 6:54 PM
  • Thank you for your help with this. Your point about logging off too soon was spot on. Here is my complete solution. I have broken this out into multiple queries (can I have one query for all of the functions? I have one per function).

    Logging in (UserSessionID:

    let
      UserSessionID = (apikey, location) => 
      let Source = Json.Document(Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/common/login"
        , [Query=[login.apiKey=apikey,login.orgid=location]])),
        loginResponse = Source[loginResponse],
        UID= loginResponse[userSessionId]
       in UID
    in
        UserSessionID

    Next this is the LogOff function.  It takes the UserSessionID that comes from the previous function:

    let
      Logoff = (userSessionID) => 
      let Source = Json.Document(Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/common/logout"
        , [Query=[userSessionId=userSessionID, responseType="json"]])),
       cnt = Record.FieldCount(Source)
       in cnt
    in
        Logoff

    I needed to return something that I could act on in my FirstArg function:

    FirstArg is what I am going to call to force this LogOff function to run:

    let
      FirstArg= (x, y) =>
      let Source = x
       in Source
    in
        FirstArg

    This puts it all together:

    let
        userSessionId = UserSessionID("MyUserID","MyOrg"),
        members=  Json.Document(
        Web.Contents("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]])),
        listMembershipTermsResponse= FirstArg(members[listMembershipTermsResponse],LogOff(userSessionId)),
         membershipTerms = listMembershipTermsResponse[membershipTerms],
        #"Converted to Table" = Record.ToTable(membershipTerms),
        #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
        #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"termInfo", "isParentTerm"}, {"Value.termInfo", "Value.isParentTerm"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"}),
        #"Expanded Value.termInfo" = Table.ExpandRecordColumn(#"Removed Columns", "Value.termInfo", {"id", "name"}, {"Value.termInfo.id", "Value.termInfo.name"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Value.termInfo",{{"Value.termInfo.id", "id"}, {"Value.termInfo.name", "name"}, {"Value.isParentTerm", "isParentTerm"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "TestCustom", each userSessionId)
    in
      #"Added Custom"

    A couple comments. My first pass was to break up the Json.Document and Web.Contents call.

    webContents =  Web.Contents
    ("https://api.neoncrm.com/neonws/services/api/membership/listMembershipTerms",
        [Query=[responseType="json",userSessionId=userSessionId]]),    
    
    members=  Json.Document(FirstArg(webContents,LogOff(userSessionId))),
        listMembershipTermsResponse= members[listMembershipTermsResponse], 

    As you pointed out, it logged out before calling the listMembershipTerms API.  So I moved it down to the listMembershipTermsResponse line.  It gets the data an logs off.

    For debugging purposes, I added the userSessionId as a column to the output.  I then use Chrome to manually make the logout call.  With this sequence of code, it appears to be logging off.

    Thank you for your help.  This would have taken me much longer to figure out, if I could have at all.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Russ Loski Tuesday, March 29, 2016 8:33 PM
    Tuesday, March 29, 2016 8:33 PM