Uncategorized

Search Parameter in Widows and Outlook

I have the need to figure out by week what was modified in my file system.
Fortunately there’s a string to put in the search box that will give you just that:

modified:7/2/2018 .. 7/31/2018

 

Blogs/Blogging

Excel – VLOOKUP

Okay if you read this blog you’ll know I’m more of a DB geek than an Excel user but I’m getting tired of every time that I want to do a quick sub-out of data having to load not one but two files into the DB and then write the query to join the two and get what I need. It’s a great way to go but awfully time consuming if it’s not going to be built into a SSIS pack or something of the sort.
So that brings us to the old standard Excel. Actually Excel is awfully handy on the export side of things but using it to import data from a DB or even flat file.
In this case we’ve got two sheets representing two tables with a common key.

  • =VLOOKUP(F2,States!A$2:B$38,2)
  • The $ keeps the range in the middle from moving when you populate down.
  • In this case this is done in G2 where F2 is being looked up.
  • The range is on a second sheet from A2 to B38 where items in A relate to F and the B value will be returned for G.
  • Remember to have the columns in the same order to pull this off.
MSSQL

SQL – Got People Data?

So how many time did you think to yourself, wouldn’t it be nice to have a repository of names an addresses so I can load for testing? If you’re normal you’ve never asked yourself that. If you’re a computer geek it crosses your mind from time to time. Microsoft has generously given us the Adventure Works database and this is perfect for just such repository. You can find the DB on Microsoft’s site and here is a bit of SQL to get you started.

SELECT top 1000
	   replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as Ident
	  --,p2.rowguid
      --,isnull(p2.[Title], '') as Title
      ,isnull(p2.[FirstName], '') as FirstName
      ,isnull(p2.[MiddleName], '') as MiddleName
      ,isnull(p2.[LastName], '') as LastName
      --,isnull(p2.[Suffix], '') as Suffix
	  ,replace(pa.AddressLine1, ',', '-') as AddressLine1
	  ,isnull(pa.AddressLine2, '') as AddressLine2
	  ,pa.City
	  --,sp.StateProvinceCode as State
	  --,sp.CountryRegionCode as Country
	  ,pa.PostalCode
	  ,replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as EmailAddress
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 1), '') as CellPhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 2), '') as HomePhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 3), '') as WorkPhone
	  ,Lower(LEFT(p2.FirstName, 1) + p2.LastName) as Username
	  ,'taleo123' as Pswd
  FROM [Person].[Person] p2
  join Person.BusinessEntityAddress ba on (p2.BusinessEntityID = ba.BusinessEntityID)
  join Person.Address pa on (ba.AddressID = pa.AddressID)
  join Person.StateProvince sp on (sp.StateProvinceID = pa.StateProvinceID)
  join Person.EmailAddress pe on (p2.BusinessEntityID = pe.BusinessEntityID)
  join Person.PersonPhone pp on (p2.BusinessEntityID = pp.BusinessEntityID)
  where sp.CountryRegionCode = 'US'
order by LastName, FirstName
TCC

TCC – Sub IncludedIn SubQuery

I never thought that this would work but it seems to run just fine, putting it here for future reference. This returns candidates numbers that have an application to a requisition that’s posted, used for a T-XML candidate attachment export.

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:includedIn>
    <quer:field path="Number"/>
    <quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
      <quer:subQueries/>
      <quer:projections>
        <quer:projection>
          <quer:field path="Candidate,Number"/>
        </quer:projection>
      </quer:projections>
      <quer:filterings>
        <quer:filtering>
          <quer:includedIn>
            <quer:field path="Applications,Requisition,Number"/>
            <quer:query projectedClass="SourcingRequest" alias="sqMotives" preventDuplicates="true" pagingsize="1">
              <quer:subQueries/>
              <quer:projections>
                <quer:projection>
                  <quer:field path="Requisition,Number"/>
                </quer:projection>
              </quer:projections>
              <quer:filterings>
                <quer:filtering>
                  <quer:equal>
                    <quer:field path="SourcingRequestStatus,Number"/>
                    <quer:string>2</quer:string>
                  </quer:equal>
                </quer:filtering>
              </quer:filterings>
            </quer:query>
          </quer:includedIn>
        </quer:filtering>
      </quer:filterings>
    </quer:query>
  </quer:includedIn>
</quer:filtering>
Linux

Linux – Block SSH and FTP Access to Specific IP and Network Range in Linux

I’m not sure why but someone in China must think my Linux server contains massive secrets and sometimes even when using fail2ban it seems to be bogged down by attempts to login as root to my SSH by brute force attack which leaves me scratching my head as to if anyone really allows root access to SSH.

Anyhow, the goal is to just reject these jokers at a transport level on the firewall.

The article on how to do this is here.

The relevant commands are:

firewall-cmd --direct --add-rule ipv4 filter INPUT 1 -m tcp --source 192.168.1.100/24 -p tcp --dport 22 -j REJECT
firewall-cmd --reload
TCC

TCC – Upgrade to 17

This post is to consolidate the information about the required update of TCC to version 17.x

TCC: Does TCC Need To Be Upgraded When A Zone Is Upgraded? (Doc ID 2086226.1)
This article addresses the question of whether TCC needs to be upgraded when a zone is upgraded.

read more »

TCC

TCC – Languages and Codes

I’ve been driven crazy a few times trying to figure out which code goes to which language so here it is for future reference:
read more »

TCC

TCC – Error: ‘Timestamp format must be…’

So I was trying to do a replace null on two date fields and render it toChar yyyy-MM-dd and it kept giving me the lovely ‘SAX parsing error’, looking in the profiler I get this reason:
Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
After rearranging the order of the toChar and replaceNull and still getting the same thing, I finally stumbled on the answer which was to declare the projection as a toChar by adding:
projectedField=”RuntimeString:toChar”
To the projection attributes.
I’m not 100% sure why this has to be done but it fixed the issue so figured I’d post it here.

Linux

Linux – Open Established connections

When you need to see what/who is connected to the server use this:
Point in time:
netstat -nap | grep EST
Ongoing:
watch ‘netstat -nap | grep EST’

TCC

TCC – Looping

There are times when you’ll want to loop through exports because the amount returned is going to be greater than 100k. There is two fold process here, in this section we’ll discuss how to set up an export configuration file for looping. Remember that you have to have the TCC custom steps in order for this to work.

Pre-processing:
1st step – com.taleo.integration.client.customstep.paging.PagingPreStep
Arguements –
pagingSize
100000
pagingFilename
[CFGFOLDER]\pagefile.pgn

Post-processing:
1st step – com.taleo.integration.client.customstep.count.ExportCountPostStep
Last step – com.taleo.integration.client.customstep.paging.PagingPostStep

There are no arguments for the Post-processing steps.
The batch file will need to take into account an if exists to look for the file, when the file is no longer there it should end the run.