SG-SCCM SAMP Usage - SCCM Database Query Getting Failed with com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'NULL'. - Support and Troubleshooting

SG-SCCM SAMP Usage - SCCM Database Query Getting Failed with com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'NULL'. Issue When executing the SG-SCCM data import, the following imports failed.

Steps to Reproduce: 1. Go to Service Graph Connector Microsoft SCCM->Data Sources

2. Select 'SG-SCCM SAMP Usage'

3. Wait for the MID Server to respond

The following error is observed:

MID Server reported error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'NULL'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursored(SQLServerStatement.java:2021) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:844) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:693) at com.service_now.monitor.jdbc.JDBCRowSet.query(JDBCRowSet.java:64) at com.service_now.mid.probe.JDBCProbe.doSelect(JDBCProbe.java:353) at com.service_now.mid.probe.JDBCProbe.doQuery(JDBCProbe.java:224) at com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:148) at com.service_now.mid.probe.AProbe.process(AProbe.java:106) at com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:129) at com.service_now.mid.queue_worker.AWorkerThread.run(AWorkerThread.java:20) at com.service_now.mid.threadpool.ResourceUserQueue$RunnableProxy.run(ResourceUserQueue.java:649) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834)

Cause There are 2 basic types of software usage that can be considered for reclamation with SCCM:

1. Last Used Date 2. Total Usage Time

SG-SCCM handles these in 2 different data sources/scheduled imports:

1. SG-SCCM Software Last Used: for reclamation rules with type "Last Used Date" 2. SG-SCCM SAMP Usage: for reclamation rules with type "Total Usage Time"

SCCM (the external tool) stores usage information associated with file executables a.k.a. product processes. SCCM has a lot of information there, and we only want to pull data for specific executables that are associated with Software Products that have configured Reclamation Rules. Otherwise, we would waste a huge amount of time pulling usage data into the CMDB for no purpose.

So SG-SCCM uses the scheduled import scripting capability to dynamically evaluate the configured Reclamation Rules, and for each type, get the list of product processes (file executable names) associated with the software product. This data is stored in samp_sw_product_process, and is typically populated/updated on a weekly basis by CDS content service.

By default, the SG-SCCM SAMP Usage has no file executables that it queries for. That is why the default SQL statement has the clause "WHERE FileName IN NULL". The scheduled import script should update this at run time.

Note that "SG-SCCM Software Last Used data" source does the same type of script process to construct the SQL for "Last Used Date" reclamation type rules. And in the case of this data source, you can see from the SQL statement expected values.

and so on, for all of the identified file executables associated with those applications. This is the type of SQL update we would expect to see in the SG-SCCM SAMP Usage data source if additional/different reclamation rules are added.

Resolution To fix this, it is suggested to set up reclamation rules for Software Products where there are product process records in samp_sw_product_process. I believe this will work as expected if this is done.

For SG-SCCM to process usage, you have to create Reclamation Rules for Software Products that actually have product processes in samp_sw_product_process. If it does not have any process records in samp_sw_product_process. So the SQL redefinition step fails to create a useful SQL statement and you get the error reported.