Export Search Results with PowerShell

A while back, I wrote an introduction to how you could play with our C# SDK from PowerShell. And just the other day, Adrian wrote a post talking about how you could export really large result sets to CSV, using the REST API. It was a good read, but there was one problem: this was a somewhat Windows-centric post (talking about SharePoint data in his case), but he used curl to get the data out! We can most certainly do better than that for our Windows community, so that’s what I’m here to help solve.

What I ended up doing was to take an example from our dev docs about the search/jobs/export REST endpoint that looks like this:

$ curl -k -u admin:changeme https://localhost:8089/services/search/jobs/export 
       --data-urlencode search="search index=_internal | stats count by sourcetype" 
       -d output_mode=json -d earliest="rt-5m" -d latest="rt"

…and I turned it into PowerShell. The code is posted on gist.github.com with some comments and syntax highlighting and all that good stuff.  I also took the liberty of fleshing it out a little bit into something you might put into a script, rather than a one-liner, but PowerShell can certainly do concise as well. In fact, there is a built-in alias called ‘curl’ that maps to the PowerShell cmdlet Invoke-WebRequest. However, in this case, there’s another cmdlet that’s even more well-suited called Invoke-RestMethod. This cmdlet (built-in alias ‘irm’) does what ‘iwr’ does, but on top, it adds an output parsing layer that will turn JSON or XML text into PowerShell objects. So, it’s got that going for it.

Also note that you do not need to worry about url-encoding your search queries, the cmdlet does that for you. And one more note: PowerShell and .NET are very picky when it comes to self-signed SSL certificates. There’s not even a flag to override this behavior, which has long been a pet peeve of mine. I’ve included a quick workaround for that in my code sample, in case you are in the group like me that thinks that self-signed certs are perfectly reasonable in many internal use cases.

And…one more important note that I just discovered: don’t do a realtime search this way. Because of how both Invoke-RestMethod and Invoke-WebRequest work, they are not going to output anything until the request completes, and of course a realtime search won’t quit until you complete/cancel the search. There’s definitely a way to tackle streaming like this using PowerShell (probably with the system.net.webclient class), but that will have to be another blog post!

Anyway, check out the gist link for the longer form. And here below is a one-liner version, along with some sample output:

PS C:\> irm -Method Post -Uri https://server:8089/services/search/jobs/export -Body @{
  search="search index=_internal | stats count by sourcetype"
  output_mode="json"
  earliest="-5m" } -Credential (Get-Credential)
 
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
{"preview":true,"offset":0,"result":{"sourcetype":"splunk_web_access","count":"9624"}}
{"preview":true,"offset":1,"result":{"sourcetype":"splunk_web_service","count":"152"}}
{"preview":true,"offset":2,"result":{"sourcetype":"splunkd","count":"88494"}}
{"preview":true,"offset":3,"result":{"sourcetype":"splunkd_access","count":"15277"}}

----------------------------------------------------
Thanks!
Hal Rottenberg