SOLVED SCCM SQL Custom Query

[sakharam patil1]

I need SCCM query to get report in below format. Please help.

 

[sakharam patil1]

I build the query. mark as resolved

 

[TG09NZ]

What was the query you used?

 

[sakharam patil1]

i was looking for the query to get report of "SoD.exe" file path, version and Modified date.

 

[sakharam patil1]

select computer_system_data.name00, computer_system_data.username00, SoftwareFile.filename, SoftwareFile.FileVersion, softwarefilepath.filepath
FROM computer_system_data, SoftwareFile, softwareinventory, workstationstatus_data, softwarefilepath
WHERE computer_system_data.machineId = softwareinventory.clientid
and softwareinventory.fileid = softwarefile.fileid
and softwareinventory.filepathid = softwarefilepath.filepathid
and SoftwareFile.filename = 'SoD.exe'

 

[Garth]

It is not supported to query the tables directly, always use the SQL view. Also ensure that you do you joins correctly.

select
    RV.Netbios_Name0,
    RV.User_Name0,
    SF.filename,
    SF.FileVersion,
    SF.FilePath
FROM
    dbo.v_R_System_Valid RV
    inner join dbo.v_GS_SoftwareFile SF on RV.ResourceID = SF.ResourceID
WHERE
    SF.FileName = 'SoD.exe'