ServiceNow · SQL

Query ServiceNow Data with SQL Part 2

In the first blog we just queried incidents and counted them by group.  Something pretty simple. 

Now I am going to query projects, select the assignment group first, then display it all in an SSRS report.  But I am going to include the the latest work notes.  I am going to use SQL reporting to design the the report the way I want it.  Creating a dashboard of sorts listing the projects and their latest status and the date they were last updated.

There were some problems I ran in to when I attempted to do this.  Open query is not really easy when it comes figuring out why a query failed, so it took some time to get the results I wanted.  If you put an open query in SSRS it does not give you the field names.  When you run a regular select statement in SSRS it will auto create all the field names.  Drag and drop on the report, nice and easy.  So this was one issue.

The issue I ran in to before that was getting the work notes.  The work notes all exist in a table called sys_journal_field.  I did not keep track of all the different methods I used to try to get the latest record, but none of them were working.  Again, open query and not knowing exactly what the error is.  In the end I was able to bring back only the latest record based on number and last updated on.  I did this by adding row number in the initial query.  Then when I queried the temp table I selected the max(rownumber).

Lets jump to the code.

First thing we need to do is create a temp table.

declare @temptable1 table (
[Project Name] varchar(500),
 [Project Lead] varchar(100), 
 [Description] varchar(500),
 [LatestUpdate] varchar(1000),
 [Percent Complete] varchar(50)
,[start date] datetime,
 [end date] datetime
,[priority] varchar(8) 
,[sys_id] varchar (50)
,[Assignment Group] varchar(40)
, [Secondary Group] varchar(40) 
, [Sys_updated_on] datetime
, [value] varchar(500)
,[Number] varchar(500)
,[RowNumber] varchar(10)
);
 DECLARE @TSQL1 varchar(max)

Include all the fields that you will want to use in your report. 

Next we will query the data.  In the pervious blog post I said you need to use double ticks ‘’, an example would be dv_assignment_group as ”Assisgnment Group”.  With temp tables we need to use 4 ticks. 

 DECLARE @TSQL1 varchar(max)

SELECT  @TSQL1 = 'SELECT * FROM OPENQUERY (TESTSERVICE ,''select 
pm_project.name as ''''Project Name'''', 
pm_project.dv_project_lead as ''''Project Lead''''
,task.short_description as ''''Description'''',

 pm_project.u_latest_update as ''''LatestUpdate'''',
CASE
	WHEN   pm_project.percent_complete = null THEN ''''0''''
		ELSE  floor(pm_project.percent_complete)
			END as ''''Percent Complete''''
,pm_project.planned_Start_date as ''''Start Date''''
,pm_project.planned_End_date as ''''End Date''''
,case 
	when task.priority = ''''1'''' then ''''Critical''''
		when task.priority = ''''2'''' then ''''High''''
			when task.priority = ''''3'''' then ''''Standard''''
				end as ''''priority''''
,task.sys_id
,task.dv_assignment_group as ''''Assignment Group''''
,pm_project.u_secondary_group as ''''Secondary Group''''
,sys_journal_field.Sys_updated_on
,sys_journal_field.value
,task.Number
,ROW_NUMBER() OVER(PARTITION BY task.number ORDER BY sys_journal_field.Sys_updated_on) AS RowNumber
from pm_project join task  on task.number = pm_project.number
join sys_journal_field on task.sys_id = sys_journal_field .element_id
where task.dv_state = ''''Open''''
order by sys_journal_field.Sys_updated_on asc
 '')' --GO
 insert @temptable1 EXEC (@TSQL1)

Now we need to query the temptable.  When we query the temp table, SQL SSRS will recognize the fields. 

-- select * from @temptable1
select ab.[Project Name],ab.[Project Lead], convert(varchar(20),
Sys_updated_on,100) as NotesDate,value as Notes, ab.Number
,ab.[Percent Complete], ab.[start date] as start_date,
ab.[end date] as End_Date,ab.[priority], ab.sys_id,ab.[Assignment Group],
ab.[Secondary Group],ab.Description  from @temptable1 as ab
join (select number, max(rownumber) as number2 from @temptable1
group by number) as drv
on ab.number = drv.number and rownumber = drv.number2

where ab.[Assignment Group] in (@Group)

group by ab.[Project Name],ab.[Project Lead], Sys_updated_on,value, ab.number,ab.[Percent Complete], 
ab.[start date],ab.[end date],ab.[priority], ab.sys_id,ab.[Assignment Group], ab.[Secondary Group],ab.Description

The last thing I did for the SSRS report is add a drop down to choose the assignment group.  This way I can get all the groups or only specific ones.  As a manager I might want to only see what’s going on with my team.

declare @temptable2 table (
[Assignment Group] varchar(40)
);
 DECLARE @TSQL2 varchar(max)

SELECT  @TSQL2 = 'SELECT * FROM OPENQUERY (TESTSERVICE ,''
select task.dv_assignment_group as ''''Assignment Group''''
from pm_project join task 
 '')' --GO

 insert @temptable2 EXEC (@TSQL2)

-- select * from @temptable2
select distinct ab.[Assignment Group]  from @temptable2 as ab

Please let me know if there is a better way to do this.  I know its not super clean.  It was fun learning how to get the data (sometimes). 

-TG

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s