Sample Queries

Delete All Records from Database
Change a Digit or Value within a Field
Change a Value within a Field Based On Two Other Fields
Change a Value within a Field Based On Another Field

Change Category within a Relationship Type
Change Relationship Type ID Based on Entity ID
Change Topic within a Specific Entity
Count the Number of Entities and Relationships

Find a Specific Entity's Record Based on its ID Number
Find a Relationship By UUID within the Relationship Table
Simple Data Import

Instructions on how to execute a Query in SQL Server Management Studio, visit our webpage Execute a Query.

Delete All Records from Database

This query does not delete defined values in the Manage Database section of Sentinel Visualizer. In other words, it does not delete the customized Knowledge Database definitions like Entity Types, Relationship Types, and Metadata Types. It deletes the data records, themselves.

This query is useful if you’ve defined many Entity Types, Relationship Types, and Metadata Types, then realize that you entered or imported erroneous data, but want to start over with an “empty” database to avoid re-entering all of the Entity, Relationship, and Metadata Types that you defined in “Manage Database.” Running this query deletes all your records in these tables but keeps all of the “Manage Database” entries intact.

DELETE FROM Entity
DELETE FROM Binaries
DELETE FROM MRU
DELETE FROM SavedDiagram

Change a Digit or Value within a Field

This changes a “5” anywhere in the Entity Metadata field to a “1.” In this example, the User added new Metadata fields (in Sentinel Visualizer’s Manage Database function) and wanted to change one digit (5) to a “1.” There are many uses for this type of Query.

UPDATE EntityMetadata
SET MetadataValue = '1'
WHERE MetadataValue like '%5%'

This Query is similar to the one above:

UPDATE Entity
SET MiscValue = '0'
WHERE MiscValue = '90'

Change a Value within a Field Based On Two Other Fields

UPDATE Relationship
SET InherentThreatValue = '100'
WHERE RelationshipId = '1537' and EntityID = '1122'

Change a Value within a Field Based On Another Field

UPDATE Relationship
SET EntityID = '1122'
WHERE UUID = '49c4d93cdbd14e1fb75cf5ca389bfb03'

Change Category within a Relationship Type

UPDATE LookupRelationshipType
SET RelationshipCategoryID='1008'
WHERE RelationshipCategoryID='1000'

Change Relationship Type ID Based On Entity ID

UPDATE Relationship
SET RelationshipTypeId = '1171'
WHERE EntityId = '1492'

Change Topic within a Specific Entity

UPDATE dbo.EntityTopic
SET topicID='1000'
WHERE EntityID=2457

Count the Number of Entities and Relationships

SELECT count(EntityId) from Entity
SELECT count(RelationshipId) from Relationship

Find a Specific Entity's Record Based on its ID Number

SELECT *
FROM [databasename].[dbo].[Entity]
WHERE EntityTypeID = '1035'

Find a Relationship By UUID within the Relationship Table

SELECT *
FROM [databasename].[dbo].[Relationship]
WHERE UUID = '49c4d93cdbd14e1fb75cf5ca389bfb03'
GO

Simple Data Import

The following query should be used with extreme caution and only by a SQL programmer experienced with similar tasks. This query imports data directly into the Sentinel Visualizer database, bypassing the Data Import tool that appears on Sentinel Visualizer’s Home page.

DECLARE @entity1 int;
DECLARE @entity2 int;
DECLARE @relationship int;

SELECT @entity1 = EntityId FROM Entity 
  WHERE EntityName = 'Entity 1'
IF @entity1 IS NULL
BEGIN
	INSERT [dbo].[Entity] ([ClassificationLevelId], [EntityTypeId], [EntityStatusId], [DisseminationTypeId], [InherentThreatValue], [EntityName], [PrimaryPicture], [Brief], [StartDate], [EndDate], [UUID], [Latitude], [Longitude], [CustomStartDateId], [CustomEndDateId], [StartTimeNull], [EndTimeNull], [CustomStartYear], [CustomEndYear], [MiscValue], [CreatedByUserId], [ModifiedByUserId], [CreatedDate], [ModifiedDate], [BriefText])
	VALUES (100, 1000, 1000, 1000, 50, N'Entity 1', NULL, NULL, NULL, NULL, N'', NULL, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 1000, NULL, CAST(N'2020-10-06T18:20:49.2733333' AS DateTime2), NULL, NULL)
	--store the id of the inserted entity
	SELECT @entity1 = SCOPE_IDENTITY()
	PRINT @entity1

	INSERT [dbo].[EntityTopic] ([EntityID], [TopicID], [CreatedDate], [CreatedByUserId], [ModifiedDate], [ModifiedByUserId]) 
	VALUES (@entity1, 1000, CAST(N'2020-10-06T18:20:49.4333333' AS DateTime2), 1000, NULL, NULL)
END

SELECT @entity2 = EntityId FROM Entity 
  WHERE EntityName = 'Entity 2'
IF @entity2 IS NULL
BEGIN
	INSERT [dbo].[Entity] ([ClassificationLevelId], [EntityTypeId], [EntityStatusId], [DisseminationTypeId], [InherentThreatValue], [EntityName], [PrimaryPicture], [Brief], [StartDate], [EndDate], [UUID], [Latitude], [Longitude], [CustomStartDateId], [CustomEndDateId], [StartTimeNull], [EndTimeNull], [CustomStartYear], [CustomEndYear], [MiscValue], [CreatedByUserId], [ModifiedByUserId], [CreatedDate], [ModifiedDate], [BriefText]) 
	VALUES (100, 1000, 1000, 1000, 50, N'Entity 2', NULL, NULL, NULL, NULL, N'', NULL, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 1000, NULL, CAST(N'2020-10-06T18:20:49.3933333' AS DateTime2), NULL, NULL)
	SELECT @entity2 = SCOPE_IDENTITY()
	PRINT @entity2

	INSERT [dbo].[EntityTopic] ([EntityID], [TopicID], [CreatedDate], [CreatedByUserId], [ModifiedDate], [ModifiedByUserId]) 
	VALUES (@entity2, 1000, CAST(N'2020-10-06T18:20:49.4366667' AS DateTime2), 1000, NULL, NULL)
END

SELECT @relationship = RelationshipId FROM Relationship 
  WHERE [EntityId] = @entity1 AND [RelatedEntityId] = @entity2
IF @relationship IS NULL
BEGIN
	INSERT [dbo].[Relationship] ([EntityId], [RelatedEntityId], [RelationshipTypeId], [ClassificationLevelId], [DisseminationTypeId], [StartDate], [EndDate], [ReliabilityOfSourceId], [CredibilityOfInformationId], [Notes], [UUID], [InherentThreatValue], [CustomStartDateId], [CustomEndDateId], [StartTimeNull], [EndTimeNull], [CustomStartYear], [CustomEndYear], [MiscValue], [CreatedByUserId], [ModifiedByUserId], [CreatedDate], [ModifiedDate], [NotesText]) 
	VALUES (@entity1, @entity2, 1069, 100, 1000, NULL, NULL, 1000, 1, NULL, NULL, 50, 0, 0, NULL, NULL, NULL, NULL, NULL, 1000, NULL, CAST(N'2020-10-06T18:20:49.4933333' AS DateTime2), NULL, NULL)
	SELECT @relationship = SCOPE_IDENTITY()
	PRINT @relationship

	INSERT [dbo].[RelationshipTopic] ([RelationshipID], [TopicID], [CreatedDate], [CreatedByUserId], [ModifiedDate], [ModifiedByUserId]) 
	VALUES (@relationship, 1000, CAST(N'2020-10-06T18:20:49.5166667' AS DateTime2), 1000, NULL, NULL)
END

GO

Sample Queries for SQL Server Management Studio