Exporting lease history to CSV Using Curl/API?

[#1]

Hey Guys,

What would be the option to auto export all lease history for past 24 hours to a fileserver using ftp, https or something in csv format?

I found some examples like these:

curl -k1 -u admin:akmlab -X GET https://10.1.60.21/wapi/v2.2/lease?_return_fields%2b=address,starts,ends,tstp,binding_state,hardware,uid&_paging=1&_return_as_object=1&_max_results=10

curl -s -H "Accept: application/json" -X GET "https://192.168.1.2/wapi/v2.7.3/network" |
jq -r '.|[.executions[] |
   select(.job.name != null) |
   select(.job.name|contains("JIRA_Create_Subtask")) ] |
   sort_by(.id) | reverse | .[0] |
   [.status, .job.name, ."date-started".date, ."date-ended".date, .job.project] |
   @csv'

But not able to get it to work with csv export thing. 

I have no experience with curl or scripting, so some details would be nice..

Anybody tried this?

[#2]

Hi,

You can use the CSV export functionality as shown below:

Initiate a CSV export of all the leases:
curl -k -u admin:infoblox -H 'content-type: application/json' -X POST "https://grid-master/wapi/v2.9/fileop?_function=csv_export" -d '{"_object": "lease" }'

Sample output:
{
    "token": "eJytUEFuwyAQvPOR5BLb62A77i2VG7VSlUhJpR5XNuAUyQYKJEp+36VSe+2lB9Cys8PsjBDW3dGr\nM6NLWBOiv4hoPXPAlkKb0Q6TvWXWqHTi3anAuj72eFQjcyUTiMNFT1EbRCa1iMyt2VI6zk4LdXPa\n3zHqWS2Yq9gOKl40bcuhyXgFZV0BC6fFxU8E10T4iNGFhzyHIoO2zsqizErIUxelpvUijnpSqG3u\n1SdqueoO7/vXw7ZbQVG2RV0V0HC+WXOeP1tyQoa8DJkIVxJokpaWVG1I6q8PaKxNBEleqYaCOHmg\nYPqzyuPs/msrgB8VVEZYqc05dUuSe3zZp3L9OzBbmYKElG23fdvi8WmX3hUL8eigTrlDQ+Co1SQD\nRovCzq7336wN26cAht6hNu4S8ap80NYkrCVsyL4AsxyhcQ==\n",
    "url": "https://10.196.202.21/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv"
}

Download the CSV file:
curl -k -u admin:infoblox -H 'content-type: application/force-download' "https://grid-master/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv" -o "Leases.csv"

Remove the stored file using the token:
curl -k -u admin:infoblox -H 'content-type: application/json' -X POST "https://grid-master/wapi/v2.9/fileop?_function=downloadcomplete" -d '{"token" : "eJytUEFuwyAQvPOR5BLb62A77i2VG7VSlUhJpR5XNuAUyQYKJEp+36VSe+2lB9Cys8PsjBDW3dGr\nM6NLWBOiv4hoPXPAlkKb0Q6TvWXWqHTi3anAuj72eFQjcyUTiMNFT1EbRCa1iMyt2VI6zk4LdXPa\n3zHqWS2Yq9gOKl40bcuhyXgFZV0BC6fFxU8E10T4iNGFhzyHIoO2zsqizErIUxelpvUijnpSqG3u\n1SdqueoO7/vXw7ZbQVG2RV0V0HC+WXOeP1tyQoa8DJkIVxJokpaWVG1I6q8PaKxNBEleqYaCOHmg\nYPqzyuPs/msrgB8VVEZYqc05dUuSe3zZp3L9OzBbmYKElG23fdvi8WmX3hUL8eigTrlDQ+Co1SQD\nRovCzq7336wN26cAht6hNu4S8ap80NYkrCVsyL4AsxyhcQ==\n"}'

Hope this is helpful,

Krishna Vasudevan

[#3]

Thanks, I'll try it out.

[#4]

Hey Krishna,

Does this script control for the time value?

Can we restrict it to previous 24hrs or just make the device export this csv automatically to some file server using https, ftp or anything else?

[#5]

I am just getting errors with both the commands:

C:\curl-7.62.0-win64-mingw\bin>curl.exe -k -u admin:akmlab@123 -H 'content-type: application/json' -X POST "https://ibgm.akmlab.local/wapi/v2.9/fileop?_function=csv_export" -d '{"_object": "lease" }'
curl: (6) Could not resolve host: application
{ "Error": "AdmConProtoError: When sending FORM arguments no URI arguments can be set (FORM arguments: {\"'{_object:\": ['']}  URI arguments: {'_function': ['csv_export']}), please pass all the arguments as FORM arguments.",
  "code": "Client.Ibap.Proto",
  "text": "When sending FORM arguments no URI arguments can be set (FORM arguments: {\"'{_object:\": ['']}  URI arguments: {'_function': ['csv_export']}), please pass all the arguments as FORM arguments."
}curl: (6) Could not resolve host: lease
curl: (3) unmatched close brace/bracket in URL position 1:
}'
 ^

AND

C:\curl-7.62.0-win64-mingw\bin>curl.exe -k1 -u admin:akmlab@123 -X GET https://10.1.60.21/wapi/v2.2/lease?_return_fields%2b=address,starts,ends,tstp,binding_state,hardware,uid&_paging=1&_return_as_object=1&_max_results=10
^C'_paging' is not recognized as an internal or external command,
operable program or batch file.
'_return_as_object' is not recognized as an internal or external command,
operable program or batch file.
'_max_results' is not recognized as an internal or external command,
operable program or batch file.

One method which worked for me is this, but here I am merely using the url of the report which was alreay exported, but it sends it to csv alright: 

curl -k1 -u admin:akmlab@123 -X GET https://ibgm.akmlab.local/ui/FyilTMbbdMgBPjjR3YIesuOmIWmMs8qk6xDpvHeLd6iGgxhW4SbCVN3IcDj6DHbR247ClphJIjQITCV3rX6UqPt5Xqcr5Ix1N3Fp_cqCxyXFQGEaR18dHvqJU0UdDAGPvXbCAftSyOt2vC1Yp2lTjUkXRvr4GzOTV2JsU_JanlU/Fyid5/SbCa3/_Jae5/DAG29?antiCache=1542626907594 > dhcpl.csv

The url is from chrome from an already downloaded report.

[#6]

Hi,

The curl command here was run from a linux-based machine. The formatting will change if you use it on a Windows machine. Please retry with the following:

Initiate a CSV export of all the leases:
curl -k -u admin:infoblox -H "content-type: application/json" -X POST "https://grid-master/wapi/v2.9/fileop?_function=csv_export" -d "{\"_object\": \"lease\" }"

Sample output:
{
    "token": "eJytUEFuwyAQvPOR5BLb62A77i2VG7VSlUhJpR5XNuAUyQYKJEp+36VSe+2lB9Cys8PsjBDW3dGr\nM6NLWBOiv4hoPXPAlkKb0Q6TvWXWqHTi3anAuj72eFQjcyUTiMNFT1EbRCa1iMyt2VI6zk4LdXPa\n3zHqWS2Yq9gOKl40bcuhyXgFZV0BC6fFxU8E10T4iNGFhzyHIoO2zsqizErIUxelpvUijnpSqG3u\n1SdqueoO7/vXw7ZbQVG2RV0V0HC+WXOeP1tyQoa8DJkIVxJokpaWVG1I6q8PaKxNBEleqYaCOHmg\nYPqzyuPs/msrgB8VVEZYqc05dUuSe3zZp3L9OzBbmYKElG23fdvi8WmX3hUL8eigTrlDQ+Co1SQD\nRovCzq7336wN26cAht6hNu4S8ap80NYkrCVsyL4AsxyhcQ==\n",
    "url": "https://10.196.202.21/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv"
}

Download the CSV file:
curl -k -u admin:infoblox -H "content-type: application/force-download" "https://grid-master/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv" -o "Leases.csv"

Remove the stored file using the token:
curl -k -u admin:infoblox -H "content-type: application/json" -X POST "https://grid-master/wapi/v2.9/fileop?_function=downloadcomplete" -d \"{\"token\" : \"eJytUEFuwyAQvPOR5BLb62A77i2VG7VSlUhJpR5XNuAUyQYKJEp+36VSe+2lB9Cys8PsjBDW3dGr\nM6NLWBOiv4hoPXPAlkKb0Q6TvWXWqHTi3anAuj72eFQjcyUTiMNFT1EbRCa1iMyt2VI6zk4LdXPa\n3zHqWS2Yq9gOKl40bcuhyXgFZV0BC6fFxU8E10T4iNGFhzyHIoO2zsqizErIUxelpvUijnpSqG3u\n1SdqueoO7/vXw7ZbQVG2RV0V0HC+WXOeP1tyQoa8DJkIVxJokpaWVG1I6q8PaKxNBEleqYaCOHmg\nYPqzyuPs/msrgB8VVEZYqc05dUuSe3zZp3L9OzBbmYKElG23fdvi8WmX3hUL8eigTrlDQ+Co1SQD\nRovCzq7336wN26cAht6hNu4S8ap80NYkrCVsyL4AsxyhcQ==\n\"}"


 

[#7]

Thank youuuu so much for your replies, I will try it out!

[#8]

It worked but the csv file has an error that the resource does not exist, and can I only limit it to download files for 24 hrs only? 

C:\curl-7.62.0-win64-mingw\bin>curl.exe -k -u admin:akmlab@123 -H "content-type: application/json" -X POST "https://ibgm.akmlab.local/wapi/v2.9/fileop?_function=csv_export" -d "{\"_object\": \"lease\" }"
{
    "token": "eJytUE1rwzAMvfuPtJcmUT7b3TKywqC00A52FIntdIbE9myntP9+8mC77jKMjaynpyc9zo19oJNX\nRg832ge38GAcs8DWXOnRDJO5J0bLeMPDSs+6PvR4liOzOeOIw6KmoDQiE4oHZgu2FrZkl5W8W+Ue\nGNQsV8xWbA9VmTdQ5TUkTdlkwPxltbiJwJrKP0Kw/ilNIUsgqbMkhzSmUCiaLOCoJonKpE5+ohKb\n7vR+PJzabgOQZwAZnaaEoinSg+y99An3N2rcRA0lKNqSxF9cKttFgqANKYaMOKknO/qrTMNs/2Eg\ngB8BlJobofQ1ZnNSen49xrD4LZiNiM5BNLNr31o8v+zjv2I+nC3U0WhoCByVnITHYJCb2fbum7Vl\nx7j70FtU2i4Bb9J5ZXTEdoQNyReS7Jux\n",
    "url": "https://10.1.60.21/http_direct_file_io/req_id-DOWNLOAD-1120110101741373/Leases.csv"
}
C:\curl-7.62.0-win64-mingw\bin>curl.exe curl -k -u admin:akmlab@123 -H "content-type: application/force-download" "https://ibgm.akmlab.local/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv" -o "Leases.csv"

C:\curl-7.62.0-win64-mingw\bin>
C:\curl-7.62.0-win64-mingw\bin>
C:\curl-7.62.0-win64-mingw\bin>
C:\curl-7.62.0-win64-mingw\bin>curl.exe -k -u admin:akmlab@123 -H "content-type: application/force-download" "https://ibgm.akmlab.local/http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv" -o "Leases.csv"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   261  100   261    0     0   1388      0 --:--:-- --:--:-- --:--:--  1388

I checked on the GM lab device it still contains lease history information but it was not exported to this csv. 

And opening the link directly in browser gives me some format error:

[#9]

Hi,

To download the CSV file, you need to use the URL that was returned after the first curl command, i.e, in your case, pass the URL

https://10.1.60.21/http_direct_file_io/req_id-DOWNLOAD-1120110101741373/Leases.csv

 So your command will change to

curl.exe -k -u admin:akmlab@123 -H "content-type: application/force-download" "https://10.1.60.21/http_direct_file_io/req_id-DOWNLOAD-1120110101741373/Leases.csv" -o "Leases.csv"

Also, I cannot see the images you have posted.

Hope this gets it working,

Krishna

[#10]

Hi

Yes I did use that same url for the download as you say;

I don't know why you can't see the images.

I'll upload to imgur then.

https://imgur.com/a/scU0L0g

This is what I see in the excel sheet:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>404 Not Found</title>
</head><body>
<h1>Not Found</h1>
<p>The requested URL /http_direct_file_io/req_id-DOWNLOAD-1029065017448344/Leases.csv was not found on this server.</p>
</body></html>

[#11]

Hi,

Please check the URL. I've highlighted the change you need to make.

curl.exe -k -u admin:akmlab@123 -H "content-type: application/force-download" "https://10.1.60.21/http_direct_file_io/req_id-DOWNLOAD-1120110101741373/Leases.csv" -o "Leases.csv"

[#12]

This worked. 

Can't i pass time values as paremeters? For last 24hrs?

The way you added -d "{\"_object\": \"lease\" }" ?

And export to some external server?

I cannot find any info on scheduling scripts or crone jobs on NIOS.

[#13]

Hi,

With leases, none of the timestamp related fields are searchable. So, I am not aware of a way to get just the last 24 hours.

Also, to download onto an external server, while specifying the output file, you can specify the location in the external server.

Thanks,

Krishna

[#14]

---

@kvasudevan wrote:

Also, to download onto an external server, while specifying the output file, you can specify the location in the external server.

So this is within the curl command itself, right? I will try it. Thanks. 

---

[#15]

You may also want to take a look at this:

https://community.infoblox.com/t5/Security/On-demand-DHCP-lease-history-search-from-SIEM/m-p/15696#M...