Easy, just add a join.
Original query:
SELECT cs.Name0 AS 'ComputerName', DATEDIFF(HOUR, os.LastBootUpTime0,
ws.LastHWScan) AS 'Uptime (in Hours)', CONVERT(VARCHAR(26), os.LastBootUpTime0,
100) AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), ws.LastHWScan, 101) AS 'Last Hardware
Inventory'
FROM dbo.v_GS_WORKSTATION_STATUS ws LEFT OUTER JOIN
dbo.v_GS_Operating_System os ON ws.ResourceID = os.ResourceID LEFT
OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM cs ON cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
ORDER BY os.LastBootUpTime0 ASC
Total rows: 17560
New query:
SELECT cs.Name0 AS 'ComputerName', DATEDIFF(HOUR, os.LastBootUpTime0,
ws.LastHWScan) AS 'Uptime (in Hours)', CONVERT(VARCHAR(26), os.LastBootUpTime0,
100) AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), ws.LastHWScan, 101) AS 'Last Hardware
Inventory'
FROM dbo.v_R_System_Valid sys join
dbo.v_GS_WORKSTATION_STATUS ws on sys.ResourceID = ws.ResourceID LEFT
OUTER JOIN
dbo.v_GS_Operating_System os ON ws.ResourceID = os.ResourceID LEFT OUTER
JOIN
dbo.v_GS_COMPUTER_SYSTEM cs ON cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
ORDER BY os.LastBootUpTime0 ASC
Total rows: 10521
Daniel Ratliff
From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On
Behalf Of Brian McDonald
Sent: Friday, October 07, 2016 2:17 PM
To: mssms@lists.myitforum.com
Subject: [mssms] Re: Uptime and Last Reboot for Servers
Daniel,
Can you tell me how I would go about modifying the query to use the v_r_system
instead?
Thanks,
Brian
________________________________
From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>
<listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>> on
behalf of Daniel Ratliff <dratl...@humana.com<mailto:dratl...@humana.com>>
Sent: Friday, October 7, 2016 12:40:03 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] RE: Uptime and Last Reboot for Servers
No, I would not consider that query to be accurate. Its only querying hardware
inventory, which in most environments is kept around for 30+ days. This means
you still get inactive and obsolete clients. You need to tie the query to
v_r_system_valid if you want to see legitimate clients.
Daniel Ratliff
From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>
[mailto:listsad...@lists.myitforum.com] On Behalf Of Brian McDonald
Sent: Friday, October 07, 2016 1:30 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Re: Uptime and Last Reboot for Servers
Yeah, I got it from here: http://www.scomgod.com/?p=652
SCCM Query: Uptime and Last Reboot Time | SCOM
GOD<http://www.scomgod.com/?p=652>
www.scomgod.com<http://www.scomgod.com>
This site is a collection of tools and tips that I needed to place in the
cloud. I have given credit where credit is due and respect all the hard work of
those in the ...
I'm curious if the number of servers on this report should be the same number
as servers found on other server related reports I run (e.g. patch reports)? It
always seems like there is a discrepancy.
Brian
________________________________
From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>
<listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>> on
behalf of Marcum, John <jmar...@bradley.com<mailto:jmar...@bradley.com>>
Sent: Friday, October 7, 2016 11:20:34 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] RE: Uptime and Last Reboot for Servers
All the quotes are messed up. Remove them and type them back in. Did you get
this off the internet somewhere?
From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>
[mailto:listsad...@lists.myitforum.com] On Behalf Of Brian McDonald
Sent: Friday, October 7, 2016 11:11 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Re: Uptime and Last Reboot for Servers
[External Email]
I'm aware I was missing the 'S' in Select in the below. Here is the FULL query
i'm running. [?]
SELECT
cs.Name0 AS 'ComputerName',
DATEDIFF(HOUR, os.LastBootUpTime0, ws.LastHWScan)
AS 'Uptime (in Hours)',
CONVERT(VARCHAR(26), os.LastBootUpTime0, 100)
AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), ws.LastHWScan, 101)
AS 'Last Hardware Inventory'
FROM
dbo.v_GS_WORKSTATION_STATUS ws
LEFT OUTER JOIN dbo.v_GS_Operating_System os
ON ws.ResourceID = os.ResourceID
LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM cs
ON cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
ORDER BY os.LastBootUpTime0 ASC
________________________________
From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>
<listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>> on
behalf of Brian McDonald
<mcdonald...@hotmail.com<mailto:mcdonald...@hotmail.com>>
Sent: Friday, October 7, 2016 10:55:41 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Uptime and Last Reboot for Servers
Hello everyone,
I'm trying to determine the uptime and last reboot for servers. Below is the
query I am trying to execute:
ELECT
cs.Name0 AS 'ComputerName',
DATEDIFF(HOUR, os.LastBootUpTime0, ws.LastHWScan)
AS 'Uptime (in Hours)',
CONVERT(VARCHAR(26), os.LastBootUpTime0, 100)
AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), ws.LastHWScan, 101)
AS 'Last Hardware Inventory'
FROM
dbo.v_GS_WORKSTATION_STATUS ws
LEFT OUTER JOIN dbo.v_GS_Operating_System os
ON ws.ResourceID = os.ResourceID
LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM cs
ON cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
ORDER BY os.LastBootUpTime0 ASC
I'm getting a syntax error,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '''.
Can someone tell me what I'm doing wrong?
Thank you,
Brian
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this
material/information in error,
please contact the sender and delete or destroy the material/information.
