Report Subscription for Hardware Inventory completion

[xerxes2985]

I am trying to create an email notification that will report all the systems that completed their hardware inventory (which occurs daily) and those that haven't, and send an email notification with the status.

Is this possible?

 

Thanks

[skissinger]

So you want a report with every single device, in a list, and which ones have and have not done hardware inventory?  I'm assuming you just might care about extenuating circumstances... what if the client is offline (it's 2 weeks of vacation, and the human powered it down); do you care that it's offline?  I'm assuming you don't want to chase after ghosts.  Have you already looked at all the wonderful things in v_ch_clientSummary view?  Anyway, making a bunch of assumptions about I think you want, and what I would want if I were  you and also assuming you only have a few hundred or thousand boxes to care about...  Below is a query.  where I 'think' what a human would want to follow up on would be devices which haven't had Hinv in the last 24 hours... BUT HAVE had 'some other kind' of activity (heartbeat, policy request); so the ones at the top of the list would be the ones to follow up on, if needed.  The 2s and 3s would be older hinv... but also older activity, so probably not on the network anyway.  or they are already current hinv, so are good (under your parameters).  So you'd use this is a base for an SSRS report.  and then set a subscription using your already working and tested SSRS report Subscription feature to email the report on whatever schedule you like.

;with cte as (select s1.Netbios_Name0 as 'ComputerName',

ws.LastHWScan,

Case

when ws.LasthwScan <= DATEADD(hour,-24, GetDate())

and cs.LastActiveTime BETWEEN DATEADD(hour,-24, GetDate()) and DATEADD(Day,3,GetDate())

then '1 older HINV, but has checked in lately'

when ws.LasthwScan <= DATEADD(hour,-24, GetDate())

and cs.LastActiveTime <= DATEADD(hour,-24, GetDate())

then '2 older and has not checked in lately'

when  ws.LasthwScan BETWEEN DATEADD(hour,-24, GetDate()) and DATEADD(Day,3,GetDate())

then '3 HINV Current'

Else 'Dunno' end as 'Age'

,cs.LastActiveTime

from v_r_system s1

join v_GS_WORKSTATION_STATUS ws on ws.resourceid=s1.resourceid

join v_CH_ClientSummary cs on cs.resourceid=s1.resourceid

)

select cte.computername, cte.age, cte.LastHWScan, cte.LastActiveTime

from cte

order by cte.Age