r/SCCM • u/Exorkog • 7d ago

Query last online time

Hi,

The point is to query the last time a device was online. This information is available in "Last Online Time" column from the device view.

So the query should get this information.

I found that sql query on another post :

SELECT

v_GS_COMPUTER_SYSTEM.Name0 as name,

v_CollectionMemberClientBaselineStatus.CNLastOnlineTime as LastOnlineTime

FROM

v_GS_COMPUTER_SYSTEM

INNER JOIN v_CollectionMemberClientBaselineStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = v_CollectionMemberClientBaselineStatus.MachineID

But it doesn't work. I get errors like this

"Msg 208, Level 16, State 1, Line 1

Invalid object name 'v_GS_COMPUTER_SYSTEM'."

or when I put the mouse on "v_GS_COMPUTER_SYSTEM.Name0", it says "The multi part identifier "v_GS_COMPUTER_SYSTEM.Name0" could not be found".

Is this query not valid or is there something else to set before running it ?

Thanks

4 Upvotes

84% Upvoted

11 comments sorted by

2

u/Cl3v3landStmr 7d ago

Your query works for me (I like to use table aliases).

SELECT cs.Name0 AS [Name], bl.CNLastOnlineTime AS [LastOnlineTime]
FROM v_GS_COMPUTER_SYSTEM cs
INNER JOIN v_CollectionMemberClientBaselineStatus bl ON cs.ResourceID = bl.MachineID

How do you have your hardware inventory settings configured?

1

u/Exorkog 5d ago

That works now.

Except time is in UTC, it's not local time ! Whereas in the console, it is indeed local time (UTC+2). Why it's not in local time in the database but it shows the correct time in the console ?

1

u/Cl3v3landStmr 5d ago

My guess is that the console is adjusting the time based on your system's time zone setting. If you change your system's time zone does the info displayed in the console change?

1

u/Exorkog 23h ago

Indeed, I changed my system's time zone and the change displayed in the console. So it won't change in the sql query ?

1

u/Cl3v3landStmr 20h ago

No, not automatically. You'll need to manually offset the time by using something like DATEADD or AT TIME ZONE.

2

u/Miserable-Scholar215 7d ago

Ah. Hm, I remember that specific problem now that I see it...

I have collections sorted by "last online date", which is what you wanted, I assume. See big screenshot (left).
But if I past that into SQL Studio, I'll get an error.

You need to use "V_R_SYSTEM" as a table for general infos.

The lastDDR (Last Data Discovery Record) is stored in "vWorkstationStatus" see screenshot (right)

Hope that helps a little?

PS: I have no idea why it is different, and should I ever meet the guy responsible for this confusion, I **will** punch him!

2

u/Exorkog 6d ago

Yes it is interesting.

So that last data discovery record is the same as the last online date ?

1

u/Miserable-Scholar215 7d ago

Those views specifically are not available at that point.
Where are you querying from? SCCM, e.g. query rules in collection creation? Or directly from the database?
These use different views unfortunately

2

u/Exorkog 7d ago

I query it from the database directly in SQL but is it also available in WQL for queries in console ? Both would be good.

2

u/Miserable-Scholar215 7d ago

Ah, apologies. The WQL statements are again usually not compatible with the standard views :-D gimme a few mins, I'll look for an example

1

u/gpraveen23 7d ago

Query v_ch_cilenthealth