Report Centre - Custom Queries

Version 4
    Share This:

    1. Software Title usage details of all Applications along with its Install date.


    MachineName   ScanTime   ApplicationName   ApplicationVersion   InstallDate   LastUsed


    select "Machine Name", su.scantime "Scan Time",su.sname "Application Name", su.version "Application Version", st.install_date "Installed Date", max(su.end_time) "Last Used" 

    from inv_software_title_usage su, inv_software_title st 

    where like '%' -----Machine Name's 

    and st.machine_id = su.machine_id 

    and st.product = su.sname 

    group by, su.version,  st.install_date, su.scantime, su.sname 

    order by,  su.sname


         For better information about the Microsoft related products, I suggest using the custom scanner (License Key Scanner) which is available at

    Try for a single machine and then a small subset. If satisfied go for a larger group.

    Also note that not all the install_date is picked up by scan(some apps may have NULL value.)

    2. Ways to know OS architecture (32 or 64 bits) in Report Center

    select distinct a.[name] machine_name, a.[scantime], o.[product], o.[kernel_bit_size] as 'Kernel Bit', i.[releaseversion] 
    from inv_machine a
    inner join inv_os o on o.[machine_id]=a.[id]
    inner join inv_tuner i on i.[machine_id]=a.[id]
    where o.[kernel_bit_size] = '64' or
    a.[id] in ( select distinct a.[id] from inv_machine a
    inner join inv_os o on o.[machine_id]=a.[id]
    where o.[product] = 'Microsoft Windows XP Professional x64 Edition' )
    order by a.[name]


    3. Patch Compliant Report (Installed Patches)

    select distinct as 'Machine Name', as 'Bulletin ID', all_patch.repository_id as 'Repository ID', all_patch.state_timestamp as 'Installed Date'
    from inv_os, all_patch, inv_machine
    where upper(all_patch."vendor severity") = 'CRITICAL'
    and inv_os.machine_id =
    and all_patch.os_id =
    and all_patch.machine_id =
    and upper(all_patch.current_status) = 'INSTALLED'
    and CAST(all_patch.state_timestamp - 0.50000004 AS INT) > (CAST(GETDATE()- 0.50000004 AS INT )-15)
    --and like '%MachineName%'


    The installed date may not be 100% accurate as the 'state_timestamp' column may have NULL values.

    This will yield more records and may result in deadlock. So i suggest you to use the last comment statement to check for few machines. If the result-set is what you desired, apply the logic to a larger subset.