Last 10 run of a script

Question

Post reply

Last 10 run of a script

davrose

Right there with Babe

Points: 728
More actions
November 2, 2010 at 8:01 am

#228703

Hi All,
i have a script that runs on a set of servers and, based on the result, writes an entry in a table.
This is the table
CREATE TABLE [dbo].[ScriptHistory](
[CreateDate] [datetime] NULL,
[HostName] [nvarchar](50) NULL,
[HostIP] [nvarchar](15) NULL,
[Step01Result] [nvarchar](255) NULL,
[Step02Result] [nvarchar](255) NULL
)
CreateDate is the datetime given by the script (a sort of a getdate() at the very beginning of the execution)
Now, I'd like to have a list of the last 10 run for each ip.
i.e
given this
1/1/2010 00:00:00, 'serverA', '1.1.1.1', 'OK', 'ERR'
1/1/2010 00:00:00, 'serverB', '1.1.1.2', 'OK', 'OK'
1/1/2010 00:00:00, 'serverC', '1.1.1.3', 'OK', 'ERR'
1/1/2010 00:00:00, 'serverD', '1.1.1.4', 'OK', 'OK'
1/1/2010 01:00:00, 'serverE', '1.1.1.5', 'OK', 'OK'
1/1/2010 02:00:00, 'serverC', '1.1.1.3', 'ERR', 'ERR'
1/1/2010 02:00:00, 'serverD', '1.1.1.4', 'OK', 'OK'
1/1/2010 03:00:00, 'serverA', '1.1.1.1', 'OK', 'OK'
1/1/2010 04:00:00, 'serverC', '1.1.1.3', 'OK', 'OK'
1/1/2010 05:00:00, 'serverC', '1.1.1.3', 'OK', 'ERR'
my goal is a table like this
ServerName, IP, 10th, 9th, 8th, 7th .....
'serverA', '1.1.1.1', 'OK', 'ERR', null...
'serverB', '1.1.1.2', 'OK', null, null...
'serverc', '1.1.1.3', 'ERR', 'OK', 'ERR', 'ERR' ...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do:

-- TABLE DEFINITION

DECLARE @ScriptHistory TABLE (

[CreateDate] [datetime] NULL,

[HostName] [nvarchar](50) NULL,

[HostIP] [nvarchar](15) NULL,

[Step01Result] [nvarchar](255) NULL,

[Step02Result] [nvarchar](255) NULL

)

-- SAMPLE DATA

INSERT INTO @ScriptHistory

SELECT '2010-01-01 00:00:00.000', 'serverA', '1.1.1.1', 'OK', 'ERR'

UNION ALL SELECT '2010-01-01 00:00:00.000', 'serverB', '1.1.1.2', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 00:00:00.000', 'serverC', '1.1.1.3', 'OK', 'ERR'

UNION ALL SELECT '2010-01-01 00:00:00.000', 'serverD', '1.1.1.4', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 01:00:00.000', 'serverE', '1.1.1.5', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 02:00:00.000', 'serverC', '1.1.1.3', 'ERR', 'ERR'

UNION ALL SELECT '2010-01-01 02:00:00.000', 'serverD', '1.1.1.4', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 03:00:00.000', 'serverA', '1.1.1.1', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 04:00:00.000', 'serverC', '1.1.1.3', 'OK', 'OK'

UNION ALL SELECT '2010-01-01 05:00:00.000', 'serverC', '1.1.1.3', 'OK', 'ERR'

--SOME PIVOTING AND GROUPING

;WITH ServerResults (HostName, HostIP, [10], [9], [8], [7], [6], [5], [4], [3], [2], [1])

AS (

SELECT HostName , HostIP, MIN([10]), MIN([9]), MIN([8]), MIN([7]), MIN([6]), MIN([5]), MIN([4]), MIN([3]), MIN([2]), MIN([1])

FROM (

SELECT *,

Result = CASE WHEN Step01Result = 'OK' AND Step02Result = 'OK' THEN 'OK' ELSE 'ERR' END,

RN = 11 - ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY CreateDate DESC)

FROM @ScriptHistory

) AS SRC

PIVOT (MIN(Result) FOR RN IN ([10], [9], [8], [7], [6], [5], [4], [3], [2], [1])) AS PVT

GROUP BY HostName, HostIp

)

SELECT *

FROM ServerResults

-- Gianluca Sartori

davrose Right there with Babe Points: 728 More actions · Answer 2

GREAT!!!! 😀

I knew it was a matter of pivoting...but couldn't figure out how to deal with the dates...

do you mind to give an explanation? I think i sort of understood how it works...but still....

davrose Right there with Babe Points: 728 More actions · Answer 3

this

RN = 11 - ROW_NUMBER() OVER(PARTITION BY HostIp ORDER BY CreateDate DESC)

creates a ranked list based on the ip

then, all you needed to do is pivot the table!!

GREAT!!

THANKS 😉

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Sorry for being late at explaining.

I see you figured it out.

Have fun!

-- Gianluca Sartori