Adding last HW inv update field to existing report

[amandaw33]

I have a report that shows me all admin users on all of my machines, sql below.  Works great.

However, I need a way to see how outdated this info could be since user info is only updated when hardware inventory updates.

How can I run a report that shows me admin users on machines, but also show me the date this information was last updated (I'd guess last time HW inventory update ran)?

Bonus points if report could filter out the local Administrator user that exists on every machine and also group 'domain admins' which exists on every machine.  For now I just ignore those entries.

Thanks reporting gurus!

Edit: every time I try to add the sql code I can't save the post - going to try to add it as a txt attachment :(

 

Attachment(s)

sql.txt   684 B 1 version

[Kevin Huntley Protirus]

Hi Sally5432,

 

I've copied in a query below that should get the latest check in date for the agent and exclude those pesky domain admins and local administrators.

The table I've joined is the INV_AEX_AC_Identification table, this effectively collates information about the machine, like OS, Timezone and last check in.

 

Let me know what results you get back.

 

Hope this helps,

 

Kev

Attachment(s)

SQL for Last check In_0.txt   939 B 1 version

[amandaw33]

thanks so much for the quick response, Kevin!

[Igor Perevozchikov]

Hi Sally5432,

try this SQL Query to see column with date of last Inventory Scan execution per each managed endpoint (attached)

This Query shows all inventory scan history for each managed endpoint therefore you will see a lot of records. You can filter out Query output and add condition in SQL Query to show machines where inventory scan was executed last:

• 1 Month ago: WHERE iir.[Collection Time] <= DATEADD (mm, -1, GETDATE ())
• 1 Week ago: WHERE iir.[Collection Time] <= DATEADD (ww, -1, GETDATE ())

Thanks,

IP.

Attachment(s)

SQL Query for Sally.txt   878 B 1 version