Skip to content

Instantly share code, notes, and snippets.

View DarkAllien's full-sized avatar

Octavian Cordos DarkAllien

View GitHub Profile
@DarkAllien
DarkAllien / software updates - to be applied.sql
Last active March 4, 2016 17:52
query - software updates - to be applied
declare @AuthListLocalID as int
select @AuthListLocalID=CI_ID from v_AuthListInfo
where CI_UniqueID=@AuthListID
SELECT distinct
ui.BulletinID,
ui.ArticleID,
ui.Title,
ui.Description,
ui.DateRevised,
CASE ui.Severity
/*
declare @CollID varchar(8) = 'Collection ID for Testing'
DECLARE @UserSIDs VARCHAR(16);
SELECT @UserSIDs = 'disabled';
*/
declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID('English')
--Getting Deployments IDs
select
select CI_ID,title
FROM
v_AuthListInfo
--Naming Filtering if you want it
where
title like '%required%'
and title not like '%office%'
--and title not like '%pilot%'
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_">
<DataSourceReference>/ConfigMgr_VSM/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>11111111-1111-1111-1111-111111111111</rd:DataSourceID>
</DataSource>
</DataSources>
USE [new database on SCCM DB Server];
GO
IF OBJECT_ID('[REP].[spSU_States_Update_Groups_per_Collection]', 'P') IS NOT NULL
DROP PROCEDURE [REP].[spSU_States_Update_Groups_per_Collection];
GO
CREATE PROCEDURE [REP].[spSU_States_Update_Groups_per_Collection] @pCollID VARCHAR(8),
@pSUGS VARCHAR(4000)
AS
BEGIN
USE [new database on SCCM DB Server];
GO
IF EXISTS
(
SELECT *
FROM sys.synonyms
WHERE name = N'v_R_System'
AND schema_id = SCHEMA_ID(N'dbo')
)
SELECT *
FROM [v_R_System] [s]
WHERE [s].[ResourceID] IN
(
SELECT [c].[ResourceID]
FROM [v_FullCollectionMembership] [c] WITH (nolock)
WHERE [c].[CollectionID] = @CollID
);
SELECT *
FROM [v_R_System] [s]
JOIN
(
SELECT [c1].[ResourceID]
FROM [v_FullCollectionMembership] [c1] WITH (nolock)
WHERE [c1].[CollectionID] = @CollID
) [c] ON [c].[ResourceID] = [s].[ResourceID];
$lastData=(get-date).AddYears(-5)
$myFile = 'your_chosen_location'
$lastData = import-csv $myFile| select -ExpandProperty Timestamp -Last 1
$data=Invoke-Sqlcmd -Query "select * from [SCCM_DATABASE(ex: CM_XXX)].dbo.v_AM_NormalizedDetectionHistory scep
where scep.timestamp > '${lastData}'
order by scep.Timestamp asc" -ServerInstance "SCCM_SQL_Server_FQDN"
define ROOT C:\Program Files (x86)\nxlog
Moduledir %ROOT%\modules
CacheDir %ROOT%\data
Pidfile %ROOT%\data\nxlog.pid
SpoolDir %ROOT%\data
LogFile %ROOT%\data\nxlog.log
<Extension gelf>
Module xm_gelf