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 su.name "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 su.name like '%' -----Machine Name's 

    and st.machine_id = su.machine_id 

    and st.product = su.sname 

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

    order by su.name,  su.sname

     


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

    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 inv_machine.name as 'Machine Name', all_patch.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 = inv_machine.id
    and all_patch.os_id = inv_os.id
    and all_patch.machine_id = inv_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 inv_machine.name 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.