turbot / steampipe-plugin-salesforce Goto Github PK

View Code? Open in Web Editor NEW

9.0 10.0 6.0 549 KB

Use SQL to instantly query Salesforce resources. Open source CLI. No DB required.

Home Page: https://hub.steampipe.io/plugins/turbot/salesforce

License: Apache License 2.0

Makefile 0.15% PLSQL 1.98% Go 97.87%

steampipe steampipe-plugin salesforce postgresql postgresql-fdw hacktoberfest sql backup etl sqlite

steampipe-plugin-salesforce's Introduction

Salesforce Plugin for Steampipe

Use SQL to query infrastructure accounts, users, oppurtinities and more from your Salesforce instance.

Get started →
Documentation: Table definitions & examples
Community: Join #steampipe on Slack →
Get involved: Issues

Quick start

Install the plugin with Steampipe:

steampipe plugin install salesforce

Configure your credentials and config file.

Run a query:

select
  name,
  amount,
  close_date
from
  salesforce_opportunity
where
  is_won;

Engines

This plugin is available for the following engines:

Engine	Description
Steampipe	The Steampipe CLI exposes APIs and services as a high-performance relational database, giving you the ability to write SQL-based queries to explore dynamic data. Mods extend Steampipe's capabilities with dashboards, reports, and controls built with simple HCL. The Steampipe CLI is a turnkey solution that includes its own Postgres database, plugin management, and mod support.
Postgres FDW	Steampipe Postgres FDWs are native Postgres Foreign Data Wrappers that translate APIs to foreign tables. Unlike Steampipe CLI, which ships with its own Postgres server instance, the Steampipe Postgres FDWs can be installed in any supported Postgres database version.
SQLite Extension	Steampipe SQLite Extensions provide SQLite virtual tables that translate your queries into API calls, transparently fetching information from your API or service as you request it.
Export	Steampipe Plugin Exporters provide a flexible mechanism for exporting information from cloud services and APIs. Each exporter is a stand-alone binary that allows you to extract data using Steampipe plugins without a database.
Turbot Pipes	Turbot Pipes is the only intelligence, automation & security platform built specifically for DevOps. Pipes provide hosted Steampipe database instances, shared dashboards, snapshots, and more.

Developing

Prerequisites:

Clone:

git clone https://github.com/turbot/steampipe-plugin-salesforce.git
cd steampipe-plugin-salesforce

Build, which automatically installs the new version to your ~/.steampipe/plugins directory:

make

Configure the plugin:

cp config/* ~/.steampipe/config
vi ~/.steampipe/config/salesforce.spc

Try it!

steampipe query
> .inspect salesforce

Open Source & Contributing

This repository is published under the Apache 2.0 (source code) and CC BY-NC-ND (docs) licenses. Please see our code of conduct. We look forward to collaborating with you!

Steampipe is a product produced from this open source software, exclusively by Turbot HQ, Inc. It is distributed under our commercial terms. Others are allowed to make their own distribution of the software, but cannot use any of the Turbot trademarks, cloud services, etc. You can learn more in our Open Source FAQ.

Get Involved

Join #steampipe on Slack →

Want to help but don't know where to start? Pick up one of the help wanted issues:

steampipe-plugin-salesforce's People

Contributors

Stargazers

Watchers

Forkers

meetsang d416 thoughtspot ppolydoras raw-labs

steampipe-plugin-salesforce's Issues

http code: 431 error while reading data from salesforce account table

Describe the bug
Salesforce account object has 754 columns with 730 custom columns and the rest are default columns. While fetching data from this object getting 431 error.

Steampipe version (steampipe -v)
v0.21.2

Plugin version (steampipe plugin list)
v0.7

To reproduce
Pass the credentials in the config file

Expected behavior
Should be queryable

Additional context
No

Optimize hydrating/retrieving data by using SOQL functions as much as possible

Is your feature request related to a problem? Please describe.
SOQL should be used as much as possible for executing data functions before retrieving.

Describe the solution you'd like
A couple of examples...

EXAMPLE 1:
select count(*) from salesforce_opportunity

^ this query will
a) download all records from salesforce_opportunity
b) perform a SQL count() on the data in postgres

It works for a few hundred records maybe, but for data sets of thousands this takes quite awhile. SOQL has its own count() function, so why not use that and let Salesforce carry the load?

EXAMPLE 2:

select id, createddate from salesforce_account limit 10

^ this query will retrieve the first 10 records from the Account object and runs relatively quickly.

but...

select id, createddate from account order by createddate desc limit 10

^ this query will download all records from the Account object then sort them in Postgres by createddate, then return the first 10 records which can take quite awhile.
Salesforce has its own 'order by' function - why not use it and let Salesforce do the lifting here?

Describe alternatives you've considered
Waiting for real simple queries like the above to execute, or even time-out if large data is present in Salesforce.

unable to authenticate

A user reported this error:

2023-07-11 00:04:46.616 UTC [WARN] steampipe-plugin-salesforce.plugin: [WARN] 1689033885946: error channel received client login error [simpleforce] Error. http code: 405 Error Message: Error Code:

I set up a fresh account and replicated it. Debugging the simpleforce SDK, I see that it is sending a SOAP body with all the expected values:

    <env:Envelope
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
            xmlns:urn="urn:partner.soap.sforce.com">
        <env:Header>
            <urn:CallOptions>
                <urn:client>3M...TM</urn:client>
                <urn:defaultNamespace>sf</urn:defaultNamespace>
            </urn:CallOptions>
        </env:Header>
        <env:Body>
            <n1:login xmlns:n1="urn:partner.soap.sforce.com">
                <n1:username>[email protected]</n1:username>
                <n1:password>T9AL...LCv</n1:password>
            </n1:login>
        </env:Body>

And it's sending it to what I think is the right endpoint:

https://jonudell-dev-ed.develop.lightning.force.com/services/Soap/u/54.0

The 405 error is:

<body><h2>HTTP ERROR 405</h2>
<p>Problem accessing /services/Soap/u/54.0. Reason:
<pre>    Only POST allowed</pre></p><hr /><br/>

But the code does seem to be sending a POST?

https://github.com/simpleforce/simpleforce/blob/master/force.go#L168

Make a Connected App optional to authenticate to Salesforce, and client_id optional in the salesforce.spc

Is your feature request related to a problem? Please describe.
Salesforce allows logging in with only a username and password, if the org is configured that way. Similar to a token, a Connected App is not required to authenticate.

Describe the solution you'd like
Make client_id in the salesforce.spc optional. If it does not exist, use only the credentials that are there, which is username/password, or token if it exists. Username and Password are the minimum required to authenticate.

Describe alternatives you've considered
Setting up a Connected App is cumbersome and may not be available to a non-admin user. User in Salesforce can be enabled with API access by an administrator, so those Users who are comfortable using SQL could use this plugin, however not if a Connected App is required.

Additional context
Many SF devs and power users authenticate to the REST API using their own credentials. This is useful for casual querying, or testing as other users.
Using a Connected App is best practice for formal integration uses, however for all other uses including casual querying this is unnecessary overhead.

Salesforce plugin

Hey,
How can I collect “Event Monitoring” events from salesforce via the plugin?

Support for SOQL Relationship Queries

Is your feature request related to a problem? Please describe.
Feature request: Support for Salesoforce SOQL relationship queries.

Describe the solution you'd like
Relationship between object can be maintained in Salesforce through 'lookup' fields. For example, on the Contact object there is a field called AccountId which will return the ID of the Account its associated with, however if you wanted to also retrieve the Name field on the Account, you could query it directly through the Contact schema via 'dot' notation like so:
Select Id, Name, AccountId, Account.Name from Contact limit 100

Describe alternatives you've considered
The alternative is to download all Contact records, download all Account records, then join them to get Name

Additional context
Salesforce docs on relationship queries
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm

Alternative idea: Allow direct SOQL passthrough of queries, then just parse the schema that is returned?

Remove/Disable Snake Casing for Field Names and Object Names

Is your feature request related to a problem? Please describe.
As a Salesforce developer, I find it confusing to have all the API names of Fields and Objects changed to snake case. Can this behaviour be removed from the plugin or optionally disabled? For Salesforce devs, the plugin isn't very useful with these name transformations. (see alternatives and additional context below)

Describe the solution you'd like
API names of fields and objects in Salesforce should be queryable as-is without snake casing, so dual-word field names like 'CreatedDate' should not be changed to 'created_date', and for objects, names like PermissionSet are now 'permission_set'. It would also be useful to keep the full proper case (capitalization as defined) on a field and object instead of lowercasing the name, but if this is an issue with case sensitivity in golang then all lowercase would be the best option here.

Describe alternatives you've considered
The alternative is for Salesforce devs to learn what the new transformed names of fields and objects when querying. Many of us (SF developers) are already familiar with our schema, so we'd need to un-learn this.

Additional context
Many SF devs use SOQL which is similar to SQL and so are quite familiar with all the field names in an object as they need to be recalled from memory when building a query.

Dynamically get Salesforce Object Names and their Field Names

Is your feature request related to a problem? Please describe.
Unless I'm missing something, it seems that for a Salesforce object to be queryable, the object name must be hard coded into the salesforce.spc config. Can the plugin dynamically query all available Object names and fields names through the connection?

Describe the solution you'd like
simpleforce library has a DescribeGlobal() function which returns a list of all objects.
https://github.com/simpleforce/simpleforce/blob/acf4ac67ef68eee62febf8e1afac93b55b0e6512/force.go#L336
(I believe field names are fetchable through this function)

Ideally, this plugin should behave more like the CSV plugin, where the number of .csv files, the names of the files, and the columns within each file could widely vary due not only to a SF instance's data model, but also the field-level and object security model. The best way to handle this would be to dynamically get whatever the logged in user is allowed to see. For the plugin description, make only 1 table available, and that is 'Objects' which would return a list of all objects available.

Describe alternatives you've considered
The alternative to hard code Object names is cumbersome and not practical, especially for instances which have hundreds of objects (like my company's)

Additional context
Access to objects and fields are defined at the user profile level, so having these pre-defined in the plugin may not be an accurate representation of what the logged-in user has access to. For example, in Opportunity there is an out-of-box field called ExpectedRevenue. In my company's instance we've disabled access to this field in lieu of a custom field. But in this plugin, no matter which query I execute on Opportunity (example: select id from opportunity limit 10) I get the error "No such column 'ExpectedRevenue' on entity 'Opportunity'." which is from Salesforce. I've been able to reproduce this in a new developer sandbox.

Implement dynamic query generation based on requested columns for API

Is your feature request related to a problem? Please describe.
Requested query -

select id, name from salesforce_account;

Actual query in the API -

2024-04-09 04:34:31.904 UTC query::="SELECT Id, Name, AnnualRevenue, Industry, OwnerId, Type, AccountSource, CleanStatus, CreatedById, CreatedDate, Description, IsDeleted, LastModifiedById, LastModifiedDate, NumberOfEmployees, Ownership, Phone, Rating, Sic, TickerSymbol, Tradestyle, Website, BillingAddress, ShippingAddress, MasterRecordId, ParentId, Fax, AccountNumber, PhotoUrl, Site, SystemModstamp, LastActivityDate, LastViewedDate, LastReferencedDate, Jigsaw, JigsawCompanyId, DunsNumber, NaicsCode, NaicsDesc, YearStarted, SicDesc, DandbCompanyId, OperatingHoursId, customerpriority__c, sla__c, active__c, numberoflocations__c, upsellopportunity__c, slaserialnumber__c, slaexpirationdate__c FROM Account"

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

414 Error when making basic Salesforce Queries

Describe the bug
When trying to make basic queries to some Salesforce tables, a 414 error is returned with the following message:
ERROR: rpc error: code = Unknown desc = [simpleforce] Error. http code: 414 Error Message: Error Code: (SQLSTATE HV000)

Here is a list of tables I am facing this issue with:
salesforce_account
salesforce_contact
salesforce_lead
salesforce_opportunity

I am making basic queries like so:
select * from salesforce_account limit 1;

Steampipe version (steampipe -v)
We are using the cloud version v0.20.12

Plugin version (steampipe plugin list)
Latest version (v0.5.0)

To reproduce

Create a Salesforce connection on pipes.turbot.com
Navigate to the query page
Run a query like select * from salesforce_account limit 1;

Expected behavior
One row or zero rows of data should be returned from the salesforce_account table.

Additional context
NA

Remove 'salesforce_' prefix from Table/Object names

Is your feature request related to a problem? Please describe.
When querying, having to type out in full 'salesforce_' prefix before any Salesforce object name is cumbersome, time consming, and prone to errors. For example, instead of typing

select * from my_custom_object__c

the plugin expects salesforce_my_custom_object__c which is an extra 11 characters.

Describe the solution you'd like
Remove the pre-pending of the string 'salesforce_' to object names in the plugin.go file

Describe alternatives you've considered
The alternative is to type 'salesforce_' each time a table is referenced in a query.

Additional context
It's unclear why the salesforce_ string is pre-pended, but I was able to remove this in the plugin code and it worked beautifully.

Failed to populate column 'lm_time__c' in the salesforce_contact table

Describe the bug
failed to populate column 'lm_time__c': invalid character '7' after top-level value